VLOOKUP and VBA

[Barb Reinhardt]

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 '_worksheet' failed

I have a named range of Query ... on the Statics worksheet.

Thanks,
Barb Reinhardt

 

[Bernie Deitrick]

Barb,

It is likely that your range name as entered in code does not match your actual named range's name:
because of where it broke in the message, I suspect that you have a space in the name in your code,
which is not allowed. Otherwise, the code should work fine.

HTH,
Bernie
MS Excel MVP

 

[John]

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

 

[Barb Reinhardt]

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.

 

[Dave Peterson]

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]

I figured out what I needed.

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

 

[John]

Glad you're sorted. Apologies for the red herring!

Have a good weekend

John

 

[John]

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

 

[Barb Reinhardt]

I needed to make a change so as to use a different target. This is what I have:

CompanyID = Application.WorksheetFunction.VLookup("R" & Target.Row & "C" &
(Target.Column + 1),
Sheets("Statics").Range("Query_from_MS_Access_Database"), 2, False)

basically, I want to do a lookup of the data that is one column to the right
of the cell that is changed.

I think my problem is that my first lookup argument is the location of the
value to lookup, not what's being checked (R3C5, not "Company1"). I'd put
in indirect, but this won't work. Suggestions?

Barb Reinhardt

Thanks,
Barb

 

[Dave Peterson]

..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.

 

[Dave Peterson]

I'd use:

Dim CompanyID as Variant 'could be error

companyid = application.vlookup(target.offset(0,+1).value, _
Sheets("Statics").Range("Query_from_MS_Access_Database"), 2, _
False)

if iserror(companyid) then
'not found
else
'go it
end if


I dropped the .worksheetfunction portion.

 

[John]

Perfect explanation. Thanks for your time Dave.

Best regards

John