Substitute a variable for a row number

Posted by Clint on November 12, 2001 1:08 PM

Great Site! Wish I had found it a long time ago!
The subject line pretty much sums it up for me. I need to know how to substitute a variable (declared w/i the macro sub.) for a literal when referencing a row.
E.G.--ActiveCell.FormulaR1C1 = "=SUM(R2C:R23C)"
I would like to replace "23" with a variable. I have tried using "&" on both sides of the variable, enclosing it in parentheses and various and sundry other bad ideas not worth mentioning.
Any help would be greatly appreciated!!
Thanks!

Posted by faster on November 12, 2001 1:14 PM

you probably had it, sometimes the spacing can cause issues
and isn't corrected by Excel

Sub UseVar()
Dim MyVar
MyVar = 23
ActiveCell.FormulaR1C1 = "=SUM(R2C:R" & MyVar & "C)"
End Sub

Posted by Clint on November 12, 2001 2:12 PM

THANK YOU SO MUCH!!!!
That was it! The spaces and paying careful attn to the " marks!!! Thanks AGAIN!!!