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

    VLookup in VB (Excel XP)

    I would like to include a VLookup function in my VB code for an excel worksheet change sub....how can I do that?
    I want the interior color to change depending on a value in a cell that is dependant on a VLookup function..

    If Not AntRelocation Is Nothing Then
    For Each cel In AntRelocation
    If cel.Value = "X" Then
    If cel.Offset(0, 3).value <> "" Then
    With cel
    .Font.Color = vbWhite
    .Interior.Color = vbBlack
    .Font.Bold = False
    End With
    ElseIf cel.Offset(0, 3).value = "" Then
    With cel
    .Font.ColorIndex = xlColorIndexAutomatic
    .Interior.ColorIndex = xlColorIndexNone
    .Font.Bold = False
    End With
    End If
    End If
    Next cel
    End If

    However since I have a VLookup in the Offset cell, it makes it all black interior...so I want to embed my VLookup directly in the code, is it possible?

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLookup in VB (Excel XP)

    You could try the Application.WorksheetFunction method.

    The following is from the help files of VBA...

    Set myRange = Worksheets("Sheet1").Range("A1:C10")
    answer = Application.WorksheetFunction.Min(myRange)
    MsgBox answer

    Allows you to use the functions of excel in VBA...
    Regards,
    Rudi

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

    Re: VLookup in VB (Excel XP)

    I did try this, however I could not figure out how to write the VLookup function in there, in terms of parameters
    VLookup(lookup value, table array, column index)

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VLookup in VB (Excel XP)

    Wouldn't it be something like:

    answer = Application.Worksheetfunction(cel.Value, AntLocation, 3)

    or perhaps

    answer = Application.Worksheetfunction(cel.Offset( 0, 3).Value, AntLocation, 3)

    Dimensioned Range 'AntLocation' has to be wide enough for the column offset parameter. If this doesn't help, post the source data.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup in VB (Excel XP)

    Have you considered using Conditional formatting for this? You can use any function in the formula Is setting.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLookup in VB (Excel XP)

    Milkyway,
    Is there any chance of getting a copy of your worksheet sothat we can attempt to solve the problem on the actual data?
    If you can attach a copy, please remove any confidential or sensitve info.
    It will help to solve the problem if we can see what we are to work with!
    Regards,
    Rudi

Posting Permissions

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