动态范围和其他工作表的Vlookup运行时错误

Question

我得到vlookup公式的运行时错误，而动态使用从活动表到另一个表即Binarysheet，在二进制表，我的查找范围是A到C列我需要迭代for循环我需要在for循环中使用vlookup

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

Answer 1

有了上面两个链接问题的评论和建议，我们可以简化整个事情并加载整个而不需要循环:

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