Vlookup run-time error for dynamic range and another worksheet-CodePudding

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

CodePudding user response：

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