Can't use WorksheetFunction
 

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?

Can't use WorksheetFunction
 

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

Can't use WorksheetFunction
 

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

Can't use WorksheetFunction
 

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

Can't use WorksheetFunction
 

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

Can't use WorksheetFunction
 

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