PDA

View Full Version : vlookup within for loop vba



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?

SamT
08-20-2016, 07:49 AM
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