Macro to change formula

I'm sorry but I don't think it's clear what you're trying to do. The formula in BN4 can be copied to any cell in coulmn BN, automatically preserving all relative references to the row. It looks like that's what you've already done. For example, if you copy the formula in BN4

=IF(YEAR($AE$2)<YEAR(AQ$1),BO4/((1+AQ4)^(BG4)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL4>AR$1,(IF($AE$2<AQ$1, (BO4/(1+AQ$2))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL4>AQ$1,(BO4/((1+AQ$2)^YEARFRAC(AQ$1,$AL4)))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^$AY4))),BO4/(1+AQ$2)^$AY4)))))))

and paste it to BN 99, you will get

=IF(YEAR($AE$2)<YEAR(AQ$1),BO99/((1+AQ99)^(BG99)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL99>AR$1,(IF($AE$2<AQ$1, (BO99/(1+AQ$2))/((1+AP$2)^AQ$3),BO99/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL99>AQ$1,(BO99/((1+AQ$2)^YEARFRAC(AQ$1,$AL99)))/((1+AP$2)^AQ$3),BO99/((1+AQ$2)^$AY99))),BO99/(1+AQ$2)^$AY99)))))))

Originally Posted by yogesh thakker

However i m having trouble copying cell after cell

attached file contains a sample data set.
All i want to do is to write a macro so that formula in cell BN4 cam be copied in cell BN24 or any other cell(in column BN)
Presently i m using word's replace command to change required cells. any automation would be of great help!

I cannot imagine what you are doing in Word. Excel has a find & replace command, but you don't need it, because Excel will automatically update your relative references when you copy & paste a formula.

Your formula is complex enough and mixed with absolute and relative references that it seems you must understand how this works, but I can't figure out how else to intepret the question. What do mean by "change required cells"?

I'm not sure you need a macro at all. Can you be more specific about what you need to do?

When there is data in ae2 formula in cell bn4 looks like this

=IF(YEAR($AE$2)<YEAR(AQ$1),BO4/((1+AQ4)^(BG4)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL4>AR$1,(IF($AE$2<AQ$1, (BO4/(1+AQ$2))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL4>AQ$1,(BO4/((1+AQ$2)^YEARFRAC(AQ$1,$AL4)))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^$AY4))),BO4/(1+AQ$2)^$AY4)))))))

This can be copied down and works fine till the time there is no further entry in column AE
However when new data is entered in Column AE, I need to change the formula in Column BN
If I copy above formula in BN4 to BN10, it would become

=IF(YEAR($AE$2)<YEAR(AQ$1),BO11/((1+AQ11)^(BG11)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL11>AR$1,(IF($AE$2<AQ$1,(BO11/(1+AQ$2))/((1+AP$2)^AQ$3),BO11/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL11>AQ$1,(BO11/((1+AQ$2)^YEARFRAC(AQ$1,$AL11)))/((1+AP$2)^AQ$3),BO11/((1+AQ$2)^$AY11))),BO11/(1+AQ$2)^$AY11)))))))

Whereas what I need is this

=IF(YEAR($AE$9)<YEAR(AQ$1),BO12/((1+AQ12)^(BG12)),(IF(YEAR($AE$9)>YEAR(AQ$1),0,(IF($AL12>AR$1,(IF($AE$9<AQ$1,(BO12/(1+AQ$2))/((1+AP$2)^AQ$10),BO12/((1+AQ$2)^AR$10))),(IF($AE$9<AQ$1,(IF($AL12>AQ$1,(BO12/((1+AQ$2)^YEARFRAC(AQ$1,$AL12)))/((1+AP$2)^AQ$10),BO12/((1+AQ$2)^$AY12))),BO12/(1+AQ$2)^$AY12)))))))
So what I m doing is I copy the formula from cell BN4 and paste in a word document, and then replace $AE$2 with $AE$9 and replace $3 with $10, so that I get my desired formula
And this would again work only till there is no further entry in column AE, as soon as new data is entered in column AE, I need to again change the formula in Bn, that is what I have been doing using Word find and replace command.
If you have any idea how a macro can be used in this situation, please help me. I thought of accomplishing this by using a macro which calls for input boxes, but I couldn’t do it. Any other suggestions, more than welcome!

Please see the attachment for more details.