Change linked cell reference to absolute

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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,
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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Hi Randy,

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

Thanks

Bob


"RAP" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >