vlookup within for loop vba

[malarvel]

I have a data in sheet1 range from "A10: H27" having 8 column ( empid, name, designation, department, GPF/CPF NO, Quarters, old basicpay, Gradepay).

I have a another data in sheet2 range from "A5:L21" having 12 column (mpid, name, designation, department, GPF/CPF NO, Quarters, old basicpay, Gradepay, DA, HRA, TA, Grosspay).

In sheet3, I would like to fetch the data using vlookup within for loop through vba.
1. First lookup value range ("A10:A27") of Sheet1.

2. The table_array ("A5:L21")

3. Column_Index - i would like to fetch all the column of sheet2 (i.e 12 column)

I have attached herewith sample data for your reference.

I have a code to find the single column value using vlookup below for your reference

Private Sub CommandButton1_Click()

Dim rng As Range
Dim ws1, ws2, ws3 As Worksheet


Set ws1 = ThisWorkbook.Sheets("sheet1")
Set ws2 = ThisWorkbook.Sheets("sheet2")
Set ws3 = ThisWorkbook.Sheets("Sheet3")
Set rng = ws1.Range("A10")


With ws1
On Error Resume Next
ws3.Range("A1") = Application.WorksheetFunction.VLookup(rng, ws2.Range("A5:L21").Value, 16, False)
End With


End Sub

How to do this task using vlookup with in for loop?

[SamT]

Private Sub SamT_CommandButton1_Click() 
     
     Dim Rw As Long
     Dim ws1 As Worksheet , ws2 As Worksheet , ws3 As Worksheet 
     Dim WsF as Object
     Set WsF = Application.WorksheetFunction
     
     
    Set ws1 = ThisWorkbook.Sheets("sheet1") 
    Set ws2 = ThisWorkbook.Sheets("sheet2") 
    Set ws3 = ThisWorkbook.Sheets("Sheet3") 
    
    For Rw = 10 to 27      
        On Error Resume Next 
        ws3.Cells(Rw - 9, "A") = WsF.VLookup(ws1.Cells(Rw, "A"), ws2.Range("A5:L21"), 16, False) 
   Next Rw     
End Sub