Setting a sum formula

[Guest]

I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go to
the cell right below it, and sum up everything above it. The number of rows
will always be different. Please help. Thanks.

 

[Harald Staff]

Hi

That's what R1C1 does. This will sum "everything above it" just anywhere as
long as it's not in row 1:

ActiveCell.FormulaR1C1 = "=SUM(R1C1:R[-1]C)"

replace "Activecell" with whatever addressing system you use. Do not
activate.

HTH. Best wishes Harald

 

[Harald Staff]

Ooops. Sorry, typo. Should read:

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"

Hi

That's what R1C1 does. This will sum "everything above it" just anywhere as
long as it's not in row 1:

ActiveCell.FormulaR1C1 = "=SUM(R1C1:R[-1]C)"

replace "Activecell" with whatever addressing system you use. Do not
activate.

HTH. Best wishes Harald

I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go to
the cell right below it, and sum up everything above it. The number of rows
will always be different. Please help. Thanks.

 

[Bob Phillips]

Try this

EndRow = TargetSheet.Range("A" & Rows.Count).End(xlUp).Row
If EndRow > 1 Or Range("A1").Value <> "" Then
Cels(EndRow+1, "A").Formula = "=SUM(A1:A" & EndCell.Row & ")"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)