Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Applying VLookup Code In Sheet (97:SR2)

    I can successfully run the code from a module but when I try to place the code in a sheet object I receive an error message related to the range (Range("R_Main") which is on another worksheet within the book.

    Any ideas as to why the error?

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Ah Got it!

    I need to refer to the range as: Sheets("UNameIT").Range("R_Main"). With this minor change it works.

    It is the end of the day ie please excuse this post.

    John

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Ah, you already found it yourself. Here is another solution:

    Placing the code in a worksheet module narrows your vision. Without explicit specification, Range is assumed to be a range on the worksheet the module belongs to. To refer to a name on another worksheet, use the Names collection. Try

    oCell.Value = Application.WorksheetFunction.VLookup(LookItUp, ActiveWorkbook.Names("R_Main").RefersToRange, 5, 0)

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Hans,

    I like your approach. See I learned two things today.

    John

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Using the Names collection is OK, as long as there are no local and global names with the same name.

    E.g. Define these two names:

    Name:Test1
    Refersto: =Sheet1!$A$1

    Name: Sheet1!Test1
    Refersto: =Sheet1!$A$2

    If you use this code:

    MsgBox ThisWorkbook.Names("Test").Refersto
    MsgBox ThisWorkbook.Names(ThisWorkbook.Name & "!Test").Refersto

    You *should* get:
    =Sheet1!$A$2 (Local name)
    =Sheet1!$A$1 (Global name)

    But the second one fails and produces:

    =Sheet1!$A$2 (Local name)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Jan Karel,

    Good point.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    One more question...

    In the case where the VLookup does not find or return a value I would like to put "Missing" in the appropriate cell. I have tried using "On Error GoTo ErrorHandler" and just can't get to the NEXT oCell. I can not seem to get around it.

    ErrorHandler:
    oCell = "N/A?
    Resume

    The resume returns me to "oCell" which is in error.

    Thanks for your help,
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Try

    ErrorHandler:
    oCell = "#N/A"
    Resume Next

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Add this line right after you get the ocell.value

    <pre>ocell.value = etc etc
    if iserror(ocell.value) then ocell.value = "Missing"</pre>


    Steve

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Applying VLookup Code In Sheet (97:SR2)

    Thank you it worked.

    I was soooo close.

    John

Posting Permissions

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