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

    WorkSheetFunction.Vlookup (XL97/SR2)

    I'm having a problem with an If statement and Application.WorksheetFunction.VLookup

    I can not get the formula to work under the following code:

    Sub 123()
    If Application.WorksheetFunction.Vlookup("John",Table _Names,1,false) = True Then
    Range("A1") = "Yeah it worked"
    End if
    If Application.WorksheetFunction.Vlookup("John",Table _Names,1,false) = False Then
    Range("A1") = "Not LIsted"
    End If

    End Sub


    Any ideas why?

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkSheetFunction.Vlookup (XL97/SR2)

    It would be really difficult to tell without seeing the table in the worksheet, but I do see a problem.

    Since the third parameter to VLOOKUP is 1, then the VLOOKUP should return the same column that you are looking in. Therefore, if the table contains "John" then the function should return "John", not True or False. It looks like you are expecting VLOOKUP to return True if it gets a hit on the search argument and False if it does not. That is not what VLOOKUP does. You probably need to use the Find method, but I can't tell for sure since it is not clear what you are trying to do.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: WorkSheetFunction.Vlookup (XL97/SR2)

    I think your problem may lie with Table_Names. If that is a variable to represent a lookup table then you should use Range(Table_Names), or if it is the actual name of the lookup table you should use Range("Table_Names") OR [Table_Names], i.e. enclose in square brackets.

    Andrew C

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

    Re: WorkSheetFunction.Vlookup (XL97/SR2)

    Legare,

    You're correct once again. I don't know what I was thinking of trying to use VLookup. Anyway the following code works:

    Sub Test()
    Var1 = "John"
    With Worksheets(1).Range("Table_Names")
    Set c = .Find("" & Var1 & "", LookIn:=xlValues)
    If c Is Nothing Then
    MsgBox "Not Listed-Nothing"
    End
    ElseIf c <> "" & Var1 & "" Then
    MsgBox "Not Listed"
    End
    End If
    If c = "" & Var1 & "" Then
    MsgBox "Yeah it worked"
    End If
    End With
    End Sub

    Thanks again,
    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
  •