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]

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

[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!



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

[qcoleman]

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

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

[Bob Phillips]

You should have mentioned that at the start