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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
>
>
>

Hi Barb,

Do you need the WorksheetFunction object in there?

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

Best regards

John

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
> >
> >
> >
> >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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

I figured out what I needed.

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


"John" wrote:

> Hi Barb,
>
> Do you need the WorksheetFunction object in there?
>
> ie "Application.WorksheetFunction.VLookup(......"
>
> Best regards
>
> John
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
> >
> >
> >
> >
>
>
>

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

Have a good weekend

John

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
>I figured out what I needed.
>
> CompanyID = Application.WorksheetFunction.VLookup(Target,
> Sheets("Statics").Range("Query_from_MS_Access_Database"), 2, False)
>
>
> "John" wrote:
>
>> Hi Barb,
>>
>> Do you need the WorksheetFunction object in there?
>>
>> ie "Application.WorksheetFunction.VLookup(......"
>>
>> Best regards
>>
>> John
>>
>> "Barb Reinhardt" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >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
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> > >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

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
"John" wrote:

> Glad you're sorted. Apologies for the red herring!
>
> Have a good weekend
>
> John
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> >I figured out what I needed.
> >
> > CompanyID = Application.WorksheetFunction.VLookup(Target,
> > Sheets("Statics").Range("Query_from_MS_Access_Database"), 2, False)
> >
> >
> > "John" wrote:
> >
> >> Hi Barb,
> >>
> >> Do you need the WorksheetFunction object in there?
> >>
> >> ie "Application.WorksheetFunction.VLookup(......"
> >>
> >> Best regards
> >>
> >> John
> >>
> >> "Barb Reinhardt" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >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
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

..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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> >> > message
> >> > news:[email protected]...
> >> > >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

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.



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_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
> "John" wrote:
>
> > Glad you're sorted. Apologies for the red herring!
> >
> > Have a good weekend
> >
> > John
> >
> > "Barb Reinhardt" <[email protected]> wrote in message
> > news:[email protected]...
> > >I figured out what I needed.
> > >
> > > CompanyID = Application.WorksheetFunction.VLookup(Target,
> > > Sheets("Statics").Range("Query_from_MS_Access_Database"), 2, False)
> > >
> > >
> > > "John" wrote:
> > >
> > >> Hi Barb,
> > >>
> > >> Do you need the WorksheetFunction object in there?
> > >>
> > >> ie "Application.WorksheetFunction.VLookup(......"
> > >>
> > >> Best regards
> > >>
> > >> John
> > >>
> > >> "Barb Reinhardt" <[email protected]> wrote in
> > >> message
> > >> news:[email protected]...
> > >> >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

Perfect explanation. Thanks for your time Dave.

Best regards

John


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> .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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in
>> >> > message
>> >> > news:[email protected]...
>> >> > >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