如何在vlookup中迭代for循环?

Question

我必须使用vLookup将列的值映射到另一个工作簿中的另一列。这里的问题是，它只需要一个值来迭代循环，即使它是随机的，它也会拒绝下一个即将到来的值。这是下标超出范围的错误。以下是我的代码-

Sub S1_Outage()
Dim rLookRange As Range
Dim Node_id As Long
Dim Wb1 As Workbook
Dim ws As Worksheet
Dim i As Long
Dim LastRow As Long
'Removing Duplicates
ActiveSheet.UsedRange.RemoveDuplicates Columns:=3, Header:=xlYes

'Insert column
Range("D1").EntireColumn.Insert
Range("D1").Value = "Zone"
LastRow = Sheets("10Hrs 4G S1 Outage (1)").Range("C2").End(xlDown).Row
For i = 2 To LastRow
Node_id = Sheets("10Hrs 4G S1 Outage (1)").Cells(i, "C").Value
Set Wb1 = Workbooks.Open("C:UsersKhushiDesktopCXX_March.xlsx")
Set ws = ActiveSheet
Set rLookRange = ws.Range("A:C")

Workbooks("10Hrs 4G S1 Outage (1).xlsm").Sheets("10Hrs 4G S1 Outage (1)").Cells(i, "D").Value  = Application.WorksheetFunction.VLookup(Node_id, rLookRange, 3, False)

Next
End Sub

Answer 1

Dim rLookRange As Range
Dim Node_id As Long
Dim Wb1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim i As Long
Dim LastRow As Long
'Removing Duplicates
ActiveSheet.UsedRange.RemoveDuplicates Columns:=3, Header:=xlYes

'Insert column
Range("D1").EntireColumn.Insert
Range("D1").Value = "Zone"
Set ws = Sheets("10Hrs 4G S1 Outage (1)")
LastRow = ws.Range("C2").End(xlDown).Row
Set Wb1 = Workbooks.Open("C:UsersKhushiDesktopCXX_March.xlsx")
Set ws1 = ActiveSheet
Set rLookRange = ws1.Range("A:C")
ws.Activate  ' optional

For i = 2 To LastRow
Node_id = ws.Cells(i, "C").Value

ws.Cells(i, "D").Value = Application.WorksheetFunction.VLookup(Node_id, rLookRange, 3, False)

Next
End Sub