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

You can simulate an error using the error statement

Error errornumber


So if you want to trap on an condition put this in an IF statement

if isnumeric() then Error 123


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149399

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.

joel wrote:


You can simulate an error using the error statement

Error errornumber


So if you want to trap on an condition put this in an IF statement

if isnumeric() then Error 123

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

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

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

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

Dave Peterson wrote:

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

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


On Oct 30, 7:48*am, 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 useVBA'sinstr() instead.

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!

On Nov 3, 10:05*am, nj wrote:
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

On Oct 30, 7:48*am, 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 useVBA'sinstr() instead.- Hide quoted text -

- Show quoted text -

I'd start with Chip Pearson's page about error trapping:
http://www.cpearson.com/Excel/ErrorHandling.htm

I don't know of any resource that can serve as a tutorial on arrays. But you
could try searching google for vba array and whatever specific question you
have.



nj wrote:

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!

On Nov 3, 10:05 am, nj wrote:
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

On Oct 30, 7:48 am, 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 useVBA'sinstr() instead.- Hide quoted text -

- Show quoted text -

--

Dave Peterson