Absolute referencing
 

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

Absolute referencing
 

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

"keith" wrote:

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

Absolute referencing
 

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

On Sep 5, 8:59*am, keith wrote:
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

Absolute referencing
 

thanks

"Mike H" wrote:

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

"keith" wrote:

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