Index/Match for a range

[VBA-Apprentice]

Hi,
I am working on a VBA code for INDEX/MATCH function for a range of IDs. Sheet1 has table i.e. Table1 (which would be a dynamic range since there could be some additions or deletions over time) with name of emp in range (B4:B36) and emp ID in (C4:C36) (first row is header in the range B4:C36). Sheet2 has another table i.e. Table2 which has some more data, in Table2 there’s column A (where I want to write a code for INDEX/MATCH formula) and it should look for ID in column C and match it with the IDs in table1 (from sheet1) and index names in A2 and all the way till the last row of the data in table2. I would also like the formula to loop through all the cells and populate the names in A2:LastRow in table2.
Here’s the code I’ve written
Sub AddNames()
Dim i As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Long
Dim LastRow1 As Long
Dim LastRow2 As Long

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
LastRow = ws2.Range("A1" & Rows.Count).End(xlUp).Row
LastRow1 = ws1.Range("B4" & Rows.Count).End(xlUp).Row
LastRow2 = ws1.Range("C4" & Rows.Count).End(xlUp).Row

ws2.Range("a2").Activate
For i = 2 To LastRow
If ActiveCell.Offset(0, 2) <> "" Then
ActiveCell.Formula = Application.WorksheetFunction.Index(Sheets(ws1).Range("B5:B" & LastRow1), Application.WorksheetFunction.Match(Sheets(ws2).Range("C2"), Sheets(ws1).Range("C2:C" & LastRow), 0), 1)
End If
Next i
End Sub

Any help would be greatly appreciated!

 

[mart37]

For i = 2 To LastRow
    If ws2.Cells(i, 3) <> "" Then
        ws2.Cells(i, 1) = Application.WorksheetFunction.Index(ws1.Range("B5:B" & LastRow1), Application.WorksheetFunction.Match(ws2.Cells(i, 3), ws1.Range("C5:C" & LastRow2), 0), 1)
    End If
Next i