Dollar SIgns in Formulas

M

Mascot

I have formulas in multiple cells that have dollar signs in the formula. So
when I copy and paste the formulas won't change. However I want them to
change. So is there a way I can clear the dollar signs in multiple Cells at
once So I don't have to do one by on?
 
G

Guest

Hi
Select your range. Hit F2 and then click, in the formula bar, on the
reference you want to change. Each time you hit F4, the reference will
change through each relative and absolute option. When you see the one you
want, hit Ctrl+Enter.
Make sure you take a copy of the sheet before you start.
 
B

Bernie Deitrick

Mascot,

Select your cells, and run the macro below. And, yes, I know that Intersect
command looks weird, but Excel will select all cells with formulas if you
don't do it that way with a single cell selected.....

HTH,
Bernie
MS Excel MVP

Sub RemoveAbsoluteReferences()
Dim myCell As Range

For Each myCell In Intersect(Selection, Selection. _
SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, xlRelative)
Next myCell

End Sub
 
M

Mascot

Hi Bernie,

Thanks for the help this macro works perfectly. You saved me a lot of time.

Mascot
 
Top