VBA Error 13 and 2015 with Vlookup in Evaluate

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!