What is wrong with this lin e of code
 

I am getting "Unable to get the VLookup property of the WorksheetFunction
class" error on this line of code.
Any ideas?

If
Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cRegion, rngFSC, 1, False)) = True Then

I also tried this, with the same returns:

If IsError(Application.WorksheetFunction.VLookup(cReg ion, rngFSC, 1, False))
Then

What is wrong with this lin e of code
 

This may help from yesterday's archive:

So I finally got VBA working with VLookup, Worksheet names, and a
lookup table referred to by list name. I had to do the proper
prefixes:

temp = WorksheetFunction.VLookup( ...)
temp = Application.WorksheetFunction.VLookup( ...)

Which of the two are best practice, and why?

Thanks!

Newsgroups: microsoft.public.excel.programming
From: Dave Peterson
Date: Mon, 11 May 2009 17:32:47 -0500
Local: Mon, May 11 2009 3:32 pm
Subject: Best practice in qualifying Excel worksheet function
invocation

There are some worksheet functions where it doesn't really matter
(maybe speed
is slightly affected???) if you use:

Application.somefunction()
or
application.worksheetfunction.somefunction()
or
worksheetfunction.somefunction()

But one of the nice things is that if you use worksheetfunction (with
or without
the application object), you'll get a hint of what comes next with the
VBE's
intellisense and autocomplete.

If you type:
application.worksheetfunction.
(with the dot)
you'll see all the worksheet functions that you can use (autocomplete)

If you type:
application.worksheetfunction.vlookup(
you'll see (not too informative) 4 arguments (arg1, arg2, arg3, arg4)

You won't see that if you use application.vlookup.

But (you didn't ask about this), there is a difference with a few
functions on
how errors are treated between:

Application.vlookup() (and application.match())
and
application.vlookup() (and application.worksheetfunction.match())

If you don't use the .worksheetfunction portion, then you can test the
returned
results for an error:

Dim res as variant 'could be an error
res = application.vlookup("someval", somerangehere, 2, false)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

===========
But when you use worksheetfunction.vlookup(), then you'll have to
program
against a run time error.

dim res as long 'or string or even variant
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if err.number < 0 then
err.clear
msgbox "no match"
else
msgbox res
end if
on error goto 0

============
Personally, I like using the
if iserror(res) then
version
And I hardly ever (almost never??) use worksheetfunction.

If you continue to hang out in the excel newsgroups, I bet you'll find
that the
people who use application.vlookup() are the ones who have been using
excel
longer (application.worksheetfunction.vlookup() was added in xl97
(IIRC))--or
they've picked up this nasty <vbg habit from the longer time users.

What is wrong with this lin e of code
 

There is a difference with a few functions on how errors are treated between:

Application.vlookup() (and application.match())
and
application.worksheetfunction.vlookup()
(and application.worksheetfunction.match())

If you don't use the .worksheetfunction portion, then you can test the returned
results for an error:

Dim res as variant 'could be an error
res = application.vlookup("someval", somerangehere, 2, false)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

===========
But when you use worksheetfunction.vlookup(), then you'll have to program
against a run time error.

dim res as long 'or string or even variant
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if err.number < 0 then
err.clear
msgbox "no match"
else
msgbox res
end if
on error goto 0

=========
And since it looks like you're trying to find if there's a match (since you
bring back what's in column 1 of the table). I'd use:

Dim res as variant
res = application.match(cRegion, rngfsc.columns(1),0)
if iserror(res) then
'not there
else
'it's on row # Res of the first column of rngfsc.
end if


Ayo wrote:

I am getting "Unable to get the VLookup property of the WorksheetFunction
class" error on this line of code.
Any ideas?

If
Application.WorksheetFunction.IsNA(Application.Wor ksheetFunction.VLookup(cRegion, rngFSC, 1, False)) = True Then

I also tried this, with the same returns:

If IsError(Application.WorksheetFunction.VLookup(cReg ion, rngFSC, 1, False))
Then

--

Dave Peterson

What is wrong with this lin e of code
 

But there was a typo (at least one!).

I wanted the second example to include the .worksheetfunction

Application.vlookup() (and application.match())
and
application.vlookup() (and application.worksheetfunction.match())

should be:

Application.vlookup() (and application.match())
and
application.worksheetfunction.vlookup()
(and application.worksheetfunction.match())

dan dungan wrote:

This may help from yesterday's archive:

So I finally got VBA working with VLookup, Worksheet names, and a
lookup table referred to by list name. I had to do the proper
prefixes:

temp = WorksheetFunction.VLookup( ...)
temp = Application.WorksheetFunction.VLookup( ...)

Which of the two are best practice, and why?

Thanks!

Newsgroups: microsoft.public.excel.programming
From: Dave Peterson
Date: Mon, 11 May 2009 17:32:47 -0500
Local: Mon, May 11 2009 3:32 pm
Subject: Best practice in qualifying Excel worksheet function
invocation

There are some worksheet functions where it doesn't really matter
(maybe speed
is slightly affected???) if you use:

Application.somefunction()
or
application.worksheetfunction.somefunction()
or
worksheetfunction.somefunction()

But one of the nice things is that if you use worksheetfunction (with
or without
the application object), you'll get a hint of what comes next with the
VBE's
intellisense and autocomplete.

If you type:
application.worksheetfunction.
(with the dot)
you'll see all the worksheet functions that you can use (autocomplete)

If you type:
application.worksheetfunction.vlookup(
you'll see (not too informative) 4 arguments (arg1, arg2, arg3, arg4)

You won't see that if you use application.vlookup.

But (you didn't ask about this), there is a difference with a few
functions on
how errors are treated between:

Application.vlookup() (and application.match())
and
application.vlookup() (and application.worksheetfunction.match())

If you don't use the .worksheetfunction portion, then you can test the
returned
results for an error:

Dim res as variant 'could be an error
res = application.vlookup("someval", somerangehere, 2, false)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

===========
But when you use worksheetfunction.vlookup(), then you'll have to
program
against a run time error.

dim res as long 'or string or even variant
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if err.number < 0 then
err.clear
msgbox "no match"
else
msgbox res
end if
on error goto 0

============
Personally, I like using the
if iserror(res) then
version
And I hardly ever (almost never??) use worksheetfunction.

If you continue to hang out in the excel newsgroups, I bet you'll find
that the
people who use application.vlookup() are the ones who have been using
excel
longer (application.worksheetfunction.vlookup() was added in xl97
(IIRC))--or
they've picked up this nasty <vbg habit from the longer time users.

--

Dave Peterson