Find/replace function in a protected sheet

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

Can we have a sheet, not a picture of one????

Well, you learn something new every day!! I hadn't realised that this was not possible. You could do it with VBA (but I couldn't - VBA is a dark art to me!!) so here is a formula based alternative.

Your "numbers" on rows 2, 4, etc are text stored as numbers, so select them, row at a time, and convert to numbers

With a lookup table of numbers and questions in R &S, use this, copied across and down:

=IF(ISODD(ROW(F7)),F7,VLOOKUP(F7,$R:$S,2,FALSE))

Thanks. I'll try out your method for sure. Before you posted,while desperately searching for solution, I found a macro. Probably from here. A post made 14 years ago on this forum. I'm not allowed to post urls, so used a [dot] in place of actual dot.

excelforum [dot] com/excel-general/474505-find-and-replace-within-unlocked-cells-in-a-protected-worksheet.html


Sharing it here. I just blindly copy pasted it and used it. I am zero in programming.


Option Explicit
Sub testme()
Dim fStr As String
Dim tStr As String
Dim myRng As Range
Dim myUnlockedCells As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myPWD As String

myPWD = "hi"

Set wks = ActiveSheet

With wks

If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
'keep going
Else
MsgBox "Sheet is unprotected--just use Edit|Replace!"
Exit Sub
End If

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, .UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select cells in the used range"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.Locked = False Then
If myUnlockedCells Is Nothing Then
Set myUnlockedCells = myCell
Else
Set myUnlockedCells = Union(myUnlockedCells, myCell)
End If
End If
Next myCell

If myUnlockedCells Is Nothing Then
MsgBox "No unlocked cells in the selected range"
Exit Sub
End If

fStr = InputBox(Prompt:="Change what")
If Trim(fStr) = "" Then
Exit Sub
End If

tStr = InputBox(Prompt:="To what")
If Trim(tStr) = "" Then
Exit Sub
End If

.Unprotect Password:=myPWD

If myUnlockedCells.Cells.Count = 1 Then
Set myUnlockedCells _
= Union(myUnlockedCells, _
.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1))
End If

On Error Resume Next
myUnlockedCells.Cells.Replace what:=fStr, _
replacement:=tStr, lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False
If Err.Number <> 0 Then
MsgBox "An error occurred during the mass change!"
Err.Clear
End If
On Error Goto 0

.Protect Password:=myPWD

End With

End Sub