Multiple reference locking/unlockng in formulas
in [Programming]
|
From: Gladiator on 26 Apr 2010 08:28 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.
From: Mike H on 26 Apr 2010 08:42 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.
From: Gladiator on 26 Apr 2010 08:46 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.
From: Mike H on 26 Apr 2010 09:00 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.
From: Gladiator on 26 Apr 2010 09:23
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. |