How to iterate for loop in vlookup?

Question

I have to take a column's value and map it to another column in another workbook using vLookup. The problem here is that it takes only one value to iterate through the loop even if it's random then it rejects the next upcoming value. This is giving me subscript out of range error. Following is my code-

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:\Users\Khushi\Desktop\CXX_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:\Users\Khushi\Desktop\CXX_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