Mass change of relative to absolute

  • Thread starter Thread starter sublimebri
  • Start date Start date
S

sublimebri

I have a large spreadsheet and i need to change all the formulae fro
relative references to absolute and i dont want to sit here and hit F
then F4 a couple of thousand times. Has anyone had this pain?

Is there a way to select all and make everything absolute? let me kno
if you need more info?

Thanks for your help ahead of time
 
VBA macro will do this. Here are four.

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

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
Next
End Sub

For info on getting started with Macros, visit David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
Thanks for your help.

Next without For


A Next statement must have a preceding For statement that matches. Thi
error has the following cause and solution:

A Next statement is used without a corresponding For statement.
Check other control structures within the For...Next structure an
verify that they are correctly matched. For example, an If without
matching End If inside the For...Next structure generates this error.

I am getting the above error. any help
 
Geez!!

Sorry about that.

Each of those Subs except the first one is missing an "End If"

Corrected versions...........

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

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub

Gord
 
Back
Top