Re: VLOOKUP and VBA

Perfect explanation. Thanks for your time Dave.

Best regards

John


"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:44D38A65.19E2E16@xxxxxxxxxxxxxxxxxxx
.worksheetfunction syntax was added in xl97 (IIRC).

But Excel/VBA still supports lots of stuff from xl95 and before.

But there is a difference in the way they behave.

application.vlookup() will return an error that you can check:

dim myVal as Variant 'could be an error
myval = application.vlookup(....)
if iserror(myval) then
'same as #n/a
else
'found it
end if

On the other hand, application.worksheetfunction.vlookup() will cause a
trappable error.

Dim myVal as Variant 'or string or long or double ...

on error resume next
myval = application.worksheetfunction.vlookup(...)
if error.number <> 0 then
'same as #n/a
else
'found it
end if
on error goto 0

========
I find the application.vlookup() easier to use.



John wrote:

Hi Dave,

I'm sure you're right, but what's the reason for .worksheetfunction not
being needed? Is it a default somehow?

Best regards

John

"Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:44D36AE1.94A6C504@xxxxxxxxxxxxxxxxxxx
You don't need the .worksheetfunction in your code.

Can you get a formula similar to this working in excel?

=vlookup("something",Statics_Query_from_MS_Access_Database, 2,false)

(I like Bernie's suggestion.)

Barb Reinhardt wrote:

I'm not sure what I need. That's why I'm asking. Defining what I'd
call a
constant based on data in the workbook is new to me.

"John" wrote:

Hi Barb,

Do you need the WorksheetFunction object in there?

ie "Application.WorksheetFunction.VLookup(......"

Best regards

John

"Barb Reinhardt" <BarbReinhardt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@xxxxxxxxxxxxxxxx
I thought I posted this, but I can't find it now so it's possible
it
never
was posted. If it's a duplicate, my apologies.

I have the following equation that's not working and I'm not sure
why

CompanyID = Application.VLookup(Target, Range("Statics
Query_from_MS_Access_Database"), 2, False)

I get
runtime error 1004.
Method 'range' of object '_work***' failed

I have a named range of Query ... on the Statics work***.

Thanks,
Barb Reinhardt










--

Dave Peterson

--

Dave Peterson


.