Hello,

Sorry to raise this issue again, but I'm still having trouble using the IS
functions, here's the latest manifestation (running Excel 97)

This formula works perfectly fine in my spreadsheet:

=IF( ISNA(MATCH(0,A1:H1,0)),"Zero NOT found","Zero found")

However, a similar statement in VBA code generates an error message if '0'
is not present (e.g., N/A), but works if '0' is present.

Set rng = Range("A1:H1")
If WorksheetFunction.IsNA(WorksheetFunction.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

Here's the error message:

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class.

Am I doing something wrong - can't seem to spot it? Appreciate any
suggestions.

Thanks,

Dave

This is a bug. Change Worksheetfunction.Match to just Match.

Also, you don't need to use the worksheet function ISNA function here. You can
write it as

If IsError(Match(0, Rng, 0)) Then


On Thu, 24 Mar 2005 22:30:05 -0600, "David Unger"
wrote:

Hello,

Sorry to raise this issue again, but I'm still having trouble using the IS
functions, here's the latest manifestation (running Excel 97)

This formula works perfectly fine in my spreadsheet:

=IF( ISNA(MATCH(0,A1:H1,0)),"Zero NOT found","Zero found")

However, a similar statement in VBA code generates an error message if '0'
is not present (e.g., N/A), but works if '0' is present.

Set rng = Range("A1:H1")
If WorksheetFunction.IsNA(WorksheetFunction.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

Here's the error message:

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class.

Am I doing something wrong - can't seem to spot it? Appreciate any
suggestions.

Thanks,

Dave

Hi Myrna,

Using the IsError function doesn't seem to change anything.

If I change Worksheetfunction.Match to just Match I get an error
message: "Compile Error - Sub or Function not defined."

Can't seem to win!

Dave

I think Myrna means change Worksheetfuunction to Application

Set rng = Range("A1:H1")
If IsError(Application.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Unger" wrote in message
ups.com...
Hi Myrna,

Using the IsError function doesn't seem to change anything.

If I change Worksheetfunction.Match to just Match I get an error
message: "Compile Error - Sub or Function not defined."

Can't seem to win!

Dave

see response in programming.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David Unger" wrote in message
...
Hello,

Sorry to raise this issue again, but I'm still having trouble using the IS
functions, here's the latest manifestation (running Excel 97)

This formula works perfectly fine in my spreadsheet:

=IF( ISNA(MATCH(0,A1:H1,0)),"Zero NOT found","Zero found")

However, a similar statement in VBA code generates an error message if '0'
is not present (e.g., N/A), but works if '0' is present.

Set rng = Range("A1:H1")
If WorksheetFunction.IsNA(WorksheetFunction.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

Here's the error message:

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class.

Am I doing something wrong - can't seem to spot it? Appreciate any
suggestions.

Thanks,

Dave

I agree with Myrna as to using Application.Match instead of
Worksheetfunction Match, and iserror instead of IsNa, but if you want to use
worksheetfunction, you have to trap the error you complain about - this is
how worksheetfunction.match signals that a match is not found

Sub ABCD()
Dim rng As Range
Dim res As Long
Set rng = Range("A1:A10")
res = -1
On Error Resume Next
res = WorksheetFunction.Match(0, rng, 0)
On Error GoTo 0
If res = -1 Then
MsgBox "Zero was Not found"
Exit Sub
Else
MsgBox "Zero was found"
End If

End Sub


--
Regards,
Tom Ogilvy


"David Unger" wrote in message
...
Hello,

Sorry to raise this issue again, but I'm still having trouble using the IS
functions, here's the latest manifestation (running Excel 97)

This formula works perfectly fine in my spreadsheet:

=IF( ISNA(MATCH(0,A1:H1,0)),"Zero NOT found","Zero found")

However, a similar statement in VBA code generates an error message if '0'
is not present (e.g., N/A), but works if '0' is present.

Set rng = Range("A1:H1")
If WorksheetFunction.IsNA(WorksheetFunction.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

Here's the error message:

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class.

Am I doing something wrong - can't seem to spot it? Appreciate any
suggestions.

Thanks,

Dave

Tom,

I think you've solved it for me - if I use Application.Match it works
as I intended! I guess what confused me is using Match without the
Application in front of it generates an error message, for some reason
my VBA does not recognize Match by itself. And for some reason, when I
type Application., Match does NOT appear on the pop-up list. Go
figure! Anyway, now that I know about this (bug?) I can carry on.
Thanks ever so much,

Dave

Hi Bob,

You're right, see my reply to Tom - live and learn I guess. Thanks for
your help,

Dave

Maybe I misread your original post. I thought you had

Application.WorksheetFunction.Match

That must be changed to

Application.Match


On 24 Mar 2005 22:50:28 -0800, "Dave Unger" wrote:

Hi Myrna,

Using the IsError function doesn't seem to change anything.

If I change Worksheetfunction.Match to just Match I get an error
message: "Compile Error - Sub or Function not defined."

Can't seem to win!

Dave

Match without the Application in front of it generates an error message, for
some reason my VBA does not recognize Match by itself

VBA doesn't recognize ANY worksheet functions "by itself". You have to tell it
where to find the function by preceding it by Application or
Application.WorksheetFunction.

And for some reason, when I type Application., Match does NOT appear on the
pop-up list.

That's because they want you to use the Application.Worksheetfunction syntax.
The fact that MATCH doesn't always work with the new syntax is a bug that they
didn't discover and/or fix.

You're actually using a worksheet function in VBA.
(like: =match(a1,b:b,0) in a cell)

If you had typed:
application.worksheetfunction.
You'd get Match to show up in that autocomplete list.

But, as you've seen, application.worksheetfunction.match and application.match
behave differently when it comes to errors.

I'm willing to live with the lack of popup to get the behavior I like.

Dave Unger wrote:

Tom,

I think you've solved it for me - if I use Application.Match it works
as I intended! I guess what confused me is using Match without the
Application in front of it generates an error message, for some reason
my VBA does not recognize Match by itself. And for some reason, when I
type Application., Match does NOT appear on the pop-up list. Go
figure! Anyway, now that I know about this (bug?) I can carry on.
Thanks ever so much,

Dave

--

Dave Peterson

Hi Myrna,

Let's see if I'm understanding you right, correct me if I'm wrong.

CountA is a worksheetfunction, all the following statements work:

cnt = Application.WorksheetFunction.CountA(rng)
cnt = WorksheetFunction.CountA(rng)
cnt = Application.CountA(rng)

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Dave

"Dave Unger" wrote in message
oups.com...

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Isn't that generally the way with bugs?

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

Isn't quite true.

Both of them work fine. But they each handle errors (#n/a in the worksheet)
differently.

worksheetfunction.match raises a runtime error (1004) that you can check one way
and application.match returns an error that you can check another way.

I personally like the application.match() syntax. I just find it simpler.



Dave Unger wrote:

Hi Myrna,

Let's see if I'm understanding you right, correct me if I'm wrong.

CountA is a worksheetfunction, all the following statements work:

cnt = Application.WorksheetFunction.CountA(rng)
cnt = WorksheetFunction.CountA(rng)
cnt = Application.CountA(rng)

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Dave

--

Dave Peterson

In xl97 at least, worksheetFunction.Match didn't always work fine.
Sometimes it did and sometimes it didn't. Or to summarize, it usually
worked, but wasn't reliable. That was Myrna's point. On the other hand,
never had a problem with Application.Match. These are my own experiences
and Myrna apparently shares those. Your comments on error handling are, of
course, correct.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

Isn't quite true.

Both of them work fine. But they each handle errors (#n/a in the
worksheet)
differently.

worksheetfunction.match raises a runtime error (1004) that you can check
one way
and application.match returns an error that you can check another way.

I personally like the application.match() syntax. I just find it simpler.



Dave Unger wrote:

Hi Myrna,

Let's see if I'm understanding you right, correct me if I'm wrong.

CountA is a worksheetfunction, all the following statements work:

cnt = Application.WorksheetFunction.CountA(rng)
cnt = WorksheetFunction.CountA(rng)
cnt = Application.CountA(rng)

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Dave

--

Dave Peterson

Thanks for the correction. I didn't recall this problem in xl97.

Tom Ogilvy wrote:

In xl97 at least, worksheetFunction.Match didn't always work fine.
Sometimes it did and sometimes it didn't. Or to summarize, it usually
worked, but wasn't reliable. That was Myrna's point. On the other hand,
never had a problem with Application.Match. These are my own experiences
and Myrna apparently shares those. Your comments on error handling are, of
course, correct.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

Isn't quite true.

Both of them work fine. But they each handle errors (#n/a in the
worksheet)
differently.

worksheetfunction.match raises a runtime error (1004) that you can check
one way
and application.match returns an error that you can check another way.

I personally like the application.match() syntax. I just find it simpler.



Dave Unger wrote:

Hi Myrna,

Let's see if I'm understanding you right, correct me if I'm wrong.

CountA is a worksheetfunction, all the following statements work:

cnt = Application.WorksheetFunction.CountA(rng)
cnt = WorksheetFunction.CountA(rng)
cnt = Application.CountA(rng)

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Dave

--

Dave Peterson

--

Dave Peterson

Thanks Dave,

Allow me to bounce this around a bit more, just to see if I've "got it"
- again, correct me where I'm wrong.

The full syntax for a worksheet function (such as MATCH) is
Application.WorksheetFunction.Match

VBA allows abbreviations such as WorksheetFunction.Match and
Application.Match, so these are 3 equivalent ways of calling the same
worksheet function. Am I correct up to this point?

Now, even though I'm calling the exact same function in all 3 cases, I
should be aware of a "bug", which causes WorksheetFunction.Match to
handles errors differently than Application.Match - which makes it seem
like you're calling 2 different functions, but you're not.

Is it possible other worksheet functions may also exhibit differences,
depending on the method used to call them?

thanks,

Dave Unger

Tom,

"Experience" seems to be the key word, thanks for sharing it.

Dave

I'm gonna jump in (even with my bad memory of xl97!). Tom or Myrna can yell,
er, correct me where I'm wrong.

Both application.match and worksheetfunction.match call the same excel function.

But I don't think Myrna meant that the bug was the different ways each handles
errors. (It seems inconsistent to me--but not quite a bug.)

I think Myrna (and Tom) meant the bug was that worksheetfunction.match didn't
return the correct value all the time in xl97.

And that's a lot more serious.

application.vlookup() and worksheetfunction.vlookup() have the same error
handling difference. I don't know if xl97 had a similar bug.

As for me, I'd dump the worksheetfunction version all the time. xl2003 still
supports application.functionnamehere().

I think it makes life much simpler.

I don't think the autocomplete (arg1, arg2, arg3) helps in any way. If I need
help, I'll swap back to excel and look at help.

(Here's hoping I'm closer to correct!)



Dave Unger wrote:

Thanks Dave,

Allow me to bounce this around a bit more, just to see if I've "got it"
- again, correct me where I'm wrong.

The full syntax for a worksheet function (such as MATCH) is
Application.WorksheetFunction.Match

VBA allows abbreviations such as WorksheetFunction.Match and
Application.Match, so these are 3 equivalent ways of calling the same
worksheet function. Am I correct up to this point?

Now, even though I'm calling the exact same function in all 3 cases, I
should be aware of a "bug", which causes WorksheetFunction.Match to
handles errors differently than Application.Match - which makes it seem
like you're calling 2 different functions, but you're not.

Is it possible other worksheet functions may also exhibit differences,
depending on the method used to call them?

thanks,

Dave Unger

--

Dave Peterson

the WorksheetFunction object wasn't introduced until xl97. Obviously, since
VBA was introduced in xl5, you still had the means to use worksheet
functions in VBA. This was through the Application Object. This capability
has been preserved for compatibility.

Any function that can return an error could be handled with the iserror
check using Application as the qualifier. For example, taking the average
of a blank range causes an error

? application.Average(Range("A1:A10"))
Error 2007
? iserror(application.Average(Range("A1:A10")))
True

worksheetFunction.Average(Range("A1:A10"))

raises a trappable error, the 1004 error. This error is produced by the
worksheetfunction object.

the bug Myrna talked about was that Match and the Lookup functions didn't
always seem to work in xl97 using worksheetfunction as a qualifier. the
difference in error handling is by design and is not a bug. Different
object qualifiers, different code execution path, different behavior.

--
Regards,
Tom Ogilvy

"Dave Unger" wrote in message
oups.com...
Thanks Dave,

Allow me to bounce this around a bit more, just to see if I've "got it"
- again, correct me where I'm wrong.

The full syntax for a worksheet function (such as MATCH) is
Application.WorksheetFunction.Match

VBA allows abbreviations such as WorksheetFunction.Match and
Application.Match, so these are 3 equivalent ways of calling the same
worksheet function. Am I correct up to this point?

Now, even though I'm calling the exact same function in all 3 cases, I
should be aware of a "bug", which causes WorksheetFunction.Match to
handles errors differently than Application.Match - which makes it seem
like you're calling 2 different functions, but you're not.

Is it possible other worksheet functions may also exhibit differences,
depending on the method used to call them?

thanks,

Dave Unger

Hi Tom,

Ok, this sheds an entirely new light on the subject (for me). I was
under the impression that, whatever qualifier you used (Application,
WorksheetFunction, Application.WorksheetFunction), it was all the same,
the compiler would parse it all down, and generate the same run-time
code. That's why I couldn't understand the different error handling.
But, if it's done by the object, that's a different matter. I don't
think I've come across this in any of my reading, guess I'll have to
delve a little deeper.

Thanks for now,

Dave

never had a problem with Application.Match. These are my own experiences
and Myrna apparently shares those.

Yes. That's been my experience. I've given up on Worksheetfunction.Match. It
isn't worth the hassle.

My experience has also been that WorksheetFunction.Match may cause a run-time
error when in fact there IS a match and I should be getting a numeric result.
In such cases, writing it as Application.Match gives the expected (and
correct) result).


On 26 Mar 2005 17:12:49 -0800, "Dave Unger" wrote:

Hi Tom,

Ok, this sheds an entirely new light on the subject (for me). I was
under the impression that, whatever qualifier you used (Application,
WorksheetFunction, Application.WorksheetFunction), it was all the same,
the compiler would parse it all down, and generate the same run-time
code. That's why I couldn't understand the different error handling.
But, if it's done by the object, that's a different matter. I don't
think I've come across this in any of my reading, guess I'll have to
delve a little deeper.

Thanks for now,

Dave

Post XL97?

Bob


"Myrna Larson" wrote in message
...
My experience has also been that WorksheetFunction.Match may cause a
run-time
error when in fact there IS a match and I should be getting a numeric
result.
In such cases, writing it as Application.Match gives the expected (and
correct) result).


On 26 Mar 2005 17:12:49 -0800, "Dave Unger"
wrote:

Hi Tom,

Ok, this sheds an entirely new light on the subject (for me). I was
under the impression that, whatever qualifier you used (Application,
WorksheetFunction, Application.WorksheetFunction), it was all the same,
the compiler would parse it all down, and generate the same run-time
code. That's why I couldn't understand the different error handling.
But, if it's done by the object, that's a different matter. I don't
think I've come across this in any of my reading, guess I'll have to
delve a little deeper.

Thanks for now,

Dave

Yes, definitely have had problems even in XL2002.


On Sun, 27 Mar 2005 10:36:40 +0100, "Bob Phillips"
wrote:

Post XL97?

Bob


"Myrna Larson" wrote in message
.. .
My experience has also been that WorksheetFunction.Match may cause a
run-time
error when in fact there IS a match and I should be getting a numeric
result.
In such cases, writing it as Application.Match gives the expected (and
correct) result).


On 26 Mar 2005 17:12:49 -0800, "Dave Unger"
wrote:

Hi Tom,

Ok, this sheds an entirely new light on the subject (for me). I was
under the impression that, whatever qualifier you used (Application,
WorksheetFunction, Application.WorksheetFunction), it was all the same,
the compiler would parse it all down, and generate the same run-time
code. That's why I couldn't understand the different error handling.
But, if it's done by the object, that's a different matter. I don't
think I've come across this in any of my reading, guess I'll have to
delve a little deeper.

Thanks for now,

Dave

Since the problem is intermittent, it would be hard to say - added to the
fact that my personal preference is to use Application.Match and iserror -
so not something I have pursued.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Post XL97?

Bob


"Myrna Larson" wrote in message
...
My experience has also been that WorksheetFunction.Match may cause a
run-time
error when in fact there IS a match and I should be getting a numeric
result.
In such cases, writing it as Application.Match gives the expected (and
correct) result).


On 26 Mar 2005 17:12:49 -0800, "Dave Unger"
wrote:

Hi Tom,

Ok, this sheds an entirely new light on the subject (for me). I was
under the impression that, whatever qualifier you used (Application,
WorksheetFunction, Application.WorksheetFunction), it was all the same,
the compiler would parse it all down, and generate the same run-time
code. That's why I couldn't understand the different error handling.
But, if it's done by the object, that's a different matter. I don't
think I've come across this in any of my reading, guess I'll have to
delve a little deeper.

Thanks for now,

Dave

Hi, Tom:

Are you using On Error Resume Next?

I either do that, or use a Variant variable to receive the result of Match.
The error that I get in the latter situation is something on the order of

Unable to get the Match property of the WorksheetFunction object.

And of course using a variant doesn't help with that. I've never had that
error message with Application.Match.

On Sun, 27 Mar 2005 19:00:31 -0500, "Tom Ogilvy" wrote:

Since the problem is intermittent, it would be hard to say - added to the
fact that my personal preference is to use Application.Match and iserror -
so not something I have pursued.

Hello everyone,

Again, I'd like to thank you all for your help, it has been very
educational and is much appreciated.

Dave

I've never had that
error message with Application.Match.

I didn't talk about any particular error message. I said I have not pursued
whether it was still a problem in later versions of Excel. So I am not
following your question, but in any event, I am not sure there is any need
to ask one.

--
Regards,
Tom Ogilvy


"Myrna Larson" wrote in message
...
Hi, Tom:

Are you using On Error Resume Next?

I either do that, or use a Variant variable to receive the result of
Match.
The error that I get in the latter situation is something on the order of

Unable to get the Match property of the WorksheetFunction object.

And of course using a variant doesn't help with that. I've never had that
error message with Application.Match.

On Sun, 27 Mar 2005 19:00:31 -0500, "Tom Ogilvy" wrote:

Since the problem is intermittent, it would be hard to say - added to the
fact that my personal preference is to use Application.Match and
iserror -
so not something I have pursued.