VBA vLookup

  • Hi,
    having a sligh problem with a vlookup in VBA.


    The vlookup is below


    Set rng = shLookup.Range("a2:b5")
    SheetPassword = WorksheetFunction.VLookup(ActiveSheet.Range("ab3"), rng, 2, 0)


    shLookup in in the current workbook and seems ok


    So the vlookup is looking up a value from a seperate workbook and finding this value in the same workbook as the vba code/


    When i run the code an error message occurs saying "Unable to get the vlookup property of the Worksheet Function class"
    Any ideas.....



    also my pc seems to be missing the mainxl.hlp file any ideas where i can find this, i am going to search tho while i await a reply


    cheers
    Mike

  • Re: VBA vLookup


    Hi


    well this code seems to work for me


    Code
    Set rng = Sheets("sheet1").Range("a2:b5")
    sheetpassword = WorksheetFunction.VLookup(ActiveSheet.Range("c3"), rng, 2, 0)


    I think ur problem is with ur "shlookup"


    HTH


    pangolin

  • Re: VBA vLookup


    Vlookup will produce a runtime error if the value being search for cannot be found. It is something that you have to trap:


    [vba]
    On Error Resume Next


    'Put Vlookup here


    If Err.Number <> 0 Then
    'Not found
    'Write out error
    Debug.Print Err.Number
    Debug.Print Err.Description
    Else
    'Found
    End If


    On Error GoTo 0 ' or On Error Goto YourErrorTrap
    [/vba]


    HTH


    TJ

  • Re: VBA vLookup


    thanks for your suggestions.


    Basically the problem was with the active sheet ie it was looking at the wrong sheet for the lookup, all sorted now thanks


    Mike

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!