You don't need R1C1 style, which I always find non-intuitive in the
spreadsheet, as you can plug row numbers into an A1 style formula, suvh as

myRange.Formula = "=SUM(A1:A" & lastorw & ")"

if you want columns as well you can use

myRange.Formula = "=SUM(A1:" & ColumnLetter(lastcolumn) & lastrow & ")"


'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Poseur" wrote in message
...
I'm sure somebody knows a better way, but...
I have this sheet I take from messed up data and make it
useable. The problem is that the # of rows and columns varies
from one instance to the next.
So, I did the formulas by hand (fx wizard, etc) and then
switched on R1C1 and lo and behold the reference could be more
easily made dynamic by putting together a string with the
correct row and column variables, like this:

MyRange.FormulaR1C1 = "=SUM(RC(2 - lastcolumn):RC[-1]"

But, of course, the application doesn't know what the variable
"lastcolumn" is so it gets pasted literal and a #NAME# thing
comes up. But this works:

MyRange.FormulaR1C1 = "=SUM(" & RCFix(, 2 - lastcolumn) & ":"
& RCFix(, -1) & ")"

with the helper function:
Function RCFix(Optional r As Integer, Optional c As Integer)
As String
Dim rstrng As String, cstrng As String
If r = 0 Then
rstrng = "R"
ElseIf r < 0 Then
rstrng = "R[" & Format(r) & "]"
Else
rstrng = "R" & Format(r)
End If
If c = 0 Then
cstrng = "C"
ElseIf c < 0 Then
cstrng = "C[" & Format(c) & "]"
Else
cstrng = "C" & Format(c)
End If
RCFix = rstrng & cstrng
End Function

which produces, in this case:
"=SUM(RC2:RC[-1])"
or

MyRange.FormulaR1C1 = "=SUM(RC2:RC[-1])"

Which works good. I don't know if that is useful to anyone but
me but whenever I spend a few hours figuring something out I
put it here sort of as a reward.
I'm interested to see some better ways too.
--
Poseur
"That's just kooky talk." --Kramer