Replace method - cannot find any data to replace

I have code that performs a replace on any cell that contains a value
#Missing and replaces it with a 0 (zero). This code works great in Office
2000. We are upgrading to 2003 and this same code now returns an
"information window" that "Microsoft Office Excel cannot find any data to
replace...etc." It then very nicely offers some suggestions as to why this
might occur.

In Office 2000, the code would simply find nothing and continue on its merry
way. Now, it stops, which is creating a problem with the rest of the code
continuing.

Is there a way around this message window -- can it be turned off or error
trapped? The code is:

Range("A1").Select
Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False

Thanks!

I just pasted your code into a module and ran it in xl2003. There were no
#Missing characters in the worksheet.

It ran fine with not prompts.

Could it be a different line causing the error?

ps. I would think that you would want xlwhole--not xlpart, but that isn't the
cause.

Mike wrote:
>
> I have code that performs a replace on any cell that contains a value
> #Missing and replaces it with a 0 (zero). This code works great in Office
> 2000. We are upgrading to 2003 and this same code now returns an
> "information window" that "Microsoft Office Excel cannot find any data to
> replace...etc." It then very nicely offers some suggestions as to why this
> might occur.
>
> In Office 2000, the code would simply find nothing and continue on its merry
> way. Now, it stops, which is creating a problem with the rest of the code
> continuing.
>
> Is there a way around this message window -- can it be turned off or error
> trapped? The code is:
>
> Range("A1").Select
> Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
> SearchOrder:=xlByColumns, MatchCase:=False
>
> Thanks!

--

Dave Peterson

Thank you for the reply and suggestion. I talked a bit more with our
programmer and he is seeing the same thing. The Replace function is an
individual "Sub" that has been defined and called from code in other parts of
the program.

He can run that sub individually and it does work, so that does go to your
point that it runs by itself.

What has us stumped is that if we go to the code and STEP through it, it
works. It is only when we run the code from the beginning in real time (not
stepping, just running "full speed") that it fails. I could post all the
code, but it is fairly long.

We have removed pieces of the code that we thought might be troublesome (one
piece loops through the formulas on the page checking for certain conditions
before proceeding to the replace command). This function could loop several
hundred times, but was not critical to the replace step. So this was removed
and the error still showed up.

We have check sheet protection and the sheet we are working with is not
protected. Our next step is to place some debugging lines in the code to see
if something appears to be happening that is unexpected.

Any additional thoughts are appreciated!



"Dave Peterson" wrote:

> I just pasted your code into a module and ran it in xl2003. There were no
> #Missing characters in the worksheet.
>
> It ran fine with not prompts.
>
> Could it be a different line causing the error?
>
> ps. I would think that you would want xlwhole--not xlpart, but that isn't the
> cause.
>
> Mike wrote:
> >
> > I have code that performs a replace on any cell that contains a value
> > #Missing and replaces it with a 0 (zero). This code works great in Office
> > 2000. We are upgrading to 2003 and this same code now returns an
> > "information window" that "Microsoft Office Excel cannot find any data to
> > replace...etc." It then very nicely offers some suggestions as to why this
> > might occur.
> >
> > In Office 2000, the code would simply find nothing and continue on its merry
> > way. Now, it stops, which is creating a problem with the rest of the code
> > continuing.
> >
> > Is there a way around this message window -- can it be turned off or error
> > trapped? The code is:
> >
> > Range("A1").Select
> > Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
> > SearchOrder:=xlByColumns, MatchCase:=False
> >
> > Thanks!
>
> --
>
> Dave Peterson
>

I don't have any other suggestions.

