Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet function Vlookup (VB 6.3)

    What is wrong with this:

    For Each Cel In MyRng
    Cel.Value = Application.WorksheetFunction.VLookup(Cel.Offset(0 , -27), Sheets("Stephen - Data").Range("A2:C500"), 3, False)
    Next Cel

    I keep getting the following error message: "Unable to get the Vlookup property of the WorksheetFunction class"

    Thank you

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

    Re: Worksheet function Vlookup (VB 6.3)

    If the lookup value cannot be found in the first column of the specified range, you'll get this error message. You can use Application.VLookup instead of Application.WorksheetFunction.VLookup. This will result in #N/A if the lookup value is not found, just like in a formula.

  3. #3
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet function Vlookup (VB 6.3)

    Thanks Hans,

    Now I have another problem...

    Sub GetData()

    Dim MyRng, cel As Range
    Dim Flag As Boolean

    Set MyRng = Sheets("SMG_SCENARIOS").Range("BJ3:BJ765")

    For Each cel In MyRng
    Flag = Application.WorksheetFunction.IsNA(Application.VLo okup(cel.Offset(0, -27), Sheets("Stephen - Data").Range("A2:C500"), 2, False))
    If Flag = True Then
    cel.Value = ""
    Else: cel.Value = Application.VLookup(cel.Offset(0, -27), Sheets("Stephen - Data").Range("A2:C500"), 2, False)
    End If
    Flag = False
    Next

    MyRng = Sheets("SMG_SCENARIOS").Range("BK3:BK765")

    For Each cel In MyRng
    cel.Value = Application.VLookup(cel.Offset(0, -27), Sheets("Stephen - Data").Range("A2:C500"), 3, False)
    Next

    MyRng = ""

    End Sub

    The cel.value is always = Empty
    And cel is never = to any cell in the range...

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

    Re: Worksheet function Vlookup (VB 6.3)

    We'd have to see (a stripped down copy of) the workbook to know what is happening here.

  5. #5
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet function Vlookup (VB 6.3)

    I made some minor changes to it because I noticed there were some columns hidden which was causing me to reference a wrong column. I need to look for my data (Vlookup value) in column F

    Sub GetData()

    Dim MyRng, cel As Range
    Dim Flag As Boolean

    Set MyRng = Sheets("SMG_SCENARIOS").Range("BJ3:BJ765")

    For Each cel In MyRng
    Flag = Application.WorksheetFunction.IsNA(Application.VLo okup(cel.Offset(0, -56), Sheets("Stephen - Data").Range("A2:C500"), 2, False))
    If Flag = True Then
    cel.Value = ""
    Else: cel.Value = Application.VLookup(cel.Offset(0, -56), Sheets("Stephen - Data").Range("A2:C500"), 2, False)
    End If
    Flag = False
    Next

    MyRng = Sheets("SMG_SCENARIOS").Range("BK3:BK765")

    For Each cel In MyRng
    Flag = Application.WorksheetFunction.IsNA(Application.VLo okup(cel.Offset(0, -57), Sheets("Stephen - Data").Range("A2:C500"), 2, False))
    If Flag = True Then
    cel.Value = ""
    Else: cel.Value = Application.VLookup(cel.Offset(0, -57), Sheets("Stephen - Data").Range("A2:C500"), 2, False)
    End If
    Flag = False
    Next

    MyRng = ""

    End Sub

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

    Re: Worksheet function Vlookup (VB 6.3)

    In the workbook you uploaded, there are no values in sheet SMG_SCENARIOS column F which match a value in sheet Stephen - Data column A. If I put a value from Stephen - Data column A for a row where column B and C contain a value, I get that value in columns BJ and BK of sheet SMG_SCENARIOS.
    Legare Coleman

Posting Permissions

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