VBA to update filepath within a Vlookup based on date within a column

Hi all,

I am trying to get this VBA code working where it will reference some inputs within the file and use those inputs to update the filepath for where the Vlookup is referencing.
I want it to use the date in Col H to automatically replace the date within the file path so it looks at the correct file.

Vlookup which is working but requires manual updating daily:

Formula:

Please Login or Register  to view this content.

From the above, I need the 2022-11 to be updated with the value in M6 and I need the 'Nov 01 2022' to be updated with the applicable value corresponding to that line in Col H, so that it is taking the correct month and date in order to reference the correct file.

Trying to recreate this using VBA (with no experience):

Formula:

Please Login or Register  to view this content.

I basically need Col I to replicate and replace Col D but by using the inputs from Col H and M.
Any help would be much appreciated. Thank you.

Hi,

Thank you so much. I really like how you went about this solution. I have it working (kind of) now, thanks to this.
I have 3 more queries/concerns regarding this however:

1. The file I sent in is a mock worksheet, however on the actual file Col A does have a formula in it, which is a direct reference to another sheet containing the dates. Is there no way around having this working on a col that contains formulas (that are updating/changing)?
2. For the actual VLookup, I need the B2+1, to dynamically update with each cell, so it would go to B3+1, B4+1.....and so on. Is that possible within the macro?
3. The formula and result is populating in Col I when I go into an individual cell and then hit return within the cell. However, it is not working when more than 1 date gets put into col A at a time, which is what will happen in this instance. There will be 24 new entries daily, all the same date, an entry for each hour. Is there a way to have this updated so it can handle multiple changes at once?

Thanks again for your help and for looking into it.