Help with Traping (I believe) a no-Find() error throuh the us of a seemingly unrelated functiom

E

EagleOne

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
 
E

EagleOne

Joel, thanks for your our thoughts. Your idea will work with a few lines of code.

That said, in the situation about which I commented, there was a search or find like function where
the result was a not easily trappable. Yet an MVP suggested the use of a seemingly unrelated VBA
/Excel function to trap the no-find.

If I figure out the situation again I'll post back. It was a very clever use of code.
 
D

Dave Peterson

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.
 
E

EagleOne

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.
 
D

Dave Peterson

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

Maybe that's where the confusion occurred???
 
E

EagleOne

My 1st guess was that you were most likely that MVP as your solutions are clever.

Please do not waste any more of your time on this issue. I'll go back in time looking for Match
coupled with isnumeric and Find coupled with isnumeric.

EagleOne
 
E

EagleOne

Thanks Dave!


I found it! Message-ID: <[email protected]>

I was attempting a SumProduct "Search/Find and attempting to Trap a #VALUE issue:

*********************************************************************************************
Ron Rosenfield:
*********************************************************************************************
And yes, Search/Find returns a #VALUE error if the substring is not found; and ISNUMBER takes care
of that issue.

Glad to help. Thanks for the feedback.

-ron

*********************************************************************************************

Your use of ISNUMERIC is very clever.

In the end, this is how I deployed it:

=SUMPRODUCT((ISNUMBER(SEARCH("IHN",$D$2:$D$1745)))*($R$2:$R$1745=$B$1795))

Thanks for your help now and over time!

EagleOne
 
D

Dave Peterson

You confused me <vbg>.

Since you posted in the .programming newsgroup and used VBA's isnumeric
function, I guessed that you were writing about macros.

And you really were interested in worksheet functions!
 
E

EagleOne

I was not sure myself. My apologies. Thanks

Dave Peterson said:
You confused me <vbg>.

Since you posted in the .programming newsgroup and used VBA's isnumeric
function, I guessed that you were writing about macros.

And you really were interested in worksheet functions!
Click to expand...
 
N

nj

Hi, folks,

I just used Dave's code below with a .find in a macro I'm working
(worked perfectly, of course). Now I have a followup question... might
be a couple of questions, I suppose.

This occurs in a child macro and "If foundcell is nothing", what needs
to happen is for that child macro to end and control return to the
parent macro. I've played around with a couple commands but I'm not
getting exactly the behavior I'm after.

I was kind of thinking it would be Exit, but maybe with a parameter or
something. So question #1 is, is there something like that?

But now I'm suspected this is running me back to an old weakness in my
coding skills, namely error-trapping, so question #2 is, can any of
you point me to a webpage will full-on coverage of VBA error trapping?
I understand it in theory but have never gotten around to using it, so
every time I try, it seems like syntax stops (and I find a workaround
faster).

Along the same lines, I've dodged arrays for these many long years,
but would really like to master those puppies. Any place with a
comprehensive coverage of that?

Honestly, my ideal would have exercises, since my brain is being
resistant - lol. How sad is that?!

Thanks so much. This group is a killer resource - I tell people about
this forum all the time.

nj
 
N

nj

OK, I figured out "Exit Sub" and then realized I had structured the
contents of the If statement poorly and didn't need it. LOL

But I'd still love direction to some websites about error trapping and
(unrelated) array manipulation.

thanks!
 
Top