FormulaR1C1 = "VLOOKUP()" with VARIABLE RANGE

[iforget]

Hello,

I'm trying to create a macro that puts a vlookup formula into a column of cells. So far this is what I have:

Sub Test_Earns_Rev_VLookup_2()

Sheets("Earnings Revision Data").Select
For i = 1 To 2
Worksheets("Earnings Revision Data").Cells(2 + i, 3).Select
beg = i * 10
ends = i * 10 + 9

ActiveCell.Formula = "=VLOOKUP(RC[-1],'Beg. Yr. Estimates'!B" & beg & ":D" & ends & ",2,TRUE)"

Next i
End Sub

The output I get for just these two iterations is:

=VLOOKUP(B3,'Beg. Yr. Estimates'!'B10':'D19',2,TRUE)
=VLOOKUP(B4,'Beg. Yr. Estimates'!'B20':'D29',2,TRUE)

Which is SO CLOSE to what I need. EXCEPT for the ' ' around the cells in the range. The strange thing is that if I remove the = from the sub (ActiveCell.Formula = "VLOOKUP(RC[-1],'Beg. Yr. Estimates'!B" & beg & ":D" & ends & ",2,TRUE)" ) the range goes in perfectly with no ' '. But then it's not a formula so, of course, isn't helpful.

Any help with this would be very much appreciated!

[shg]

You can't mix R1C1 and A1 references like that -- they have to be one or the other.

Please Login or Register  to view this content.