Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA using Names in Vlookup Range Argument (Excel 2000 or 2003)

    Hi all,

    I have a Named Range (GroupPageInfo) on worksheet (ForCode). I simply need to use Vlookup, but I do not know how to use a Named Range in VBA.

    For G = 1 To NumOfGroups
    Variable = Application.WorksheetFunction. VLookup(G, Worksheets("ForCode").Range("GroupPageInfo"), 9, False)

    As always, any help is appreciated !
    --cat

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

    Re: VBA using Names in Vlookup Range Argument (Excel 2000 or 2003)

    Your named range is probably global to the workbook, so you should be able to use

    Variable = Application.WorksheetFunction.VLookup(G, Range("GroupPageInfo"), 9, False)

  3. #3
    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: VBA using Names in Vlookup Range Argument (Excel 2000 or 2003)

    One comment: if there is a possibility of the lookup not finding a match, you should trap the runtime error that will be produced. The excel worksheet function essentially does this and reports the error in the cell. If used in VB it will result in a runtime error. Something like this should work:

    <pre>Dim lRow As Long
    For G = 1 To NumOfGroups
    lRow = 0
    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(G, Range("GroupPageInfo").Columns(1), 0)
    On Error GoTo 0
    If lRow = 0 Then
    'Code here for not finding a match
    Else
    Variable = Range("GroupPageInfo").cells(lrow, 9)
    End If
    Next</pre>


    The code sets the row value to 0. and then tells vb to ignore errors. If a match is not found then the row remains 0, otherwise it will get a value which can be used to get the item of interest. After checking the match the error handling is again initiated.

    Steve

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA using Names in Vlookup Range Argument (Excel 2000 or 2003)

    Thank you both, Hans and Steve, this is what I needed!
    --cat

Posting Permissions

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