christomunthe
New Member
- Joined
- Apr 28, 2023
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi,
I have a question regarding how to create a VBA coding to Vlookup from multiple workbooks. However, if there is no value in the Vlookup, the destination column does not change into "N/A". Hence, I want it to keep it as it is.
Here's the coding that I have made:
Sub VLookupFromMultipleSources()
Dim SourceFile As Variant
Dim LookupValue As Variant
Dim WorkbookSource1 As Workbook
Dim WorkbookSource2 As Workbook
Dim WorkbookSource3 As Workbook
Dim LookupRange1 As Range
Dim LookupRange2 As Range
Dim LookupRange3 As Range
Dim DestinationRange As Range
Dim FoundValue1 As Variant
Dim FoundValue2 As Variant
Dim FoundValue3 As Variant
'Set the lookup value to the current cell
LookupValue = ThisWorkbook.Worksheets("LOG").Range("E:E")
'Define the lookup range in the source files
Set WorkbookSource1 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB1.xlsx")
Set LookupRange1 = WorkbookSource1.Worksheets("Tracker").Range("D3:H200")
Set WorkbookSource2 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB2.xlsx")
Set LookupRange2 = WorkbookSource2.Worksheets("Tracker").Range("D3:H200")
Set WorkbookSource3 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB3.xlsx")
Set LookupRange3 = WorkbookSource3.Worksheets("Tracker").Range("D3:H200")
'Set Destination Range
Set DestinationRange = ThisWorkbook.Worksheets("LOG").Range("AL:AL")
'Perform the VLOOKUP
FoundValue1 = Application.VLookup(LookupValue, LookupRange1, 5, False)
FoundValue2 = Application.VLookup(LookupValue, LookupRange2, 5, False)
FoundValue3 = Application.VLookup(LookupValue, LookupRange3, 5, False)
If Not IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then
'If a value is found
DestinationRange.Value = Application.Union(FoundValue1, FoundValue2, FoundValue3)
'If a value is not found
If IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then
DestinationRange.Value = Application.WorksheetFunction.IfError((Application.Union(FoundValue1, FoundValue2, FoundValue3)), "")
End If
End If
End Sub
However, from the coding that has been made so far, it cannot be run since there is a debug in
If Not IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then --> "Object Required"
Please kindly help if you know the solution.
Thank you.
I have a question regarding how to create a VBA coding to Vlookup from multiple workbooks. However, if there is no value in the Vlookup, the destination column does not change into "N/A". Hence, I want it to keep it as it is.
Here's the coding that I have made:
Sub VLookupFromMultipleSources()
Dim SourceFile As Variant
Dim LookupValue As Variant
Dim WorkbookSource1 As Workbook
Dim WorkbookSource2 As Workbook
Dim WorkbookSource3 As Workbook
Dim LookupRange1 As Range
Dim LookupRange2 As Range
Dim LookupRange3 As Range
Dim DestinationRange As Range
Dim FoundValue1 As Variant
Dim FoundValue2 As Variant
Dim FoundValue3 As Variant
'Set the lookup value to the current cell
LookupValue = ThisWorkbook.Worksheets("LOG").Range("E:E")
'Define the lookup range in the source files
Set WorkbookSource1 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB1.xlsx")
Set LookupRange1 = WorkbookSource1.Worksheets("Tracker").Range("D3:H200")
Set WorkbookSource2 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB2.xlsx")
Set LookupRange2 = WorkbookSource2.Worksheets("Tracker").Range("D3:H200")
Set WorkbookSource3 = Workbooks.Open(ThisWorkbook.Path & "\Template Tracker PR AML BB3.xlsx")
Set LookupRange3 = WorkbookSource3.Worksheets("Tracker").Range("D3:H200")
'Set Destination Range
Set DestinationRange = ThisWorkbook.Worksheets("LOG").Range("AL:AL")
'Perform the VLOOKUP
FoundValue1 = Application.VLookup(LookupValue, LookupRange1, 5, False)
FoundValue2 = Application.VLookup(LookupValue, LookupRange2, 5, False)
FoundValue3 = Application.VLookup(LookupValue, LookupRange3, 5, False)
If Not IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then
'If a value is found
DestinationRange.Value = Application.Union(FoundValue1, FoundValue2, FoundValue3)
'If a value is not found
If IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then
DestinationRange.Value = Application.WorksheetFunction.IfError((Application.Union(FoundValue1, FoundValue2, FoundValue3)), "")
End If
End If
End Sub
However, from the coding that has been made so far, it cannot be run since there is a debug in
If Not IsError(Application.Union(FoundValue1, FoundValue2, FoundValue3)) Then --> "Object Required"
Please kindly help if you know the solution.
Thank you.