View Full Version : vba to insert vlookup between workbooks

11-12-2015, 09:18 PM
I need your help to modify the below code to insert vlookup formula in row 1 till last used column based on values in row 2.
I got this code from net

Sub ImpFPQ()

On Error Resume Next

Dim Imp_Row As Integer
Dim Imp_Col As Integer
Dim Table1 As Range
Dim Table2 As Range
Dim cl As Range

Set Table1 = Range("D2", Range("D2").End(xlDown))

Set Table2 = Workbooks("AAA_data.xlsx").Sheets("Sheet1").Columns("A:Q")

Imp_Row = 2

Imp_Col = 7

For Each cl In Table1
Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, 3, False)
Imp_Row = Imp_Row + 1
Next cl

MsgBox "Done"

End Sub

i tried using the same for inserting vlookup in row number 1, based on lookup value in row 2

Set Table1 = Range("A1", Range("A1").End(xltoLeft))
but getting an error saying table1 has nothing.

can someone help


Cross post:http://www.mrexcel.com/forum/excel-questions/901620-visual-basic-applications-insert-vlookup-between-workbooks.html