+ Reply to Thread
Results 1 to 12 of 12

VLOOKUP and VBA

  1. #1
    Barb Reinhardt
    Guest

    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







  2. #2
    Bernie Deitrick
    Guest

    Re: 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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    >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
    >
    >
    >
    >
    >
    >




  3. #3
    John
    Guest

    Re: VLOOKUP and VBA

    Hi Barb,

    Do you need the WorksheetFunction object in there?

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

    Best regards

    John

    "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
    news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    >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
    >
    >
    >
    >
    >
    >




  4. #4
    Barb Reinhardt
    Guest

    Re: 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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > >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
    > >
    > >
    > >
    > >
    > >
    > >

    >
    >
    >


  5. #5
    Dave Peterson
    Guest

    Re: 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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    > > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > > >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

  6. #6
    Barb Reinhardt
    Guest

    Re: VLOOKUP and VBA

    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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > >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
    > >
    > >
    > >
    > >
    > >
    > >

    >
    >
    >


  7. #7
    John
    Guest

    Re: VLOOKUP and VBA

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

    Have a good weekend

    John

    "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
    news:F35B67DB-43DE-4CEA-8BA7-EFAE4773081F@microsoft.com...
    >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" <BarbReinhardt@discussions.microsoft.com> wrote in
    >> message
    >> news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    >> >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
    >> >
    >> >
    >> >
    >> >
    >> >
    >> >

    >>
    >>
    >>




  8. #8
    John
    Guest

    Re: 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" <petersod@verizonXSPAM.net> wrote in message
    news:44D36AE1.94A6C504@verizonXSPAM.net...
    > 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@discussions.microsoft.com> wrote in
    >> > message
    >> > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    >> > >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




  9. #9
    Barb Reinhardt
    Guest

    Re: 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_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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    > news:F35B67DB-43DE-4CEA-8BA7-EFAE4773081F@microsoft.com...
    > >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" <BarbReinhardt@discussions.microsoft.com> wrote in
    > >> message
    > >> news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > >> >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
    > >> >
    > >> >
    > >> >
    > >> >
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


  10. #10
    Dave Peterson
    Guest

    Re: 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" <petersod@verizonXSPAM.net> wrote in message
    > news:44D36AE1.94A6C504@verizonXSPAM.net...
    > > 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@discussions.microsoft.com> wrote in
    > >> > message
    > >> > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > >> > >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

  11. #11
    Dave Peterson
    Guest

    Re: 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_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" <BarbReinhardt@discussions.microsoft.com> wrote in message
    > > news:F35B67DB-43DE-4CEA-8BA7-EFAE4773081F@microsoft.com...
    > > >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" <BarbReinhardt@discussions.microsoft.com> wrote in
    > > >> message
    > > >> news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    > > >> >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

  12. #12
    John
    Guest

    Re: VLOOKUP and VBA

    Perfect explanation. Thanks for your time Dave.

    Best regards

    John


    "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
    news:44D38A65.19E2E16@verizonXSPAM.net...
    > .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@verizonXSPAM.net> wrote in message
    >> news:44D36AE1.94A6C504@verizonXSPAM.net...
    >> > 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@discussions.microsoft.com> wrote in
    >> >> > message
    >> >> > news:DCCE6000-C933-499E-BD5E-3FA8101C35BA@microsoft.com...
    >> >> > >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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1