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!

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:D" & lngRow & ")"

--
Kevin Backmann


"Annoyed Accountant" wrote:

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!

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


"Kevin B" wrote:

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:D" & lngRow & ")"

--
Kevin Backmann


"Annoyed Accountant" wrote:

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!

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

Charles

Annoyed Accountant wrote:
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!

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


"Annoyed Accountant" wrote:

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!

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

Tom Ogilvy wrote:
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


"Annoyed Accountant" wrote:

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!

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


"Tom Ogilvy" wrote:

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


"Annoyed Accountant" wrote:

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!

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


"Die_Another_Day" wrote:

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

Tom Ogilvy wrote:
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


"Annoyed Accountant" wrote:

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!

Thank you all for the help!!!!

"Tom Ogilvy" wrote:

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


"Tom Ogilvy" wrote:

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


"Annoyed Accountant" wrote:

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!