VBS VLookup always returning error 2042 - #N/A

Gamblor

New Member
Joined
Jan 30, 2006
Messages
2
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:
<font face=Courier New><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdCalendar_Click()

<SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> varName <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> intColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Set</SPAN> myRange = Range("C3:AJ24")

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange

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

<SPAN style="color:#00007F">Next</SPAN> c

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

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.
 
Upvote 0
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. ;)
 
Upvote 0
Nate you are the best. I added the .Value to the Range object and it worked. Thank you very much.
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,518
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top