I've never seen VBA display that message when there was no data to change.
(Well, I can't recall ever seeing it.)



Mike wrote:
>
> Thank you for the reply and suggestion. I talked a bit more with our
> programmer and he is seeing the same thing. The Replace function is an
> individual "Sub" that has been defined and called from code in other parts of
> the program.
>
> He can run that sub individually and it does work, so that does go to your
> point that it runs by itself.
>
> What has us stumped is that if we go to the code and STEP through it, it
> works. It is only when we run the code from the beginning in real time (not
> stepping, just running "full speed") that it fails. I could post all the
> code, but it is fairly long.
>
> We have removed pieces of the code that we thought might be troublesome (one
> piece loops through the formulas on the page checking for certain conditions
> before proceeding to the replace command). This function could loop several
> hundred times, but was not critical to the replace step. So this was removed
> and the error still showed up.
>
> We have check sheet protection and the sheet we are working with is not
> protected. Our next step is to place some debugging lines in the code to see
> if something appears to be happening that is unexpected.
>
> Any additional thoughts are appreciated!
>
> "Dave Peterson" wrote:
>
> > I just pasted your code into a module and ran it in xl2003. There were no
> > #Missing characters in the worksheet.
> >
> > It ran fine with not prompts.
> >
> > Could it be a different line causing the error?
> >
> > ps. I would think that you would want xlwhole--not xlpart, but that isn't the
> > cause.
> >
> > Mike wrote:
> > >
> > > I have code that performs a replace on any cell that contains a value
> > > #Missing and replaces it with a 0 (zero). This code works great in Office
> > > 2000. We are upgrading to 2003 and this same code now returns an
> > > "information window" that "Microsoft Office Excel cannot find any data to
> > > replace...etc." It then very nicely offers some suggestions as to why this
> > > might occur.
> > >
> > > In Office 2000, the code would simply find nothing and continue on its merry
> > > way. Now, it stops, which is creating a problem with the rest of the code
> > > continuing.
> > >
> > > Is there a way around this message window -- can it be turned off or error
> > > trapped? The code is:
> > >
> > > Range("A1").Select
> > > Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
> > > SearchOrder:=xlByColumns, MatchCase:=False
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

We finally figured out a solution to the message, but we're still puzzled as
to the why -- why it works when stepping, but not when running.

We went as far as to putting a breakpoint in at the "replace", ran full
speed to the breakpoint, then ran again to continue and the message didn't
appear. But, putting code in just before the "replace" to make it wait ten
seconds, then automatically continue after the wait, the window pops up.

Our solution was to put:

application.DisplayAlerts = false

just before the "replace", then reset to true just after the "replace". The
code runs fine.

Again, thank you for the thoughts.

"Dave Peterson" wrote:

> I don't have any other suggestions.
>
> I've never seen VBA display that message when there was no data to change.
> (Well, I can't recall ever seeing it.)
>
>
>
> Mike wrote:
> >
> > Thank you for the reply and suggestion. I talked a bit more with our
> > programmer and he is seeing the same thing. The Replace function is an
> > individual "Sub" that has been defined and called from code in other parts of
> > the program.
> >
> > He can run that sub individually and it does work, so that does go to your
> > point that it runs by itself.
> >
> > What has us stumped is that if we go to the code and STEP through it, it
> > works. It is only when we run the code from the beginning in real time (not
> > stepping, just running "full speed") that it fails. I could post all the
> > code, but it is fairly long.
> >
> > We have removed pieces of the code that we thought might be troublesome (one
> > piece loops through the formulas on the page checking for certain conditions
> > before proceeding to the replace command). This function could loop several
> > hundred times, but was not critical to the replace step. So this was removed
> > and the error still showed up.
> >
> > We have check sheet protection and the sheet we are working with is not
> > protected. Our next step is to place some debugging lines in the code to see
> > if something appears to be happening that is unexpected.
> >
> > Any additional thoughts are appreciated!
> >
> > "Dave Peterson" wrote:
> >
> > > I just pasted your code into a module and ran it in xl2003. There were no
> > > #Missing characters in the worksheet.
> > >
> > > It ran fine with not prompts.
> > >
> > > Could it be a different line causing the error?
> > >
> > > ps. I would think that you would want xlwhole--not xlpart, but that isn't the
> > > cause.
> > >
> > > Mike wrote:
> > > >
> > > > I have code that performs a replace on any cell that contains a value
> > > > #Missing and replaces it with a 0 (zero). This code works great in Office
> > > > 2000. We are upgrading to 2003 and this same code now returns an
> > > > "information window" that "Microsoft Office Excel cannot find any data to
> > > > replace...etc." It then very nicely offers some suggestions as to why this
> > > > might occur.
> > > >
> > > > In Office 2000, the code would simply find nothing and continue on its merry
> > > > way. Now, it stops, which is creating a problem with the rest of the code
> > > > continuing.
> > > >
> > > > Is there a way around this message window -- can it be turned off or error
> > > > trapped? The code is:
> > > >
> > > > Range("A1").Select
> > > > Cells.Replace What:="#Missing", Replacement:="0", LookAt:=xlPart, _
> > > > SearchOrder:=xlByColumns, MatchCase:=False
> > > >
> > > > Thanks!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Just a wild guess, but could you use something like "On Error Resume
Next" to just ignore the error and move on?