Variable in formula

Hi there!

Firstly thank you all for your help, I've found all the solutions to my problems so for on this forum!

Unfortunately I am unable to find a solution to this particular problem, maybe you could help:

I have a workbook with multiple worksheets, on which are a lot of raw data by year (one year per sheet), and a sheet where I extract selected data for analysis.

I am writing a VBA code to automatically go look for this selected data using index/match. My code so far works correctly and inserts the formula in the cells:

Please Login or Register  to view this content.

This goes looking for the data that I want on the 2016 sheet.

Now I would like to use a variable to automatically change all the "2016" in the formula to a variable, so that the people I work with only have to press a button to add a new year to the anlysis sheet.

I've set the variable as:

Please Login or Register  to view this content.

How can I integrate the variable "YEAR" in my formula ?

Sorry, this is quite a basic question but I cannot find on Google an explaination as to how this works and I'm learning as I go.

Thanks very much!

Hi there,

Does the following code get you moving in the right direction?

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thank you very much Special-K and Greg!

It works wonderfully! I actually had an issue with the variable type, which I noticed when you suggested using an Integer.

Hi again,

Many thanks for your prompt feedback and also for the Reputation increase - much appreciated

You're very welcome - glad I was able to help.

Best regards,

Greg M