Sub FindReplaceAll()
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim x As Long
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Please select the range with the mouse", Title:="Selection required", Type:=8)
answ = 1
If rng.Cells.Count = 1 Then answ = MsgBox("Only one cell selected so this will affect the whole worksheet" & vbLf & "Contine?", vbOKCancel, "")
If answ = 1 Then
fndList = Array("Unsubstantiated", "Substantiated", "Insufficient information to substantiate")
rplcList = Array("Invalid", "Valid", "Insufficient")
For x = LBound(fndList) To UBound(fndList)
rng.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next x
End If
End Sub
Edit after posting:
After doing some testing, the message about only one cell is not necessary on my version of Excel (Office 365) - it used to be. So depending on what version of Excel you have the code could be shortened:
Sub FindReplaceAll()
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim x As Long
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Please select the range with the mouse", Title:="Selection required", Type:=8)
fndList = Array("Unsubstantiated", "Substantiated", "Insufficient information to substantiate")
rplcList = Array("Invalid", "Valid", "Insufficient")
For x = LBound(fndList) To UBound(fndList)
rng.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next x
End If
End Sub