Results 1 to 5 of 5

Thread: VBS VLookup always returning error 2042 - #N/A
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2006
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBS VLookup always returning error 2042 - #N/A

    Ok, I have been trying to fix this for several weeks to no avail. I am building a calendar in excel that will change the colors of the days depending on data on another sheet within the same workbook. The VB script is supposed to iterate through the range of cells that make up the calendar and first check if it is a date. If it is, it takes that value and performs a vlookup to the other sheet for all of the dates and is supposed to return the corresponding row regarding if the person worked or not. The problem is that the vlookup function always returns the error 2042. I changed the variable to variant so I can catch it the error but I shouldn't be receiving one in the first place. Every date for the year is in this column but it always says it can't find it. The formatting for the dates are different, but to the best of my knowledge, that shouldn't matter. The code is below.

    Code:
    Private Sub cmdCalendar_Click()
    
        Dim myRange As Range
        Dim varName As Variant
        Dim intColor As Integer
        Dim cell As Range
            
        Set myRange = Range("$C$3:$AJ$24")
        
    For Each cell In myRange.Cells
    
        If IsDate(cell.Value) = True Then
            varName = Application.VLookup(cell.Value, Sheet1.Range("$A$6:$G$371"), 7, False)
            If Not IsError(varName) Then
                Select Case varName
                    Case "Working"
                        intColor = Range("$F$26").Interior.ColorIndex
                    Case "RDO"
                        intColor = Range("$F$27").Interior.ColorIndex
                    Case "Chart Day"
                        intColor = Range("$F$28").Interior.ColorIndex
                    Case "Vacation Day"
                        intColor = Range("$F$29").Interior.ColorIndex
                    Case "Sick Day"
                        intColor = Range("$F$30").Interior.ColorIndex
                    Case "Military Day"
                        intColor = Range("$F$31").Interior.ColorIndex
                    Case "Lost Time"
                        intColor = Range("$F$32").Interior.ColorIndex
                End Select
                cell.Interior.ColorIndex = intColor
            End If
        End If
        
    Next cell
    
    End Sub
    I also attempted the Lookup function with the same problem. I tried the vlookup function as a regular formula and it works, but it will not work from VB. I also tried doing the function from the same sheet instead of from a different sheet with no success. Any help would be appreciated.

  2. #2
    Board Regular XLGibbs's Avatar
    Join Date
    Feb 2005
    Location
    Buffalo NY
    Posts
    2,446
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    varName = Application.VLookup(cell.Value, Sheet1.Range("$A$6:$G$371"), 7, False)

    should be

    varName = Application.Evaluate (VLookup(cell, Sheet1.Range("A6:g371"), 7, False))


    Also, you don't need those $ signs in VBA Code.

    Your problem lied in your not telling the application to evaluate the formula...

    But this will be more efficient:
    End Sub
    Private Sub cmdCalendar_Click()

    Dim myRange As Range
    Dim varName As Range
    Dim intColor As Integer
    Dim c As Range

    Set myRange = Range("C3:AJ24")

    For Each c In myRange

    If IsDate(c) Then
    Set varName = myRange.Find(c)
    If Not varName Is Nothing Then
    Select Case c.Offset(, 6)
    Case "Working"
    intColor = Range("F26").Interior.ColorIndex
    Case "RDO"
    intColor = Range("F27").Interior.ColorIndex
    Case "Chart Day"
    intColor = Range("F28").Interior.ColorIndex
    Case "Vacation Day"
    intColor = Range("F29").Interior.ColorIndex
    Case "Sick Day"
    intColor = Range("F30").Interior.ColorIndex
    Case "Military Day"
    intColor = Range("F31").Interior.ColorIndex
    Case "Lost Time"
    intColor = Range("F32").Interior.ColorIndex
    End Select
    c.Interior.ColorIndex = intColor
    End If
    End If

    Next c

    End Sub


    It would also be easier to just identify the color index in the code (1 is Black, 3 is red, 6 is yellow...etc) rather than pulling the color from another cell....but not a too noticeable difference in speed i imagine.

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,
    varName = Application.VLookup(cell.Value, Sheet1.Range("$A$6:$G$371"), 7, False)

    should be

    varName = Application.Evaluate (VLookup(cell, Sheet1.Range("A6:g371"), 7, False))
    No, with all due respect, Evaluate wants a String.

    Evaluate Method

    Converts a Microsoft Excel name to an object or a value.

    expression.Evaluate(Name)
    expression Optional for Application, required for Chart, DialogSheet, and Worksheet. An expression that returns an object in the Applies To list.

    Name Required String. The name of the object, using the naming convention of Microsoft Excel.
    You can and want to use VLookup as is, with the Application qualification.

    I would add .Value to the end of your Range, Excel's Lookup Functions want arrays, not Range Objects...

    It means you're not returning a match. When you say formatting, try to format them the same just to test. If one's text and the other is a serial date, you should expect your Lookup to fail every time.

  4. #4
    New Member
    Join Date
    Jan 2006
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate you are the best. I added the .Value to the Range object and it worked. Thank you very much.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Glad to help, you are welcome.

    Incidentally, welcome to the board.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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