Find and Replace - Error 91

[SanguineSleet]

Hi there,

I am running a find and search function on a column of data on a simple Macro I have created. However I seemed to be getting error 91 when it is encountering data that it cannot find.

My current code is:

Range("I:I").Select
Columns("I:I").Select
Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Replace What:="1", Replacement:="Revocable", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("I:I").Select
Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Replace What:="2", Replacement:="Irrevocable", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

However when I have data that does not have "2" in the I column it will stop and error.

Any help would be greatly appreciated!
Thanks

 

[Fluff]

Hi & welcome to the board
Will you only ever have a maximum of one "1" & one "2" in col I?

 

[SanguineSleet]

Hi & welcome to the board
Will you only ever have a maximum of one "1" & one "2" in col I?

Hi Fluff,

That is correct its our work systems and it uses numbers instead of words. The only options are 1 and 2. However I have found some raw data that only has 1 and when I try running it I get the error.

 

[SanguineSleet]

Sorry! no there can be multiple 1 and 2s in column I.

 

[Fluff]

In that case do you want to replace all 1s & all 2s, or only the first of each?

 

[SanguineSleet]

In that case do you want to replace all 1s & all 2s, or only the first of each?

I would need to replace all the 1s and 2s in the column so that they are text instead of numbers.

 

[Fluff]

In that case try

Sub Myreplace()
   With Range("I:I")
      .Replace 1, "Revocable", xlWhole, , , , False, False
      .Replace 2, "Irrevocable", xlWhole, , , , False, False
   End With
End Sub

 

[SanguineSleet]

Thank you Fluff!

That works wonderfully! =D

 

[Fluff]

Glad to help & thanks for the feedback