If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing A1 to $A$1.....
Is there any way to change a set of cells all at one time? The entire
equation? I'm copying cells from one area in the worksheet to another, and I don't want them to automatically change the cell references. How can this be done? Thanks! Shawn -- It's not just based on number of championships won. Richard Petty won 200 races and 7 Daytona 500s in his 30+ year driving career. He also has the most top-5s (555), top-10s (712), poles (126), laps completed (307,836), laps led (52,194), races led (599) and consecutive races won (10 in 1967) of any driver in NASCAR history. |
#2
|
|||
|
|||
Changing A1 to $A$1.....
Shawn,
Select your cells and run the sub below. HTH, Bernie Excel MVP Sub ConvertToAbsoluteReferences() Dim myCell As Range Dim storedCalc As Variant With Application storedCalc = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual For Each myCell In Selection.SpecialCells(xlCellTypeFormulas) myCell.Formula = Application.ConvertFormula( _ myCell.Formula, xlA1, xlA1, xlAbsolute) Next myCell .ScreenUpdating = True .EnableEvents = True .Calculation = storedCalc End With End Sub "43fan" wrote in message ... Is there any way to change a set of cells all at one time? The entire equation? I'm copying cells from one area in the worksheet to another, and I don't want them to automatically change the cell references. How can this be done? Thanks! Shawn -- It's not just based on number of championships won. Richard Petty won 200 races and 7 Daytona 500s in his 30+ year driving career. He also has the most top-5s (555), top-10s (712), poles (126), laps completed (307,836), laps led (52,194), races led (599) and consecutive races won (10 in 1967) of any driver in NASCAR history. |
#3
|
|||
|
|||
Changing A1 to $A$1.....
Bernie,
Worked great! Thanks! Shawn "Bernie Deitrick" wrote in message ... Shawn, Select your cells and run the sub below. HTH, Bernie Excel MVP Sub ConvertToAbsoluteReferences() Dim myCell As Range Dim storedCalc As Variant With Application storedCalc = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual For Each myCell In Selection.SpecialCells(xlCellTypeFormulas) myCell.Formula = Application.ConvertFormula( _ myCell.Formula, xlA1, xlA1, xlAbsolute) Next myCell .ScreenUpdating = True .EnableEvents = True .Calculation = storedCalc End With End Sub "43fan" wrote in message ... Is there any way to change a set of cells all at one time? The entire equation? I'm copying cells from one area in the worksheet to another, and I don't want them to automatically change the cell references. How can this be done? Thanks! Shawn -- It's not just based on number of championships won. Richard Petty won 200 races and 7 Daytona 500s in his 30+ year driving career. He also has the most top-5s (555), top-10s (712), poles (126), laps completed (307,836), laps led (52,194), races led (599) and consecutive races won (10 in 1967) of any driver in NASCAR history. |
#4
|
|||
|
|||
Changing A1 to $A$1.....
you either have to make the cell references that you
DON'T want to be changed to absolute by adding the $ before the row and/or column (as you show in your subject line) OR put a ' in front of your formula to make it a text string, copy the text string to the desired location and then deleting the ' -----Original Message----- Is there any way to change a set of cells all at one time? The entire equation? I'm copying cells from one area in the worksheet to another, and I don't want them to automatically change the cell references. How can this be done? Thanks! Shawn -- It's not just based on number of championships won. Richard Petty won 200 races and 7 Daytona 500s in his 30+ year driving career. He also has the most top-5s (555), top-10s (712), poles (126), laps completed (307,836), laps led (52,194), races led (599) and consecutive races won (10 in 1967) of any driver in NASCAR history. . |
Thread Tools | |
Display Modes | |
|
|