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.

yogesh, please don't quote whole posts. It's just clutter.

ok will attach in a separate file if post becomes long!

Yogesh, I asked you not to quote whole posts.

I suspect the underlying problem is with your data organization. It is very cryptic and difficult to understand. However, note that I have attached a solution that uses a macro (see below).

As near as I can figure, you have these blocks of data, each with some sort of header record followed by several data records. Then your "present value matrix" does a calcuation for each data record, but has absolute references to elements of the header record (such as $AE$2).

Therefore, when adding a new data block, you have to update the formula in your "present value matrix" (in column BN) to refer to the appropriate header record (such as changing to $AE$9).

One suggestion is that you repeat the header data in every record, instead of just putting it at the beginning. That allows you to use relative references in your formulas that can be copied to subsequent rows with no macros, no editing. That approach also allows for other analysis, such as with pivot tables, though you may not need that.

...that is what I have been doing using Word find and replace command.

I will repeat that if you have to do this, you should use the find & replace that's already in Excel. There is no need to copy formulas into Word, change them, then copy them back.

If you have any idea how a macro can be used in this situation, please help me.

It would be possible to do this with a macro. The way I would approach it is to define a user-defined function to retrieve the date that you are now getting from column AE. It would look like this:

Please Login or Register  to view this content.

This allows you to use relative references for the data in AE. I have attached a revised workbook for your reference.

However, I still can't figure out what you are doing when you

replace $3 with $10