aravindhan_3
11-12-2015, 09:18 PM
Hi,
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
Regards
Arvind
Cross post:http://www.mrexcel.com/forum/excel-questions/901620-visual-basic-applications-insert-vlookup-between-workbooks.html
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
Regards
Arvind
Cross post:http://www.mrexcel.com/forum/excel-questions/901620-visual-basic-applications-insert-vlookup-between-workbooks.html