Multiple reference locking/unlockng in formulas
in [Programming]
|
From: Mike H on 26 Apr 2010 09:40 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. "Gladiator" wrote: > 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. |