|
Posted to microsoft.public.excel.programming
|
|
|
Multiple reference locking/unlockng in formulas
Thanks Mike. It worked just fine.
"Mike H" wrote:
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" wrote:
Mike thanks, but how do I do massive reference lock?
"Mike H" wrote:
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" wrote:
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.
|