Change formula macro

[sirohiv]

This one is tricky,

Each time I copy selected ROW.
I have to change this formula in various columns of row no. 259
=BDP($D$259,"interval_aVG","calc interval=30D","market data override=px_volume",G$204)
to
=BDP($D$270,"interval_aVG","calc interval=30D","market data override=px_volume",G$204)
for columns in row no. 270 and so on for other rows I copy the same formula.

This formula is used in column G,I,K (every alternate one) till column AA
Just to save the effort, I'm trying to figure out the macros I can use.


So far,

Sub copy()

Selection.copy
Range("A1000").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial (xlPasteAll)


End Sub

This one doesn't work for absolute ref. $D$270. This value has to remain absolute. So, I can't change it to D270. Need a macro. <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[Ralajer]

Welcome to the board sirohiv,

If I am understanding you requirements correctly I don't think you will need a macro to do this. You can use replace as it seems you just want to change the reference from $D$259 to $D$270

• Select the cells/row you want to change
• Open Find and Replace (ctrl+h).
• Find what: $D$259
• Replace with: $D$270
• Press the Option >> button
• Look in: Formulas

You can change all if you know it won't be a problem with Replace All or walk through it with Replace and Find Next (to skip).

 

[mikerickson]

Perhaps you just need relative addressing. Try putting this in row 259. (note the $D259
=BDP($D259,"interval_aVG","calc interval=30D","market data override=px_volume",$G$204)

and then just drag downward, or copy/paste, or use FillDown.