Problems with R1C1 Formula Notation and Last Row

[newatmacros]

Hi Everyone!

As my username suggests, I am very new at VBA with Excel; however, I am really trying to create macros that can be utilized whenever. The one I am working on is one for my data. Its purpose is to sum Row 5 cell all the way down to the last cell row of data in the specified column. I have successfully used this kind of macro for different formulas that have work great; however, for some reason I cannot get this one to work. The error keeps coming up in the italicized red row for the end of the expression. I know that it must be something really simple that I am just not catching.
Any help will be much appreciated!
Thank you!


Sub SumData()
Dim LastRow As Long

LastRow = Range(ColNum).End(xlDown).Row
For ColNum = 8 To 1000 Step 3
Range (Cells(1, ColNum).FormulaR1C1 = "=SUM(R5C:R[" & LastRow & "]C)"
Next ColNum
End Sub

 

[dchaney]

Do you really need to take this out to 1000 columns or is that a number your using to ensure you hit all of them you need?

You could create a lastCol variable like the LastRow

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Then make the macro like

[COLOR=#333333]Sub SumData()
[/COLOR]
Dim LastRow As Long, LastCol As Long, ColNum As Long

LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For ColNum = 8 To LastCol Step 3

    Cells(1, ColNum).FormulaR1C1 = "=SUM(R5C:R[" & LastRow & "]C)"
    
Next ColNum

 [COLOR=#333333]End Sub[/COLOR]

 

[newatmacros]

Hi Jonmo1,

Sorry about the confusion. That line was the error line from the start. Then going off of what was suggested by others, the line you pointed out became the trouble spot. You were definitely correct in catching that.
I tried your new macro and it works great! Thank you, thank you, thank you! You actually answered my next question about the addition of that extra row. I noticed it when I ran the code, but since you caught that and explained it, I can now make the appropriate adjustments! Thank you, again!

 

[Jonmo1]

You're welcome.