Hi all,
I am working on a workbook with the following sheets:
- Sheet 1 has a date column in the format "30.07.2020" (a string), it runs from 30.07.2020 to 31.08.2020. This serves as the lookup value (to be transformed to datevalue)
- Sheet 5 has a lookup table, which takes the date mentioned above to return a string which is a path to an excel workbook(not opened). This string looks like 'Z:\...\[Standard Mandate Trade Rationales 27thJul2020 - 31stJul2020.xlsx]SG'
I am trying to loop through every row in Sheet 1, and store the excel file path as a variable in VBA using the Evaluate function. Reason for storing it as a variable is because I need to reference it again in an Index Match formula (using 2 columns in Sheet 1 as the criteria) which returns a value found in the excel workbook 'Z:\...\[Standard Mandate Trade Rationales 27thJul2020 - 31stJul2020.xlsx]SG'
However, I am getting Error 13 when I use the evaluate function. I understand that it is a mismatch in type, however, I used LookupValue in both string and datevalue format, to no avail. When my cursor hovers over VLPathSGStan, it says "Empty". Previously, I was getting Error 2015, but am now unable to replicate it. Commented away lines are my previous attempts that also did not work.
Here is my code:
Sub Vlookup()
Dim TotalRange As Range
Dim row As Long
Dim SGStanRationale As String
Dim SGStanClient As String
Dim SGStanAsset As String
Dim SGCustRationale As String
Dim SGCustClient As String
Dim SGCustAsset As String
Dim LookupTable As Range
Set LookupTable = Sheet5.Range("I3:T9")
Debug.Print LookupTable.Rows.Count
Dim dot As String
Dim slash As String
dot = "."
slash = "/"
With Worksheets("SG")
Set TotalRange = .UsedRange '
Set TotalRange = TotalRange.Offset(2, 0).Resize(TotalRange.Rows.Count - 2, TotalRange.Columns.Count)
For row = 1 To (TotalRange.Rows.Count)
'Dim LookupValue As Range
'Set LookupValue = TotalRange.Cells(row, 4)
Dim LookupValue As String
LookupValue = Replace(TotalRange.Cells(row, 4), ".", "/")
Dim LookupValueDate As Date
LookupValueDate = DateValue(LookupValue)
Debug.Print LookupValueDate
Dim VLPathSGStan As Variant
'Dim VLPathSGCust As Variant
'VLPathSGStan = .Evaluate("VLOOKUP(DATEVALUE(SUBSTITUTE(" & LookupValue & "," & dot & "," & slash & "))," & LookupTable & ",5)")
'Debug.Print VLPathSGStan
VLPathSGStan = .Evaluate("VLOOKUP(" & LookupValue & "," & LookupTable & ",9)")
'VLPathSGCust = .Evaluate("VLOOKUP(DATEVALUE(SUBSTITUTE(" & LookupValue & "," & dot & "," & slash & "))," & LookupTable & ",10)")
SGStanRationale = VLPathSGStan & "!Y:Y"
SGStanClient = VLPathSGStan & "!$U:$U"
SGStanAsset = VLPathSGStan & "!$Q:$Q"
SGCustRationale = VLPathSGCust & "!$Y:$Y"
SGCustClient = VLPathSGCust & "!$U:$U"
SGCustAsset = VLPathSGCust & "!$Q:$Q"
TotalRange.Cells(row, 26).FormulaArray = _
"=IFNA(INDEX(SGStanRationale), MATCH(1,(TotalRange.Cells(row,17)=SGStanAsset" & _
"* SEARCH(LEFT(TotalRange.Cells(row,21),13),SGStanClient)=1, 0))," & _
"INDEX(SGCustRationale), MATCH(1,(TotalRange.Cells(row,17)=SGCustAsset" & _
"* SEARCH(LEFT(TotalRange.Cells(row,21),13),SGCustClient)=1, 0))"
Next row
End With
End SubThanks in advance, am quite new to VBA so any help would be greatly appreciated.