Macro to Lock Cell References in a formula

[shjennex]

I found an old thread on here that showed a macro you can use to lock cell references in a formula versus having to go cell by cell and adding $'s . Below is the macro I used. The issue I'm running into is on majority of the formulas' it worked, but on certain cells it changed the contents to # Value. Does anyone know why this is happening?

Sub Multi_Cell_Make_Absolute_Reference()
Application.Calculation = xlCalculationManual
Dim c As Range
For Each c In Selection
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlAbsolute)
Next
End Sub

 

[Joe4]

Welcome to the Board!

Can you please show us the formula that returns this #Value error?
What was the formula before the code ran, and what was it after the code ran?
And can you show us the contents of the cells that the formula is referencing?

 

[shjennex]

Hi Joe,
This is the formula before the macro and it changes the contents to #Value after.
Before: =('PCMCS Retrieve'!E64+'PCMCS Retrieve'!E65+'PCMCS Retrieve'!E66)/'Report Current Month'!$A$3
After: #VALUE

This is an example of a formula in the same workbook that does work with the macro.
Before: =('PCMCS Retrieve'!E88+'PCMCS Retrieve'!E922)/$A$3
After: =('PCMCS Retrieve'!$E$88+'PCMCS Retrieve'!$E$922)/$A$3

 

[Joe4]

Does it really show #VALUE for the formula, or is that what the formula is being evaluated to?
If you go to that cell, and hit F2 to edit the formula, what does it show?

I set up a workbook with multiple sheets, and set up an example using the cells from that formula.
When I ran the code on that cell, it correctly changed my formula to the following:

=('PCMCS Retrieve'!$E$64+'PCMCS Retrieve'!$E$65+'PCMCS Retrieve'!$E$66)/'Report Current Month'!$A$3

Makes me think that maybe you have something else going on here.

Also note that your code is changing your calculation mode to "Manual" and not changing it back.