simple sum formula question

**Conor** · 07-03-2011, 12:40 PM

First, I would be amazed if this hasn't been answered before, but I cannot get the forum search to function correctly. So...

I have a single workbook open, with a single worksheet.

I want to sum a column where I know the top row, but I am using

FinalRow = Range("B65536").End(xlUp).Row

to determine the bottom row. Two cells below that bottom row, I want to insert a sum of all the numbers above it.

For example, I want to sum B4:Bx. So in cell Bx+2, I want the formula "=sum(B4:Bx)"

I simply cannot come up with the syntax for this simple process. Any help would be much appreciated. Thank you in advance.

Conor

**docMed** · 07-03-2011, 01:43 PM

"=sum(B4:B" & x & ")"

is the correct syntax for including a variable titled 'x'

**royUK** · 07-03-2011, 01:53 PM

Something like

finalrow = Range("B65536").End(xlUp).Row
Cells(finalrow + 2, 2).Formula = "=sum(B4:B" & finalrow & " )"

**docMed** · 07-03-2011, 01:54 PM

It depends whether or not he's typing the code into a spreadsheet, or adding it via vba. Since he provided a vba code at the start, i assumed he wanted to execute this from within vba, which means the code I supplied is correct.

**Conor** · 07-03-2011, 02:05 PM

Thanks for the quick responses. I'm sorry, perhaps I was not clear. I thought it was that simple. I am executing a macro, so the lines in question currently read:

Range("B" & FinalRow + 2).Select
 ActiveCell.Formula = "=SUM(B4:B & FinalRow)"

However, when I execute the macro, which does other things also, the cell with address B & FinalRow +2 gives the result #NAME?, so I must be doing something wrong.

Thanks again for any more feedback.

**royUK** · 07-03-2011, 02:07 PM

Have a look at my answer, it's not the same as your formula. Also, you don't need to select the cell to place the formula in it

**Conor** · 07-03-2011, 02:12 PM

Thank you gentlemen. I did not wish to provoke any disagreement. I have the answer. I think it was that final &" within the parans that was throwing me. I definitely appreciate the input from both of you. If I can figure it out, I will update as solved. thanks again.

**royUK** · 07-03-2011, 02:19 PM

Conor my answer was to you about the difference in formulas.

I originally thought that Docmed was posting something different, I didn't see his next post.