Vlookup run-time error for dynamic range and another worksheet

Question

I am getting run-time error for Vlook-up formula while using dynamically from activesheet to another sheet i.e Binarysheet, in Binary sheet, my lookup range is A to C column I need to iterate the for loop and I have to use vlookup inside the for loop

Dim wfd As Worksheet
Set wfd = thisworkbook.Sheets("Binarysheet")

For i = 20 To 61
    Cells(2, i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],wfd!$A:$B,2,False)"
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells(2, i+1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],wfd!$A:$C,3,0)"
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Next

you cannot mix r1c1 and A1 references: `"=VLOOKUP(RC[-2],wfd!C1:C2,2,False)"` — Scott Craner, Jan 04 '23 at 19:02
Furthermore, `wfd` in `"=VLOOKUP(RC[-3],wfd!$A:$C,3,0)"` does not refer to the variable `wfd`. Use `Binarysheet` — BigBen, Jan 04 '23 at 19:02
with both: `"=VLOOKUP(RC[-2],'" & wfd.Name & "'!C1:C2,2,False)"` — Scott Craner, Jan 04 '23 at 19:03
A couple of must reads: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba — Scott Craner, Jan 04 '23 at 19:06
You are also going to want to add `Step 2` to your For loop, otherwise each loop will overwrite the previous. — Scott Craner, Jan 04 '23 at 19:07
instead of C1:C2, can I make it as variable like Ci:Ci+2 since columns must be dynamic it should be based on variable like i ? @Scott Craner — Itzme ram, Jan 04 '23 at 19:11
There is no harm just doing C1:C65 and only changing the third criterion. — Scott Craner, Jan 04 '23 at 19:16

score 2 · Accepted Answer · answered Jan 04 '23 at 19:23

With the above comments and advice from the two linked questions, we can simplify the whole thing and load the whole without the need to loop:

Dim wfd As Worksheet
Set wfd = ThisWorkbook.Sheets("Binarysheet")

With ActiveSheet
    Dim lastrow As Long
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    Dim rng As Range
    Set rng = .Range(.Cells(2, 20), .Cells(lastrow, 61))
    
    rng.FormulaR1C1 = "=VLOOKUP(RC1,'" & wfd.Name & "'!C1:C65,COLUMN(RC),False)"
    rng.Value = rng.Value
End With

you are amazing Scott, it really worked, and how we have to know like for name we have to add likewise '" & wfd.Name & "' any rules are there for these criterias? — Itzme ram, Jan 05 '23 at 05:28

Vlookup run-time error for dynamic range and another worksheet

1 Answers1