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

Change linked cell reference to absolute
 

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" wrote in message
...
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

Change linked cell reference to absolute
 

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" wrote in message
...
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

Change linked cell reference to absolute
 

Hi Randy,

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

Thanks

Bob


"RAP" wrote in message
...
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" wrote in message
...
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

Change linked cell reference to absolute
 

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

"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" wrote in message
...
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

Change linked cell reference to absolute
 

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 wrote:

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

"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" wrote in message
...
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




--

Dave Peterson

Change linked cell reference to absolute
 

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" wrote:

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 wrote:

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

"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" wrote in message
...
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




--

Dave Peterson

Change linked cell reference to absolute
 

A good rule for life, too <bg.

AussieBec wrote:

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" wrote:

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 wrote:

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

"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" wrote in message
...
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




--

Dave Peterson


--

Dave Peterson