Change linked cell reference to absolute

[RAP]

Hello,
I've got too many cells to go to, click in the formula bar and hit F4 to
change the formula reference to absolute.
I'm looking for a way to select my cells and run a macro that will take the
cells and make all linked cell references absolute.
Ex: in cell D4, the formula is "=Jan!$D15" , "Jan" being another page in the
workbook. I need like the formula to change to "=Jan!$D$15"

I have the same problem with linked cells having a formula and multiple cell
references in the formula.
Ex: in cell E4, the formula is "=IF(Jan!$F15=Jan!$A$34,Jan!$E15,0)" Again,
I need all formulas referenced to be absolute.

Any help is much appreciated.

Thanks,
Randy

 

[Bob Phillips]

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula( _
cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RAP]

Bob,
As usual, it worked like a charm. Thank you so much for the help. It has
already saved me a "ton" on time. I wasn't aware of the "ConvertFormula"
method. It's exactly what I was looking for.

Also, I found a treasure trove of examples in the VBHelp. "Examples of
Commonly used formulas" Good reading.
Thanks again,
Randy

 

[Bob Phillips]

Hi Randy,

Yeah, that is quite good. I will make a note of it and offer it in future
appropriate responses

Thanks

Bob

 

[AussieBec]

Is it just a case of copy and pasting in VB? This is what I've done and I get
a syntax error at cell.formula = (I'm using 2003)

Thanks
bec

 

[Dave Peterson]

I'm betting that you typed Bob's suggestion into the VBE. Instead, try
copy|pasting his code.

If you did copy|paste, then try copy|paste once more. Something bad happened.
Bob's code works fine for me.

If this doesn't help, post the code you're using.

 

[AussieBec]

Thanks, I repasted and made sure I took out any hidden text before each line
and it's worked a treat. If at first you don't succeed try try again.

Thanks
Bec

 

[Dave Peterson]

A good rule for life, too <bg>.

Thanks, I repasted and made sure I took out any hidden text before each line
and it's worked a treat. If at first you don't succeed try try again.

Thanks
Bec