Alex,
This is another way of doing the same thing but with a validation to maybe
save you some time in your code.
If you have to do multiple changes inside a range of cells (as you mentioned
J2 through J22351 (or whatever) then one way to go about it is to check to
see if what you need to change is even there in the first place. If at least
1 example is found then your find / replace will proceed, if not your code
will move on to whatever you have next.
I kept the MsgBox in the code (for troubleshooting purposes) but they are
commentated out as they are not needed.
Sub TryThis
Dim CountIfAnswer as Long
Dim rToSearch as Range
CountIfAnswer = WorksheetFunction.CountIf(Range("J:J").EntireColumn,
"<your search here>")
If CountIfAnswer >= 1 Then
Set rToSearch = Range(Cells(2, 10), Cells(Rows.Count, 10).End(xlUp))
rToSearch.Replace What:="<your search here>", Replacement:="<what
you need here>", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'MsgBox (CountIfAnswer)
Else
' MsgBox ("Nothing Found")
End If
End Sub
What ‘Set rToSearch’ does is force Excel to only do a find / replace inside
a range of cells that you are specify vs letting excel iterate through each
cell 1 by 1 until it finds something (A1, A2, A3, B1, B2, B3, etc etc). This
forces Excel to search only where you want it (J2, J3, J4, J5, …).
If you don’t want to perform the check first, omit the ‘CountIfAnswer’ part
and the reference to the 'If .. Then' statements and the code will work.
Hope this helps,
Jason