malarvel
08-20-2016, 02:24 AM
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?
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?