Problem w/ Match prop vs. Match method
 

Wishing all a happy new year ...

I get a runtime error with this piece of code:

junk = Application.WorksheetFunction.VLookup("Min", table,
Application.WorksheetFunction.Match("Gorge", top, 0))

junk is variant, table and top are ranges.

The error is:

"Unable to get the Match Property of the WS function"

If I replace the ref to App.WSF.Match with an integer, it works fine.

Thoughts?

Thanks, Tony

Problem w/ Match prop vs. Match method
 

Try dropping the .worksheetfunction from your code:

with application
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
end with

if iserror(Junk) then
msgbox "Hey, there wasn't a match"
else
msgbox junk
end if

=========
If you want to keep the .worksheetfunction, you'll have to see if there was a
runtime error.

with application.worksheetfunction
on error resume next
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
if err.number < 0 then
msgbox "Hey, there wasn't a match"
err.clear
else
msgbox junk
end if
on error go to 0
end with

===
By using the with/end with stuff, I could save some typing.

George Raft wrote:

Wishing all a happy new year ...

I get a runtime error with this piece of code:

junk = Application.WorksheetFunction.VLookup("Min", table,
Application.WorksheetFunction.Match("Gorge", top, 0))

junk is variant, table and top are ranges.

The error is:

"Unable to get the Match Property of the WS function"

If I replace the ref to App.WSF.Match with an integer, it works fine.

Thoughts?

Thanks, Tony

--

Dave Peterson

Problem w/ Match prop vs. Match method
 

Thanks Dave,

It works and it's more elegant - I'm learning slowly, but I'm learning. But
why does it work? Or more precisely, why didn't my clumsier version work?
Is there something fundamental I'm missing?

Tony

Dave Peterson wrote in message
...
Try dropping the .worksheetfunction from your code:

with application
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
end with

if iserror(Junk) then
msgbox "Hey, there wasn't a match"
else
msgbox junk
end if

=========
If you want to keep the .worksheetfunction, you'll have to see if there
was a
runtime error.

with application.worksheetfunction
on error resume next
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
if err.number < 0 then
msgbox "Hey, there wasn't a match"
err.clear
else
msgbox junk
end if
on error go to 0
end with

===
By using the with/end with stuff, I could save some typing.

George Raft wrote:

Wishing all a happy new year ...

I get a runtime error with this piece of code:

junk = Application.WorksheetFunction.VLookup("Min", table,
Application.WorksheetFunction.Match("Gorge", top, 0))

junk is variant, table and top are ranges.

The error is:

"Unable to get the Match Property of the WS function"

If I replace the ref to App.WSF.Match with an integer, it works fine.

Thoughts?

Thanks, Tony

--

Dave Peterson

Problem w/ Match prop vs. Match method
 

Application.worksheetfunction.match() causes a runtime error. About the only
thing you can do is to turn off the error checking and then check for yourself.

application.match() returns an error that you can check with iserror().

It's just one of the vagaries of excel/vba.

I find the application.match() (and application.vlookup()), prettier???.

But that's just a personal choice.



George Raft wrote:

Thanks Dave,

It works and it's more elegant - I'm learning slowly, but I'm learning. But
why does it work? Or more precisely, why didn't my clumsier version work?
Is there something fundamental I'm missing?

Tony

Dave Peterson wrote in message
...
Try dropping the .worksheetfunction from your code:

with application
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
end with

if iserror(Junk) then
msgbox "Hey, there wasn't a match"
else
msgbox junk
end if

=========
If you want to keep the .worksheetfunction, you'll have to see if there
was a
runtime error.

with application.worksheetfunction
on error resume next
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
if err.number < 0 then
msgbox "Hey, there wasn't a match"
err.clear
else
msgbox junk
end if
on error go to 0
end with

===
By using the with/end with stuff, I could save some typing.

George Raft wrote:

Wishing all a happy new year ...

I get a runtime error with this piece of code:

junk = Application.WorksheetFunction.VLookup("Min", table,
Application.WorksheetFunction.Match("Gorge", top, 0))

junk is variant, table and top are ranges.

The error is:

"Unable to get the Match Property of the WS function"

If I replace the ref to App.WSF.Match with an integer, it works fine.

Thoughts?

Thanks, Tony

--

Dave Peterson

--

Dave Peterson

Problem w/ Match prop vs. Match method
 

Thanks again Dave, that helps.

Tony

Dave Peterson wrote in message
...
Application.worksheetfunction.match() causes a runtime error. About the
only
thing you can do is to turn off the error checking and then check for
yourself.

application.match() returns an error that you can check with iserror().

It's just one of the vagaries of excel/vba.

I find the application.match() (and application.vlookup()), prettier???.

But that's just a personal choice.



George Raft wrote:

Thanks Dave,

It works and it's more elegant - I'm learning slowly, but I'm learning.
But
why does it work? Or more precisely, why didn't my clumsier version
work?
Is there something fundamental I'm missing?

Tony

Dave Peterson wrote in message
...
Try dropping the .worksheetfunction from your code:

with application
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
end with

if iserror(Junk) then
msgbox "Hey, there wasn't a match"
else
msgbox junk
end if

=========
If you want to keep the .worksheetfunction, you'll have to see if
there
was a
runtime error.

with application.worksheetfunction
on error resume next
junk = .VLookup("Min", table, .Match("Gorge", top, 0))
if err.number < 0 then
msgbox "Hey, there wasn't a match"
err.clear
else
msgbox junk
end if
on error go to 0
end with

===
By using the with/end with stuff, I could save some typing.

George Raft wrote:

Wishing all a happy new year ...

I get a runtime error with this piece of code:

junk = Application.WorksheetFunction.VLookup("Min", table,
Application.WorksheetFunction.Match("Gorge", top, 0))

junk is variant, table and top are ranges.

The error is:

"Unable to get the Match Property of the WS function"

If I replace the ref to App.WSF.Match with an integer, it works
fine.

Thoughts?

Thanks, Tony

--

Dave Peterson

--

Dave Peterson