getting the sum in an unknow lenght and placing in another cell

[barrowinfurnace]

Hi all,

I have this code for summing up the values in a column of an unknown lenght.With this code the sum will appear in the cell at the bottom of the column at the end of the figures being summed up.Is there a way I could get the sum and place it in another cell on the sheet J1 for example.Here is the code:

Sub test()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

Range("B" & lastrow + 1).Formula = "=sum(B4:B" & lastrow & ")"

[Richard Buttrey]

Hi,

I always feel it's much better to have formula totals in a fixed cell above a varying range of rows since the formula need never change, just make sure it covers all the rows your likely to use. Or better still give the total cell a dynamic range name.

If you want to put the formula in J1 then simply

Please Login or Register  to view this content.

Or if you've created a dynamic range name for column B then

Please Login or Register  to view this content.

[barrowinfurnace]

Hi Richard,
thanks for that,I placed ths code in a button but get the following message:
Variable not defined
and it highlights the word "LastRow" in the code function.Am I doing something wrong.
Regards
John

[Richard Buttrey]

Hi,

Stating the obvious I suppose but have you declared the LastRow variable?

I rather inferred from your original post that since you were implying that the code worked and you just wanted to know how to place it somewhere else that yuo have it defined elsewhere and just not shown.

However for a more detailed response upload the workbook so that we may see the request in context.

Just as a matter of interest since I used to work in Barrow In Furness is your name an allusion to this?

[barrowinfurnace]

Hello Richard,
Thanks for that it works great now.The Barrow connection is where I was born and grew up,I moved to bognor in the 80's,just after the Chewits monster ate the bus depot