Creating macro for find and replace

[hidaya]

I recorded a macro for find value 0 and replace with
nothing. However after I run it on another excel
worksheet, there is an error.
Run-time error 91:
Object variable or with block variable not set.
Is there any way to rectify this?
Thank you for your help

 

[Bernie Deitrick]

hidaya,

It would help if you would post your code.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,
There code is:-
Columns("E:E").Select
Range("E9").Activate
Selection.Find(What:="00-01-1900", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Replace What:="00-01-1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

Thank you.

 

[Guest]

Hi Bernie,
The code is :-

Columns("E:E").Select
Range("E9").Activate
Selection.Find(What:="00-01-1900", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Replace What:="00-01-1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Bernie Deitrick]

When the value isn't found, you can't activate the cell. The way around that
is to test prior to acting on the cell.

Try it like this:

Dim myCell As Range

Columns("E:E").Select
Range("E9").Activate
Set myCell = Selection.Find(What:="00-01-1900", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not myCell Is Nothing Then
myCell.Replace What:="00-01-1900", _
Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End If

Note that the default date formatting may affect how the find and replace is
done for the value 0, which may be why you are having a problem with running
it on another sheet.

HTH,
Bernie
MS Excel MVP