Index Match Formula in VBA, that utilises Workbook dynamic named ranges

Hi,

Firstly, thanks to everyone who has posted on here. I have frequently trawled the forums as a guest trying to solve problems and this has been a mine of information.

Scenario:
I am working on a macro that utilises two mirror workbooks, a current one and one from the week previous. The user opens the previous Workbook via the macro. At this point I would like the macro to insert an Index Match formula into a specific cell and perform the calculation. The name of the previous Workbook will change and as such I use the Macro to define the name of this Workbook within the formula. The formula itself is made up of dynamic named ranges that will automatically calculate for their own workbook. With the code I have I keep receiving the "Compile Error: Expected end of Statement / Syntax error" on the line containing the formula itself. Hopefully, I have explained my situation clearly enough, if not please let me know.

The code I have so far is as follows:

Please Login or Register  to view this content.

In case anyone is wondering, I know I have sections commented out that I have already created but not utilising whilst testing.

Thanks for any and all help.

Jamie

Thanks OllyXLS,

I did that and it cleared the Compile Error. Unfortunately, I have a slightly different error now.

Run-Time error '1004': Application-defined or object-defined error

A slightly modified version of my code is below. Please could you take a look and let me know what foolish thing I'm doing now

Please Login or Register  to view this content.

Hi OllyXLS

That solved it for me!

Many Thanks,
Jamie