How to sum a range of data?

[ungers]

Hi,

I am trying to simply find the sum of a range of data...I have tried putting the range into a the formula, and also creating aliases for the cells but it either doesn't work or I keep getting a name error in excel and I just can't seem to hit the nail on the head with right code:

The range I am trying to sum is:
Range(Range("B2"), Range("B2").End(xlDown)).Select

And I can find the cell that I want the sum to go into by:
Range("B2").End(xlDown).Offset(1, 0).Select

As I said I have tried a few things but just get this right. Should I be using Value, Formula or FormulaR1C1?

Any help would be very much appreciated!

 

[namliam]

sum(B:B)

Is that what you are looking for?

 

[Brianwarnock]

Try

Formularow=Range("B2").End(xlDown).Offset(1, 0)
Range("B" & formularow)="=Sum("B2:B" & formularow-1 & ")"

Brian

Ps I cannot test my old pc is kaput, typing this on an iPad

 

[ungers]

Hi Brian,

Thats doesn't get very far...I get an error inside VBA editor saying Compile Error, Expected: End of Statement and it highlight B2.

It seems it doesn't like the quotation marks very much!! Not sure if that provides any clues as to why I can't get this working?!

 

[ungers]

I got this to work using:

lastrow = Range("B1").End(xlDown).Row
Range("B" & lastrow + 2).Formula = "=SUM(B2:B" & lastrow & ")"

Thanks for your pointers.

 

[Brianwarnock]

Glad you got it working, took me a while to spot the difference in our formulas, then noticed that I had been so busy getting the syntax correct, rusty after 7 years retired, that I forgot .Formula

Brian