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

  • Thread starter Thread starter 43fan
  • Start date Start date
4

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.
 
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
 
Bernie,

Worked great! :) Thanks!

Shawn


Bernie Deitrick said:
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
Click to expand...
 
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 '
 
Back
Top