Making Cells Absolute

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

I Have a spreadsheet that has 75 columns with data that is
from two other sheets. Stupid me when i did the formula I
did not make it absolute, so now when I sort it the
formulas change. Is there anyway I can make the formulas
absolute without going into every cell and changing? Any
help will be greatly appreciated. Thank You
 
Hi Mac

Gord posted this

****************************

Only through VBA macro.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)

'line above can be altered for varying references as below
'(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
'(cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
'(cell.Formula, xlA1, xlA1, xlRelative)

End If
Next
End Sub

Gord Dibben Excel MVP
 
also, try downloading ASAP Utilities. this function, and many man
others, is included and sounds like what you need here.

i've used it many times, and have been very pleased with the results.

just don't download the Perpetual Workbook. if you do and the
uninstall it, you'll lose your ASAP Utilities possibly.

HT
 
Back
Top