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


  • Re: VBA vLookup


    well this code seems to work for me

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

    I think ur problem is with ur "shlookup"



  • 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:

    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
    End If

    On Error GoTo 0 ' or On Error Goto YourErrorTrap



  • 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


Participate now!

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