problem with relative references / anchoring when copying formulas from one range to another

[pingpong777]

Hi. I'm using this line:

Range("Scen_Active").Offset(0, (1 + Range("Scenario_Destination").Value)).Formula = Range("Scen_Active").Offset(0, (1 + Range("Scenario_Source").Value)).Formula

to copy scenario inputs from one column/scenario (numbered as Scenario_Source) to another (numbered as Scenario_Destination). I'm noticing an inconsistent issue- within this range (Scen_Active), I have a few cells which have formulas with the row anchored (=o$38 * 3; that sort of thing). Sometimes, the column letter remains an absolute reference, without any anchoring (for example, when trying to copy column P to column T, the formulas in column T will end up still referencing column P, even though they're written like (=P$38 * 3), with only the row number anchored.

Any ideas on what is tripping me up?


Thanks,

pingpong777

 

[pgc01]

Hi

You are using (range1.formula=range2.formula). This is reading the formula as as string from one range and writing it exactly equal in the second range.
There is no anchoring, the formula is copied exactly as is.

Maybe what you want

Range2.Copy Destination:=Range1

This is like the excel worksheet copy, the anchoring will be respected and the relative addresses are adjusted.

Another option is to copy only the formula, like:

Range2.Copy
Range1.PasteSpecial Paste:=xlPasteFormulas

 

[pingpong777]

Hi @pgc01. Thanks! I think the first option that you outlined is what I want, as the range includes a mix or formulas and hardcoded values and I want to bring everything, including the formatting.

This site is awesome!

 

[pgc01]

You're welcome. Thanks for the feedback.