Absolute Question

[Peter Muller]

Good evening,

I have a summary sheet with 600+ rows, info all sourced from other sheets in the workbook.

Is it all possible to make all the addresses absolute once off without having to go to each row and select F4?

I am really hoping this is possible and that you can assist me.

Peter

 

[lrobbo314]

This seems to work.

Just select the range you want to convert then run the code.

Sub toABS()
Dim r As Range:     Set r = Selection
Dim cel As Range

For Each cel In r
    cel.Formula = Application.ConvertFormula(cel.Formula, xlA1, xlA1, 1)
Next cel
End Sub

 

[6StringJazzer]

All the formulas referencing the other sheets all the same format, or do they vary? It may be possible to do this with Find and Replace, without VBA, depending on what the formulas look like.

If you are comfortable with VBA then post #2 is probably your quickest solution.

 

[Peter Muller]

This seems to work.

Just select the range you want to convert then run the code.

Sub toABS()
Dim r As Range:     Set r = Selection
Dim cel As Range

For Each cel In r
    cel.Formula = Application.ConvertFormula(cel.Formula, xlA1, xlA1, 1)
Next cel
End Sub

This is perfect, thank you so much.

You are saving me tons of time