Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

What worksheet function?

Some aren't available in VBA. But VBA's help shows which ones are.

Yossi wrote:

Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

--

Dave Peterson

for example the MATCH function.
I used it somewhere else (not in a user form) and it works, so I am a bit
puzzled as to why it doesn't work here.

basically I am triggering the function when the user selects something from
the list box I gave him.

"Dave Peterson" wrote:

What worksheet function?

Some aren't available in VBA. But VBA's help shows which ones are.

Yossi wrote:

Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

--

Dave Peterson

dim res as variant 'in case of an error returned
dim lookupRng as range
dim lookupVal as variant

with worksheets("sheet99")
set lookuprng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

set lookupval = worksheets("sheet77").range("a99")

res = application.match(lookupval.value,lookuprng,0)

if iserror(res) then
'not found
else
'it was found
end if

There are function (=vlookup() and =match() come to mind) that cause a run time
error if you use:

application.worksheetfunction.match()

But you can code around it (I just find the previous method easier):

dim res as long
'''same as that other stuff

on error resume next
res = application.match(lookupval.value,lookuprng,0)
if err.number < 0 then
'no match
err.clear
else
'it was found
end if
on error goto 0






Yossi wrote:

for example the MATCH function.
I used it somewhere else (not in a user form) and it works, so I am a bit
puzzled as to why it doesn't work here.

basically I am triggering the function when the user selects something from
the list box I gave him.

"Dave Peterson" wrote:

What worksheet function?

Some aren't available in VBA. But VBA's help shows which ones are.

Yossi wrote:

Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

--

Dave Peterson


--

Dave Peterson

Thanks a lot, man, for the help :-)

"Dave Peterson" wrote:

dim res as variant 'in case of an error returned
dim lookupRng as range
dim lookupVal as variant

with worksheets("sheet99")
set lookuprng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

set lookupval = worksheets("sheet77").range("a99")

res = application.match(lookupval.value,lookuprng,0)

if iserror(res) then
'not found
else
'it was found
end if

There are function (=vlookup() and =match() come to mind) that cause a run time
error if you use:

application.worksheetfunction.match()

But you can code around it (I just find the previous method easier):

dim res as long
'''same as that other stuff

on error resume next
res = application.match(lookupval.value,lookuprng,0)
if err.number < 0 then
'no match
err.clear
else
'it was found
end if
on error goto 0






Yossi wrote:

for example the MATCH function.
I used it somewhere else (not in a user form) and it works, so I am a bit
puzzled as to why it doesn't work here.

basically I am triggering the function when the user selects something from
the list box I gave him.

"Dave Peterson" wrote:

What worksheet function?

Some aren't available in VBA. But VBA's help shows which ones are.

Yossi wrote:

Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

--

Dave Peterson


--

Dave Peterson

You're welcome.

Yossi wrote:

Thanks a lot, man, for the help :-)

"Dave Peterson" wrote:

dim res as variant 'in case of an error returned
dim lookupRng as range
dim lookupVal as variant

with worksheets("sheet99")
set lookuprng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

set lookupval = worksheets("sheet77").range("a99")

res = application.match(lookupval.value,lookuprng,0)

if iserror(res) then
'not found
else
'it was found
end if

There are function (=vlookup() and =match() come to mind) that cause a run time
error if you use:

application.worksheetfunction.match()

But you can code around it (I just find the previous method easier):

dim res as long
'''same as that other stuff

on error resume next
res = application.match(lookupval.value,lookuprng,0)
if err.number < 0 then
'no match
err.clear
else
'it was found
end if
on error goto 0






Yossi wrote:

for example the MATCH function.
I used it somewhere else (not in a user form) and it works, so I am a bit
puzzled as to why it doesn't work here.

basically I am triggering the function when the user selects something from
the list box I gave him.

"Dave Peterson" wrote:

What worksheet function?

Some aren't available in VBA. But VBA's help shows which ones are.

Yossi wrote:

Hi,
I am trying to use the WorsheeFunction property within a user form, but the
VBA claims it can't get the method I want to use out of the worksheetFunction
class.
Why is that?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson