Solved: Dynamic Sum Macro

[qcoleman]

I'm trying to write a macro that will search a row i.e the column headers for a text string i.e. sales, based on the column that sales is found in i would like for it to find the last used row in this column and sum this column and put the results in the same column and 1 cell below the last row found in the particular column. I'm close but can't seem to tweak things just right.

[Bob Phillips]

[vba]

Public Sub Test()
Dim cell As Range
Dim Lastrow As Long

On Error Resume Next
Set cell = Rows(1).Find("Sales")
On Error GoTo 0
If Not cell Is Nothing Then

Lastrow = Cells(Rows.Count, cell.Column).End(xlUp).Row
cell.Offset(Lastrow, 0).FormulaR1C1 = "=SUM(R2C" & cell.Column & ":R[-1]C" & cell.Column & ")"
End If
End Sub[/vba]

[qcoleman]

what does the ".formulaR1C1" do?

[qcoleman]

The code works perfect, but its not putting the results in the appropriate place since i changed the row where my columns headings are located What part of your code selects the cell to enter the formula? Thanks for the quick response and help!



[vba]Public Sub Test()
Dim cell As Range
Dim Lastrow As Long

On Error Resume Next
Set cell = Rows(4).Find("*Sales*")
On Error GoTo 0
If Not cell Is Nothing Then

Lastrow = Cells(Rows.Count, cell.Column).End(xlUp).Row
cell.Offset(Lastrow, 0).FormulaR1C1 = "=SUM(R5C" & cell.Column & ":R[-4]C" & cell.Column & ")"
End If
End Sub[/vba]

[qcoleman]

i got it to work, but thank you for the all of your help on the code!

[VBA]Public Sub Test()
Dim cell As Range
Dim Lastrow As Long

On Error Resume Next
Set cell = Rows(4).Find("*Sales*")
On Error GoTo 0
If Not cell Is Nothing Then

Lastrow = Cells(Rows.Count, cell.Column).End(xlUp).Row
cell.Offset(Lastrow - 3, 0).FormulaR1C1 = "=SUM(R5C" & cell.Column & ":R[-1]C" & cell.Column & ")"
End If
End Sub[/VBA]

[Bob Phillips]

You should have mentioned that at the start