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.

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.

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.

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.



.