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 Sub
Thanks in advance, am quite new to VBA so any help would be greatly appreciated.