VBA to replace value in formula in range

[MagicMan]

Hi Team,

I'm looking for a macro tthat would find a specific value (Cell E2 in sample) in a formula in range F4:F50 and replace all instances of the value of cell E2 with the value in cell E1 without compromising the formula.

In short the formula in F4:

Please Login or Register  to view this content.

would become:

Please Login or Register  to view this content.

and so on down to F50

Many thanks

MagicMan

P.S: The values in cells E1 and E2 are driven by D1 which is in turn calculated from C1 (from A4)
A4 = Text with date at end
C1 = Date extrapolated from A4
D1 = Date from C1 as text
E1 = D1 - 1
E2 = D1 - 2

[pjwhitfield]

I'm probably missing something fundamental here but why not just replace the value with D1 - 1 ?

[MagicMan]

Hi PJ

I'm not sure I follow your thoughts.

The value I want replacing refers to other sheets that will be created. I'm not sure if you mean replacing the sheet reference to D1-1. That would remove the sheet reference which would kill the formula.

MM

[romperstomper]

The basic code would be something like:

Please Login or Register  to view this content.

[MagicMan]

Hi Team,

Doing a bit of research and it's a bit ugly, but I've got this far and it works for cell F3, I just need it to loop until the end of the formulas in this column.

Please Login or Register  to view this content.

I'm quite happy for it to loop.

Any ideas?

MM

[MagicMan]

It's not pretty, and always open to improvements, but finally come up with this ...

Please Login or Register  to view this content.

[romperstomper]

Was there something wrong with post 4?

[MagicMan]

Was there something wrong with post 4?

Hi Rorya,

Not as far as im aware mate. Didnt see it until too late.

I'll certainly try it and, if its faster than what I've done,... Happy days! I'll use it 😀

[MagicMan]

Hi Rorya,

I've now had a chance to test it and WOW!!! So much simpler and so quick (it was that quick (when compared to the rubbish I had put together), I had to run it twice just to confirm it had run )

Rep added and many thanks,

MM