Multiple Data Sources in Vlookup (VBA)

[Claire Jackson]

Hi,

I have an excel workbook which pulls in data from a range but there is now another range that I need to add to the lookup.

Is this possible as I continually get an error when it hits the 2nd workbook. Please see code below? (When it gets to Rev_Comp_Date = Application.WorksheetFunction.VLookup(Job_Ref, Workbook2, 9, False) is when Im getting the error???

Sub GetData()

Dim CRM_Data As Range
Dim MS_Forms As Range
Dim InitialRange As Range
Dim Workbook2 As Range
Dim Job_Ref As Range
'Dim Job_No As Range
'Dim CM_Name As Range
Dim CM_Name As Variant
Dim Job_Name As Variant
Dim Contract_Administrator As Variant
'Dim Current_Completion_Date As Variant
Dim Client_Ref As Variant
Dim Client As Variant
Dim Workstream As Variant
Dim Next_Action_Date As Variant
Dim Cur_Percentage As Byte
Dim Rev_Comp_Date As Variant
Dim Delay_Reason As Variant


'Open the data tables
Workbooks.Open ("\\PDDC01\ClaimManagement\0-CLAIRE\Daily Report CRM.xlsx")
Workbooks.Open ("\\PDDC01\ClaimManagement\0-CLAIRE\Prodrive Job Update.xlsx")

'Workbook Activate
    Workbooks("Job Update via MS Forms.xlsm").Activate


Set InitialRange = Workbooks("Daily Report CRM.xlsx").Worksheets("Table").Range("A1:DE1000")
Set Workbook2 = Workbooks("Prodrive Job Update.xlsx").Worksheets("Sheet1").Range("A1:AA1000")
Set Job_Ref = Workbooks("Job Update via MS Forms.xlsm").Worksheets("Sheet1").Range("$H$1")



CM_Name = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 61, False)
Job_Name = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 12, False)
Contract_Administrator = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 69, False)
'Current_Completion_Date = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 51, False)
Client_Ref = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 4, False)
Client = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 5, False)
Workstream = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 6, False)
Next_Action_Date = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 10, False)
Cur_Percentage = Application.WorksheetFunction.VLookup(Job_Ref, InitialRange, 55, False)

Rev_Comp_Date = Application.WorksheetFunction.VLookup(Job_Ref, Workbook2, 9, False)
Delay_Reason = Application.WorksheetFunction.VLookup(Job_Ref, Workbook2, 17, False)




'MsgBox (Result)

Range("B4").Value = CM_Name
Range("G4").Value = Job_Name
Range("B8").Value = Contract_Administrator
'Range("B10").Value = Current_Completion_Date
Range("G6").Value = Client_Ref
Range("B6").Value = Client
Range("G8").Value = Workstream
Range("B13").Value = Next_Action_Date
Range("G19").Value = Cur_Percentage
Range("B15").Value = Rev_Comp_Date
Range("B17").Value = Delay_Reason

 

[offthelip]

you need to put

Workbooks("Prodrive Job Update.xlsx").Activate

just before the line that fails

 

[Claire Jackson]

That didn’t work. I now have error 1004 unable to get the VLookup property of the WorksheetFunction class

I have no idea why it’s not working as the path is definitely correct?