VBA to Enter a Formula into a Range Within a Column

Column S in my worksheet has formulae starting in cell S9: =IF($C9="x",IF($Q9>$M9,0,IF($P9>$N9,-1,($M9-$R9)/$O9)),0)

That formula is repeated all the way down the column, currently down to row 4206.

Several times each day I have to replace "$C9" in the formula with "$B9" or "$A9" or "$D9" and then drag the revised formula in the S9 cell all the way down the column. I would like to automate this process. I've tried concatenating $C9 by putting "C" in cell S2 but that didn't work for me: =IF(CONCATENATE("$",S2,"9")="x",IF($Q9 . . . . . . etc.

Is it practicable to have VBA that will enter the formula, as above, in cell S9 and then copy it to every cell in the column? I can then assign that coding to a control called, for example, "C-Update". I can then duplicate the code for A, B & D and assign each coding block to controls "A-Update", "B-Update" and "D-Update" and by use of those four controls I can then readily switch the contents of col S (between S9 and S4206) back and forth.

In case it's not clear, cell S4206 would contain: =IF($C4206="x",IF($Q4206>$M4206,0,IF($P4206>$N4206,-1,($M4206-$R4206)/$O4206)),0)

Re: VBA to Enter a Formula into a Range Within a Column

Hi Carim - Brilliant! That's exactly what I was trying to do when I tried using Concatenate. It works a lot better when it's coded by someone who knows what they're doing!

It's been a while since you last assisted me; thanks for your assistance once again. That will save me so much time.