Results 1 to 10 of 10

Thread: vlookup (all)

  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup (all)

    hello,
    i have a problem with large lists of id
    Attached Files Attached Files

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

    Re: vlookup (all)

    Welcome to Woody's Lounge!

    Try this version. The code uses an On Error handler to take care of IDs without a description.

    Sub sverweis()
    Dim ws1 As Worksheet
    Dim r As Long
    Dim strVal As String
    On Error GoTo ErrHandler
    Set ws1 = Sheets("Tabelle1")
    For r = 2 To ws1.Range("A1").End(xlDown).Row
    strVal = Application.WorksheetFunction.VLookup( _
    ws1.Range("A" & r), ws1.Range("E2:F70"), 2, False)
    ws1.Range("B" & r) = strVal
    Next r
    Exit Sub

    ErrHandler:
    If Err = 1004 Then ' VLookup failed
    strVal = ""
    Resume Next
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  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: vlookup (all)

    Hans gave you some VB.

    If you want to keep the formulas so that they stay "live" an alternative to using Vlookup in multiple cells is to use a defined named formula instead:
    Select B2:
    Insert -name-define
    NamesInWorkbook:
    MyLookup
    Refers to:
    =IF(ISERROR(VLOOKUP(A2,$E$5:$F$38,2,0)),"Not found",VLOOKUP(A2,$E$5:$F$38,2,0))
    [ok}

    Then add the formula in B2:
    =MyLookup

    Then copy B2 to B3:B23

    The named formula should reduce the filesize since it is only 1 large formula

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (all)

    hi Hans,
    this is fine, it is exact, what i was searching for.
    many thanks,
    stefan

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

    Re: vlookup (all)

    Yet another possibility is to use the Worksheet_Change event of the worksheet to fill in the description in column B if the code in column A is changed. See the attached version. The code is in the Tabelle1 worksheet module.
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (all)

    does it work as well, if the sourcedata is in another worksheet?

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (all)

    thanks, i don't want to use formulas for the large amount of cells. I am not good in vba, so
    i wanted an other solution, but many thanks to your quick reply as well.

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

    Re: vlookup (all)

    Yes, you only need to change the reference in the code, for example:

    strVal = Application.WorksheetFunction.VLookup( _
    oCell, Worksheets("Tabelle2").Range("A2:B70"), 2, False)

    The rest remains unchanged.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (all)

    but i have to declare oCell - as variable?

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

    Re: vlookup (all)

    If you want to use the code in the Worksheet_Change event of the Tabelle1 worksheet. you need to declare oCell as in the sample workbook I attached:

    Dim oCell As Range

    It doesn't matter whether the lookup range is on the Tabelle1 worksheet or on another sheet.

    If you want to use the sverweis macro from my first reply, you don't need oCell at all - it isn't used in that macro.

Posting Permissions

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