FormulaR1C1 = "VLOOKUP()" with VARIABLE RANGE

**iforget** · 06-05-2012, 05:56 PM

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** · 06-05-2012, 07:03 PM

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

Sub Test_Earns_Rev_VLookup_2()
    Dim i           As Long
    Dim iBeg        As Long
    Dim iEnd        As Long

    With Worksheets("Earnings Revision Data")
        For i = 1 To 2
            iBeg = i * 10
            iEnd = i * 10 + 9
            .Cells(2 + i, "C").FormulaR1C1 = "=VLOOKUP(RC[-1],'Beg. Yr. Estimates'!R" & iBeg & "C2:R" & iEnd & "C4,2)"
        Next i
    End With
End Sub

FormulaR1C1 = "VLOOKUP()" with VARIABLE RANGE

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

FormulaR1C1 = "VLOOKUP()" with VARIABLE RANGE

Re: FormulaR1C1 = "VLOOKUP()" with VARIABLE RANGE

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions