Multiple reference locking/unlockng in formulas

[Gladiator]

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

 

[Mike H]

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Gladiator]

Mike thanks, but how do I do massive reference lock?

 

[Mike H]

Hi,

Try this macro. To install it ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code in. Select your data and
run the code, you will be prompted whether you want relative or absolute

Sub ChangeRef()
response = InputBox("Enter 1 for relative or 2 for absolute")
Select Case response
Case Is = 1
RefType = xlRelative
Case Is = 2
RefType = xlAbsolute
Case Else
Exit Sub
End Select
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, RefType)
End If
Next c

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Gladiator]

Thanks Mike. It worked just fine.

Hi,

Try this macro. To install it ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code in. Select your data and
run the code, you will be prompted whether you want relative or absolute

Sub ChangeRef()
response = InputBox("Enter 1 for relative or 2 for absolute")
Select Case response
Case Is = 1
RefType = xlRelative
Case Is = 2
RefType = xlAbsolute
Case Else
Exit Sub
End Select
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, RefType)
End If
Next c

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.