Macro to change formula

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:

' Return the previous non-blank value at or above the reference
Public Function GetPreviousNonempty(r As Range) As Variant
   Do Until (Not IsEmpty(r) And r <> "") Or r.Row = 1
      Set r = r.Offset(-1, 0)
   Loop
 
   GetPreviousNonempty = r.Value
End Function

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