Using variable cell reference in vba to insert formula

[trodneyrotter]

Hi,

I think I'm nearly there with this, but just need a bit of help tweaking it to make it work. I'm wanting to use vba to add an indirect formula in a range of cells with a lastrow variable.

So far I have this, but I can't get the last row variable to work. When I set the Range as (D20:F30) it works great, but when I add in the last row variable it errors on the (""'""&RC3&""'!""&R19C), stating Expected: list separator or ).

Sub AddIndirect()
Dim thisRow As Long
thisRow = Range("A" & Rows.Count).End(xlUp).Row
'ActiveCell.Offset(0, 1).Formula = "=SUM(C" & thisRow & "!" & "$D19)"
Range("D20:F " & lastrow").FormulaR1C1 = "=SUM(INDIRECT(""'""&RC3&""'!""&R19C))"
End Sub


[code/]

I'm really trying to get to grips with the ampersand syntax, but its just not sinking in.

Thanks for your help.

 

[FormR]

Looks like the problems were with your variable names and space after the :F

Sub AddIndirect()
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
'ActiveCell.Offset(0, 1).Formula = "=SUM(C" & thisRow & "!" & "$D19)"
Range("D20:F" & lastRow).FormulaR1C1 = "=SUM(INDIRECT(""'""&RC3&""'!""&R19C))"
End Sub

 

[trodneyrotter]

Oh wow, thank you so much! I've obviously been staring at this too long and didn't even see that.

Thanks again, I really appreciate your help. Time for me to get some zzzz I think