VBA Error 13 and 2015 with Vlookup in Evaluate

  • 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:



    Thanks in advance, am quite new to VBA so any help would be greatly appreciated.

  • Try removing the dot before Evaluate

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You need to attach your workbook, or a true example of it with dummy data, but retaining full original structure

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Uploaded Dummy.xlsx, the vlookup table can be found in the 5th sheet. The file name (under column Standard SG) that I am eventually trying to look up from contains date strings, which you will be able to see in the 5th sheet.