unknownymous
Board Regular
 Joined
 Sep 19, 2017
 Messages
 176
 Office Version

 2016
 Platform

 Windows
Hi Guys,
I have 2 workbooks on my deck namely Project (source file) and Reference (vlookup reference file).
In Project workbook, I need to look for the Value of F in Reference Sheet column B and populate it in G column.
Project Workbook: I was able to populate the data in column G using below codes but I also need to get other values for Column H:J. Also, badly need to adjust the formula based on the last nonblank cell and hardcode the formula.
Value for Column G = Column C in Reference File
Value for Column H = Column D in Reference File
Value for Column I = Column E in Reference File
Value for Column J = Column F in Reference File

Sub ProjectLookUp()
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\unknown\Documents\Reference.xlsx")
Set x = extwbk.Worksheets("Sheet1").Range("B1:I100000")
With twb.Sheets("Final")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 7) = Application.VLookup(.Cells(rw, 6).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub

Any help will be much appreciated.
I have 2 workbooks on my deck namely Project (source file) and Reference (vlookup reference file).
In Project workbook, I need to look for the Value of F in Reference Sheet column B and populate it in G column.
Project Workbook: I was able to populate the data in column G using below codes but I also need to get other values for Column H:J. Also, badly need to adjust the formula based on the last nonblank cell and hardcode the formula.
Value for Column G = Column C in Reference File
Value for Column H = Column D in Reference File
Value for Column I = Column E in Reference File
Value for Column J = Column F in Reference File

Sub ProjectLookUp()
Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\unknown\Documents\Reference.xlsx")
Set x = extwbk.Worksheets("Sheet1").Range("B1:I100000")
With twb.Sheets("Final")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(rw, 7) = Application.VLookup(.Cells(rw, 6).Value2, x, 2, False)
Next rw
End With
extwbk.Close savechanges:=False
End Sub

Any help will be much appreciated.