| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortcut to change change cell reference to Absolute reference? | Excel Worksheet Functions | |||
| Change a cell reference from relative to absolute | Excel Discussion (Misc queries) | |||
| change change cell reference to Absolute reference | Excel Discussion (Misc queries) | |||
| How do I change the default cell reference from absolute to relati | Setting up and Configuration of Excel | |||
| How do I change a cell from absolute reference to relative referen | Excel Discussion (Misc queries) | |||
Hybrid Mode
