software

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:
gt;
gt; Hi,
gt; I am trying to use the WorsheeFunction property within a user form, but the
gt; VBA claims it can't get the method I want to use out of the worksheetFunction
gt; class.
gt; 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.

quot;Dave Petersonquot; wrote:

gt; What worksheet function?
gt;
gt; Some aren't available in VBA. But VBA's help shows which ones are.
gt;
gt; Yossi wrote:
gt; gt;
gt; gt; Hi,
gt; gt; I am trying to use the WorsheeFunction property within a user form, but the
gt; gt; VBA claims it can't get the method I want to use out of the worksheetFunction
gt; gt; class.
gt; gt; Why is that?
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

with worksheets(quot;sheet99quot;)
set lookuprng = .range(quot;a2quot;,.cells(.rows.count,quot;Aquot;).end(xlup))
end with

set lookupval = worksheets(quot;sheet77quot;).range(quot;a99quot;)

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 lt;gt; 0 then
'no match
err.clear
else
'it was found
end if
on error goto 0
Yossi wrote:
gt;
gt; for example the MATCH function.
gt; I used it somewhere else (not in a user form) and it works, so I am a bit
gt; puzzled as to why it doesn't work here.
gt;
gt; basically I am triggering the function when the user selects something from
gt; the list box I gave him.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; What worksheet function?
gt; gt;
gt; gt; Some aren't available in VBA. But VBA's help shows which ones are.
gt; gt;
gt; gt; Yossi wrote:
gt; gt; gt;
gt; gt; gt; Hi,
gt; gt; gt; I am trying to use the WorsheeFunction property within a user form, but the
gt; gt; gt; VBA claims it can't get the method I want to use out of the worksheetFunction
gt; gt; gt; class.
gt; gt; gt; Why is that?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; dim res as variant 'in case of an error returned
gt; dim lookupRng as range
gt; dim lookupVal as variant
gt;
gt; with worksheets(quot;sheet99quot;)
gt; set lookuprng = .range(quot;a2quot;,.cells(.rows.count,quot;Aquot;).end(xlup))
gt; end with
gt;
gt; set lookupval = worksheets(quot;sheet77quot;).range(quot;a99quot;)
gt;
gt; res = application.match(lookupval.value,lookuprng,0)
gt;
gt; if iserror(res) then
gt; 'not found
gt; else
gt; 'it was found
gt; end if
gt;
gt; There are function (=vlookup() and =match() come to mind) that cause a run time
gt; error if you use:
gt;
gt; application.worksheetfunction.match()
gt;
gt; But you can code around it (I just find the previous method easier):
gt;
gt; dim res as long
gt; '''same as that other stuff
gt;
gt; on error resume next
gt; res = application.match(lookupval.value,lookuprng,0)
gt; if err.number lt;gt; 0 then
gt; 'no match
gt; err.clear
gt; else
gt; 'it was found
gt; end if
gt; on error goto 0
gt;
gt;
gt;
gt;
gt;
gt;
gt; Yossi wrote:
gt; gt;
gt; gt; for example the MATCH function.
gt; gt; I used it somewhere else (not in a user form) and it works, so I am a bit
gt; gt; puzzled as to why it doesn't work here.
gt; gt;
gt; gt; basically I am triggering the function when the user selects something from
gt; gt; the list box I gave him.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; What worksheet function?
gt; gt; gt;
gt; gt; gt; Some aren't available in VBA. But VBA's help shows which ones are.
gt; gt; gt;
gt; gt; gt; Yossi wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hi,
gt; gt; gt; gt; I am trying to use the WorsheeFunction property within a user form, but the
gt; gt; gt; gt; VBA claims it can't get the method I want to use out of the worksheetFunction
gt; gt; gt; gt; class.
gt; gt; gt; gt; Why is that?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

You're welcome.

Yossi wrote:
gt;
gt; Thanks a lot, man, for the help :-)
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; dim res as variant 'in case of an error returned
gt; gt; dim lookupRng as range
gt; gt; dim lookupVal as variant
gt; gt;
gt; gt; with worksheets(quot;sheet99quot;)
gt; gt; set lookuprng = .range(quot;a2quot;,.cells(.rows.count,quot;Aquot;).end(xlup))
gt; gt; end with
gt; gt;
gt; gt; set lookupval = worksheets(quot;sheet77quot;).range(quot;a99quot;)
gt; gt;
gt; gt; res = application.match(lookupval.value,lookuprng,0)
gt; gt;
gt; gt; if iserror(res) then
gt; gt; 'not found
gt; gt; else
gt; gt; 'it was found
gt; gt; end if
gt; gt;
gt; gt; There are function (=vlookup() and =match() come to mind) that cause a run time
gt; gt; error if you use:
gt; gt;
gt; gt; application.worksheetfunction.match()
gt; gt;
gt; gt; But you can code around it (I just find the previous method easier):
gt; gt;
gt; gt; dim res as long
gt; gt; '''same as that other stuff
gt; gt;
gt; gt; on error resume next
gt; gt; res = application.match(lookupval.value,lookuprng,0)
gt; gt; if err.number lt;gt; 0 then
gt; gt; 'no match
gt; gt; err.clear
gt; gt; else
gt; gt; 'it was found
gt; gt; end if
gt; gt; on error goto 0
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Yossi wrote:
gt; gt; gt;
gt; gt; gt; for example the MATCH function.
gt; gt; gt; I used it somewhere else (not in a user form) and it works, so I am a bit
gt; gt; gt; puzzled as to why it doesn't work here.
gt; gt; gt;
gt; gt; gt; basically I am triggering the function when the user selects something from
gt; gt; gt; the list box I gave him.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; What worksheet function?
gt; gt; gt; gt;
gt; gt; gt; gt; Some aren't available in VBA. But VBA's help shows which ones are.
gt; gt; gt; gt;
gt; gt; gt; gt; Yossi wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi,
gt; gt; gt; gt; gt; I am trying to use the WorsheeFunction property within a user form, but the
gt; gt; gt; gt; gt; VBA claims it can't get the method I want to use out of the worksheetFunction
gt; gt; gt; gt; gt; class.
gt; gt; gt; gt; gt; Why is that?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson