Locking multiple cells (F4 key)

[kestrel]

I'm often locking a formula in a cell so that the cell reference number
doesn't change upon copying and pasting.

E.g.

=A2+5
becomes
=$A$2+5
or sometimes I choose
=A$2+5
etc.

I need to do this for around 100 different cells, and then change the
lock on them.

Is there a quick way I can lock multiple cells in the same way, rather
than going through each one and pressing 'F4'?

Thanks

 

[Pete_UK]

You could use Find and Replace (CTRL-H), depending on what your
formulae look like. In your example above, you could:

Find What: A
Replace with: $A$

(or A$, depending on the circumstances)

Hope this helps.

Pete

 

[Bernie Deitrick]

Kestrel,

Select your cells, then run the macro below.

HTH,
Bernie
MS Excel MVP


Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
Dim RefStyle As Variant
Dim MyMsg As String
Dim myStyle As Integer

MyMsg = "1: =A1 Relative" & Chr(10) & _
"2: =A$1 Absolute Row" & Chr(10) & _
"3: =$A1 Absolute Column" & Chr(10) & _
"4: =$A$1 Absolute" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, 3, or 4...."
myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)

Select Case myStyle
Case 1
RefStyle = xlRelative
Case 2
RefStyle = xlAbsRowRelColumn
Case 3
RefStyle = xlRelRowAbsColumn
Case Else
RefStyle = xlAbsolute
End Select

With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual

For Each myCell In Intersect(Selection, Selection.SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, RefStyle)
Next myCell

.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With

 

[Financier]

Hi Bernie,

I just used your macro and its worked wonders.

Many thanks

Financier