CODEWITHSUNDEEP
vbaexcel
Zdub
Zdub

Reputation: 3

Inserting vlookup via formular1c1 in a Macro

I'm trying to fill a Vlookup into a range of cells using .FormulaR1C1 and get an error 1004. I have almost this exact code elsewhere in my macro and it works fine so I'm not sure what's wrong. It's probably a simple fix and I'm just not seeing it...

Here is the code:

Range("W2").Select

Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "=VLOOKUP(RC[-2],$AA$2:$AC$35,3,TRUE)"

Upvotes: 0

Views: 665

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

If you are going to use R1C1 then all references must be in that format.

Range(Range("W2"), Range("W2").End(xlDown)).FormulaR1C1 = "=VLOOKUP(RC[-2],R2C27:R35C29,3,TRUE)"

But you should always apply the parentage of the sheet to each range object:

With Worksheets("Sheet1") 'Change to your sheet
    .Range(.Range("W2"), .Range("W2").End(xlDown)).FormulaR1C1 = "=VLOOKUP(RC[-2],R2C27:R35C29,3,TRUE)"
End With

Upvotes: 2

Related Questions