I am working on a program which sums a collumn; however, the column is created by a loop and therefore the last cell is a variable. How do I enter a variable in the formula. I tried several variations of this:

a = Range("A1").Formula = "=sum(Range(("b" & 3), ("b" & currow)))

However the formula is not recoginized as a formula and will not sum the range.

Thanks

What you want is:

   Range("A1").Formula = "=SUM(B3:B" & currrow & ")"

Tip: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.

Hope this helps

---

Roger J Coult
Grimsby, UK

: What you want is:
:

:    Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
: 

: Tip: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
:
: Hope this helps
:
:

---

: Roger J Coult
: Grimsby, UK
:
:
:

: : What you want is:
: :

: :    Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
: : 

: : Tip: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
: :
: : Hope this helps
: :
: :

---

: : Roger J Coult
: : Grimsby, UK
: :
: :
: : That's great I already tried it out
:
:
:
:

: : : What you want is:
: : :

: : :    Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
: : : 

: : : Tip: Tou always set the .Formula property to the value you would type to enter a formula in the worksheet directly.
: : :
: : : Hope this helps
: : :
: : :

---

: : : Roger J Coult
: : : Grimsby, UK
: : :
: : :
: : : That's great I already tried it out
: :
: :
: : I did try this out; however, I could not get the program to recognize the variable "currow" I either receive an error message or, if I put the quotation marks around the b3:b as you suggested, I only got the value of the cell with in the quotation marks
Range("A1").Formula = "=SUM(B3:B" & currrow & ")"
: :
:
:
:
:

What I understood you were trying to do was to produce in cell A1 the sum of the cells from row 3 in column B to some calcuated row in column B. That is, the variable currrow is calculated in your VBA script.

I suspect that this is not what you are trying to do. Can you explain what you want to achieve in a little more detail?

---

Roger J Coult
Grimsby, UK

: What I understood you were trying to do was to produce in cell A1 the sum of the cells from row 3 in column B to some calcuated row in column B. That is, the variable currrow is calculated in your VBA script.
:
: I suspect that this is not what you are trying to do. Can you explain what you want to achieve in a little more detail?
:
:

---

: Roger J Coult
: Grimsby, UK
:
:
:
THIS PROGRAM IS KIND OF AN AMATURE WAY OF FORECASTING SALES. AS EACH DAY PROGRESSES THE GROSS SALES ARE SUBMITTED AND ENTERED INTO A CELL (VIA A TEXT BOX) THAT BOX WILL BE MULTIPLIED BY THE FRACTION OF THE TOTAL NUMBER OF DAYS IN THE PERIOD. FOR INSTANCE ON THE FIRST DAY THE PERIOD IS 1/20 (GIVEN 20 SALES DAYS) THE NEXT DAY IS 2/20, AND THE NEXT 3/20 AND SO FORTH. THEREFORE; ON THE DAY BEFORE THE FINAL DAY YOU WILL HAVE 19/20. WHAT I NEED TO DO IS TO BE ABLE TO TAKE THE AVERAGE OF THE COLUMN OF SALES LETS SAY THE B COLUMN (WHICH IS INCREASED BY ONE EACH DAY) MULTIPLY THAT BY THE FRACTION TO SOMEHOW BE ABLE TO PREDICT GIVEN LINEAR SALES THE END OF THE MONTH TOTALS.

SO WHAT I NEED IS THE ABILITY TO TAKE A AVERAGE OF A COLUMN GIVEN THAT THE COLUMN ROW IS A VARIABLE.

I KNOW THAT THIS IS CONVOLUTED REASONING AND IT MAY BE FAULTY BUT ONCE THE PROGRAMMING IS WORKED OUT WE CAN WORK ON THE PREDICTION FORMULA.

I KNOW THAT THE BOSS WILL USE THIS PROGRAM TO SHOW ME THAT MY SALES ARE POOR AND THEREFORE I DON'T DESERVE A RAISE.

THANKS FOR YOUR HELP

Ahhh...

Let's say you have your sales figures in column B.

There is going to be some maximum number of sales days (shall we say 1000, just for the sake of example).

What you are saying is that you want the average of the cells B1:B1000 only counting the cells with some data in.

This is exactly what the AVERAGE() worksheet function does!

If you enter =AVERAGE(B1:B1000) in A1 (and there is no data in B1:B1000), Excel will display #DIV/0! becuase there is no data. Start entering values in B1:B1000 and you'll see the average displayed.

Hope this helps.

---

Roger J Coult
Grimsby, UK