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

 

[jolivanes]

Try

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

 

[dchaney]

You need to change your code a bit

From

[B]Sub SumData()
[B]Dim LastRow As Long

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

to

[B]Sub SumData()
[B]Dim LastRow As Long

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

LastRow is using ColNum as its range

 

[newatmacros]

Hi Jolivanes!
Thanks for responding. I tried this; however, it still didn't work and came up with an error every time I try to run it. Any other suggestions?

 

[newatmacros]

Hi Dchaney!
Thanks for the macro! I tried it; however, it is still not working either. Your suggestion definitely sounds like it makes sense, but I keep getting an error with the exact sub you created. Any other suggestions?

 

[Jonmo1]

Are you certain about which line the error is occuring on?

It would seem to me the error would be on this line

LastRow = Range(ColNum).End(xlDown).Row

Since ColNum is just a Number, say 8
Then that is
LastRow = Range(8).End(xlDown).Row

There is no such range(8)

That line probably needs to be

LastRow = Cells(5,ColNum).End(xlDown).Row

 

[newatmacros]

Hi JonMo1,

You are right. I am now getting the error on that line; however, before with my original code, I was getting it on the other line. You solution makes sense as I can see it may have been missing the row reference. I tried it, but it still does not work. The error is still on the "LastRow=Cells(5, ColNum).End(xlDown).Row"

 

[dchaney]

Try this, it works for me

Sub SumData()

Dim LastRow As Long


For ColNum = 8 To 1000 Step 3


    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Cells(1, ColNum).FormulaR1C1 = "=SUM(R5C:R[" & LastRow & "]C)"
    
Next ColNum


End Sub

 

[Jonmo1]

The error is still on the "LastRow=Cells(5, ColNum).End(xlDown).Row"

What do you mean 'Still' ?
That is NOT the line you indicated had the error in your original post (you had it highlighted in Red)

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

Anyway, the code should now be

Sub SumData()
Dim LastRow As Long, ColNum As Long

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

 

[Jonmo1]

I think I see the problem..

this
Cells(1, ColNum).FormulaR1C1 = "=SUM(R5C:R[" & LastRow & "]C)"
should be
Cells(1, ColNum).FormulaR1C1 = "=SUM(R5C:R[" & LastRow - 1 & "]C)"


It's how R1C1 style works, if you have If you have a formula in say H1 that references CR[5], that actually ends up referring to H6
Those [brackets] tell the formula to use the row that is 5 rows OFFSET from the row containing the formula.
If the formula is in Row1, then R[5] is actually row 6, 5 rows offset from 1.

This becomes a problem if LastRow is equal to 1048576 (the last row on the sheet), this would happen if the given column is empty.
Because then there is no such row that is 1048576 rows offset from row 1, it would be trying to go to row 1048577 which doesn't exist - error.