Absolute referencing

[keith]

I want to change all references to cells on work sheet 1, to absolute cell
references? Is there any way I can change every cell reference in every
formula in every cell in one go, or do I have to edit every single cell.
Example of sheet data:-
=IF(ISNA(BP$74),"VB","") to equal =IF(ISNA($BP$74,"VB","") etc.

(A$3,BP$3:BP$13,1,FALSE) to equal ($A$3,$BP$3:$BP$13,1,FALSE)

I'm using Office 97!!

Thanks Keith

 

[Mike H]

Hi,

Right click the sheet tab, view code and paste this in and run it.

Sub versive()
Dim MyRange As Range
Set MyRange = ActiveSheet.UsedRange
For Each c In MyRange
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Mike

 

[Pete_UK]

You can use Edit | Replace (or CTRL-H) to find eg BP and replace with
$BP, find (A and replace with ($A etc and do this a few times
depending on how many different column references you use.

Hope this helps.

Pete

 

[keith]

thanks

Hi,

Right click the sheet tab, view code and paste this in and run it.

Sub versive()
Dim MyRange As Range
Set MyRange = ActiveSheet.UsedRange
For Each c In MyRange
If c.HasFormula Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Mike