Macro to anchor formulas/make references absolute - not working with some formulas

[tcowap]

Hi There,

I have a macro which takes the current formula in a cell and anchors each reference (makes them absolute instead of relative), the macro is as follows:

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, xlAbsolute, ActiveCell)

This works with all the formulas I’ve used it on before, but I just ran it on the following formula:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25+'UK P&L monthly'!C39+'UK P&L monthly'!C44+'UK P&L monthly'!C45+'UK P&L monthly'!C40+'UK P&L monthly'!C41+'UK P&L monthly'!C42+'UK P&L monthly'!C46)*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

(Bit of an ugly formula sorry)

For some reason the cell just became "#value" rather than replacing the formula with an anchored version-does anyone know what it is about this formula that prevents the macro working or what needs to be changed? Thanks.

T

 

[oldbrewer]

try it on


=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

then on


=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120)

then on

=('UK P&L monthly'!C24+'UK P&L monthly'!C25)

 

[tcowap]

Interestingly:

It doesn’t work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

It does work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120)

It does work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25)


Through trial and error I think it works on anything with less then 4 elements in the formula (don’t know if elements is the correct term), here’s what I mean:

It would work on:
=(x+x+x)
=sum(x:y)+sum(a:b)+sum(c:e)

It would not work on:
=(x+x+x+x)
= sum(x:y)+sum(a:b)+sum(c:e)+sum(h:i)

I have no idea why though-I don’t understand why formulas being over a certain size stop it working or how to re write the macro to overcome this? Would really appreciate it if you could explain the logic and/or a solution. Thanks a lot

T

 

[oldbrewer]

change
=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)
to
=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/4

 

[oldbrewer]

with a c losing bracket on the end - just testing to see if you are paying attention.............

 

[oldbrewer]

it works ok with
=$B$1+$C$1+$D$1+$E$1+$F$1+$G$1+$H$1

 

[tcowap]

change
=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)
to
=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/4

Thing is if I change the formulas the file stops working the way it should- do you know why it's working with some and not others?

 

[oldbrewer]

It doesn’t work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

it my be a typo on your part but you have two ( and only one )

 

[tcowap]

It doesn’t work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

it my be a typo on your part but you have two ( and only one )

Yep just a typo sorry

 

[oldbrewer]

It doesn’t work on:

=('UK P&L monthly'!C24+'UK P&L monthly'!C25*'Balance sheet'!$F$120/SUM('Working capital'!O$4:Q$4)

but it does work if you change SUM('Working capital'!O$4:Q$4) to 4

in spare cell put = SUM('Working capital'!O$4:Q$4) and tell me what the answer is