using sum function after offset

[Guest]

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!

 

[Guest]

Declare a variable at the top of the sub to capture the row number -1

Dim lngRow As Long

And then get the current row number less 1 and insert the sum function at
the current cell

lngRow = ActiveCell.Row - 1
ActiveCell.Formula = "=Sum(D2" & lngRow & ")"

 

[Guest]

Ooops! Meant to say D8 in the sum formula, not D2 as initially posted.

 

[Die_Another_Day]

Most efficient way I can think of:
Range("D" & Range("D" & Rows.Count).End(xlUp).Row + 1).FormulaR1C1 = _
"=Sum(R1C4:R[-1]C4)"

Charles

 

[Guest]

Assuming you want the sum in the activecolumn and the top number to sum is in
row 8:

Sub AddSum()
Dim rng as Range
set rng = cells(rows.count,activecell.column).end(xlup)(2)
if rng.row > 8 then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
end Sub
End Sub

 

[Die_Another_Day]

Oops, missed the start @ row 8 part. . Tom, I noticed that when using
R1C1, unless I put the numbers in [], it locks the formula into
Absolute referencing, is there a way to not do that? What I want is to
specify R1C1 ("A1"), but not have it as an absolute so I can copy/drag
down.

Charles

Assuming you want the sum in the activecolumn and the top number to sum is in
row 8:

Sub AddSum()
Dim rng as Range
set rng = cells(rows.count,activecell.column).end(xlup)(2)
if rng.row > 8 then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
end Sub
End Sub

--
Regards,
Tom Ogilvy

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!

 

[Guest]

Looks like I had a typo:

Sub AddSum()
Dim rng As Range
Set rng = Cells(Rows.Count, ActiveCell.Column).End(xlUp)(2)
If rng.Row > 8 Then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
End If
End Sub

--
Regards,
Tom Ogilvy

Assuming you want the sum in the activecolumn and the top number to sum is in
row 8:

Sub AddSum()
Dim rng as Range
set rng = cells(rows.count,activecell.column).end(xlup)(2)
if rng.row > 8 then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
end Sub
End Sub

--
Regards,
Tom Ogilvy

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!

 

[Guest]

R5C6 is absolute. R[-3]C[2] is relative. If you want to have a relative
address, you have to use relative addressing. So you are correct. If you
don't use brackets, it will give an absolute address.

--
Regards,
Tom Ogilvy

Oops, missed the start @ row 8 part. . Tom, I noticed that when using
R1C1, unless I put the numbers in [], it locks the formula into
Absolute referencing, is there a way to not do that? What I want is to
specify R1C1 ("A1"), but not have it as an absolute so I can copy/drag
down.

Charles

Assuming you want the sum in the activecolumn and the top number to sum is in
row 8:

Sub AddSum()
Dim rng as Range
set rng = cells(rows.count,activecell.column).end(xlup)(2)
if rng.row > 8 then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
end Sub
End Sub

--
Regards,
Tom Ogilvy

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!

 

[Guest]

Thank you all for the help!!!!

Looks like I had a typo:

Sub AddSum()
Dim rng As Range
Set rng = Cells(Rows.Count, ActiveCell.Column).End(xlUp)(2)
If rng.Row > 8 Then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
End If
End Sub

--
Regards,
Tom Ogilvy

Assuming you want the sum in the activecolumn and the top number to sum is in
row 8:

Sub AddSum()
Dim rng as Range
set rng = cells(rows.count,activecell.column).end(xlup)(2)
if rng.row > 8 then
rng.FormulaR1C1 = "=Sum(R8C:R[-1]C)"
end Sub
End Sub

--
Regards,
Tom Ogilvy

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!