VLOOKUP and VBA
 

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

VLOOKUP and VBA
 

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


"Barb Reinhardt" wrote in message
...
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

VLOOKUP and VBA
 

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" wrote in message
...
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

VLOOKUP and VBA
 

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" wrote in message
...
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

VLOOKUP and VBA
 

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" wrote in message
...
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










--

Dave Peterson

VLOOKUP and VBA
 

I figured out what I needed.

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


"John" wrote:

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" wrote in message
...
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

VLOOKUP and VBA
 

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

Have a good weekend

John

"Barb Reinhardt" wrote in message
...
I figured out what I needed.

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


"John" wrote:

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" wrote in
message
...
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

VLOOKUP and VBA
 

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" wrote in message
...
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" wrote in
message
...
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










--

Dave Peterson

VLOOKUP and VBA
 

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_Data base"), 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
"John" wrote:

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

Have a good weekend

John

"Barb Reinhardt" wrote in message
...
I figured out what I needed.

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


"John" wrote:

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" wrote in
message
...
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

VLOOKUP and VBA
 

..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" wrote in message
...
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" wrote in
message
...
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










--

Dave Peterson

--

Dave Peterson

VLOOKUP and VBA
 

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_Data base"), 2, _
False)

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


I dropped the .worksheetfunction portion.



Barb Reinhardt wrote:

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_Data base"), 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
"John" wrote:

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

Have a good weekend

John

"Barb Reinhardt" wrote in message
...
I figured out what I needed.

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


"John" wrote:

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" wrote in
message
...
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













--

Dave Peterson

VLOOKUP and VBA
 

Perfect explanation. Thanks for your time Dave.

Best regards

John


"Dave Peterson" wrote in message
...
.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" wrote in message
...
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" wrote in
message
...
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










--

Dave Peterson

--

Dave Peterson