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

 

[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)

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

It works fine on that formula

 

[oldbrewer]

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

if that now works - what is the formula that does not work?

 

[tcowap]

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

if that now works - what is the formula that does not work?

Yes-in a spare cell I have put that formula and it works-but this is no good to me.

The macro still doesn’t work on the original formula I asked about:


=('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)

And I still do not know why? We seem to have just proven that it works on a number of smaller formulas which I already knew it worked on-have I missed something here?

 

[oldbrewer]

The formula works as a formula so it is the way you express it in a macro. Create an extra worksheet called test and in A1 insert the formula to prove that it works. Then delete A1

In a macro IN THE SHEET CALLED TEST create a macro that just says

cells(1,1)= ('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)

if this runs ok cell A1 in TEST will now contain a value

 

[tcowap]

The formula works as a formula so it is the way you express it in a macro. Create an extra worksheet called test and in A1 insert the formula to prove that it works. Then delete A1

In a macro IN THE SHEET CALLED TEST create a macro that just says

cells(1,1)= ('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)

if this runs ok cell A1 in TEST will now contain a value

I think we may be talking at crossed purposes here sorry, the situation you’ve described above doesn’t solve the initial objective.
The idea is not that the macro inputs a formula into the cell, but that it looks at whatever formula is already in the cell, and changes the references in that formula from relative references to absolute ones, i.e. just buts $’s in front of each character in the formula.

So if cell A1 contains
= B1+C1

And you run the macro while A1 is selected, A1 should become
= $B$1+$C$1

The use of this being that if you’ve typed a whole column of formulas which you meant to anchor, but did not, you don’t have to go through each cell individually and can instead just run this macro with a loop over all the formulas.
Normally the macro does this fine, in the case of the formula mentioned here it’s not working. The formula is not input into the macro itself at any point, the formula is sitting in a cell on the spreadsheet which the macro is run upon.
The formula sits in the cell fine, returning a result, then I run the macro on the cell, and instead of leaving the formula in place with $ signs attached, in the case of the formula I've asked about it deletes the formula and leaves “#value”

 

[oldbrewer]

I have just made a spreadsheet with a formula LIKE yours in A1 (it references 3 other sheets) with cell A1 highlighted I ran the macro and it put in all the $ signs in the formula - I think you need to keep simplifying your formula and running the macro UNTIL it works fine then look at the logic