Changing A1 to $A$1.....

[43fan]

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 Deitrick]

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]

Bernie,

Worked great! Thanks!

Shawn

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

 

[jr]

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 '