VBA replace part of formula IF and VLOOKUP

Hi,

I try to solve a VBA problem. I like to have a dropdown list with the choices of January to December in the worksheet "Net Sales-Budg-FC-PY". This worksheet links to 3 different worksheets with numbers of each month (jan-dec) in a different column (5-16). Each worksheet has the same column number from jan-dec.

I like with VBA to change the formula on each cell Range("E5:S238") when I choice a different month.
The difficulty is that the formula "Find and Replace" don't replace a part of a formula or the column number of the formula between semicolon ";". My formula look like this each cell Range("E5:S238"):

- cell E5

Please Login or Register  to view this content.

- cell E6

Please Login or Register  to view this content.

and so on...

And
- cell F5

Please Login or Register  to view this content.

- cell F6

Please Login or Register  to view this content.

and so on...

Dropdown list:
To choose February: January with the column ;5; have to be changed in February with column ;6;
To choose March: February with the column ;6; have to be changed in March with column ;7;
and so on...

Can somebody help me with the wright formula? Thanks in advance!

Change the number in your formula, to a reference to a named range, which is changed to the relevant month number by changing the dropdown list.

If your dropdown list is in cell A1, then put a formula like this (in cell B1 for this example) to convert the month name to a month number 5-16

Formula:

Please Login or Register  to view this content.

Then change your formulas to reference the cell with the month number...

Formula:

Please Login or Register  to view this content.

Originally Posted by Made

Thanks AlphaFrog! It works without using VBA. So simple and effective.

You're welcome. Thanks for the feedback.