But I do use isnumeric (or iserror) when working with application.match().

Maybe that's where the confusion occurred???

wrote:

The master has arrived!

Your example is excellent!

That said, over the summer I was working two distinct coding projects 1) classic VBA for typical
Excel worksheet handling and 2) converting VBA to VB.NET projects.

Somewhere in those projects, I was having my code blowup due to a "no-find" or a Null-like
situation.

To solve the specific glitch, an MPV suggested the marriage of two functions the second of which in
a very unique way, controlled/trapped the null or no-find. That second Function?? seemed to have
nothing to do with error trapping BUT it worked in this special case!!

I thought I'd never forget it.

Sorry Dave for the blind alley. I promise that I'll post back when I find it. It was very very
clever. I have spent the better part of two days attempting to find it. Just obsessed I guess.

Dave Peterson wrote:

If you're using the Find method, I think you'll be better served by using
something like:

Dim FoundCell as Range
...

with someRangeHere
set foundcell = .cells.find(What:="something", After:=.cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End with

If foundcell is nothing then
'not found, what should happen
else
'found it. Do what you want to FoundCell here
foundcell.offset(0,1).clearconents 'whatever
end if

==========
If you're using worksheetfunction.find() to check for something in a string,
then use VBA's instr() instead.



wrote:

2003 - 2007

Last year I needed help trapping a no-find error in VBA for Excel.

I think that the answer used isnumeric() to trap the error from blowing up succeeding VBA.

In short, the initial function would throw an error which was not easily trapped.

One of the MVP crew, used I believe an isnumeric() to trap that error.

I thought I had documented that synergistic coupling of functions but did not.

Anyone have ideas of coupling seemingly unrelated functions to trap?

TIA EagleOne

--

Dave Peterson