Type mismatch...help!

[cstimart]

I've got the following code that gives me an error on the vlookup portion, indicating a type mismatch.

Sub neu()
Dim days(7) As Date
Dim sheettab As String, count As Variant
Dim i As Integer, monat(7) As Integer, jahren(7) As Integer

    days(1) = Range("B10")
    monat(1) = Month(days(1))
    jahren(1) = Year(days(1))
    For i = 2 To 7
        days(i) = days(i - 1) + 1
        monat(i) = Month(days(i))
        jahren(i) = Year(days(i))
    Next i


Workbooks.Open ("M:\REPORT\Duplicate.xls")

    For i = 1 To 7
        If monat(i) = 1 Then
            sheettab = "Jan " & jahren(i)
        ElseIf monat(i) = 2 Then
            sheettab = "Feb " & jahren(i)
        ElseIf monat(i) = 3 Then
            sheettab = "March " & jahren(i)
        ElseIf monat(i) = 4 Then
            sheettab = "April " & jahren(i)
        ElseIf monat(i) = 5 Then
            sheettab = "May " & jahren(i)
        ElseIf monat(i) = 6 Then
            sheettab = "June " & jahren(i)
        ElseIf monat(i) = 7 Then
            sheettab = "July " & jahren(i)
        ElseIf monat(i) = 8 Then
            sheettab = "Aug " & jahren(i)
        ElseIf monat(i) = 9 Then
            sheettab = "Sept " & jahren(i)
        ElseIf monat(i) = 10 Then
            sheettab = "Oct " & jahren(i)
        ElseIf monat(i) = 11 Then
            sheettab = "Nov " & jahren(i)
        ElseIf monat(i) = 12 Then
            sheettab = "Dec " & jahren(i)
        End If
        
        count = count + Application.VLookup(days(i), Sheets(sheettab).Range("B3:N33"), 9, False)

    Next i
ActiveWorkbook.Close
Range("F10") = count
End Sub

Duplicate.xls 1 tab for each month/year combination with the dates of the month in column B. Column B is in Date format and column J is a number.

I have tried count as integer (which it should be), long and variant...all with no luck.

 

[cstimart]

If I change the application.vlookup to workbookfunction.vlookup, I get the following error.

Unable to get the VLookup property of the WorksheetFunction class

 

[MrKowz]

Something in the VLOOKUP is failing.

When i = 1, and it errors, mouseover the following variables and check their values:

• Days(i)
• sheettab

Then verify that sheettab is a sheet in your ActiveWorkbook that currently exists. There appears to be nothing wrong with the code itself.

 

[cstimart]

days(1) = 02/19/2011
sheettab = "Feb 2011"

Also, the workbook that opens does have a sheet named Feb 2011

 

[MrKowz]

Got it - did some testing and it had to do with the fact that it was actually trying to pass days(i) as a string "2/19/2011". Since the cell value isn't 2/19/2011, but is rather actually 40593, it was returning an error. If we convert it to a long, it will work:

Sub neu()
Dim days([COLOR=red][B]1 to 7[/B][/COLOR]) As Date
Dim sheettab As String, count As Variant
Dim i As Integer, monat([COLOR=red][B]1 to 7[/B][/COLOR]) As Integer, jahren([COLOR=red][B]1 to 7[/B][/COLOR]) As Integer
 
    days(1) = Range("B10")[B][COLOR=red].Value[/COLOR][/B]
    monat(1) = Month(days(1[COLOR=black]))[/COLOR]
    jahren(1) = Year(days(1))
    For i = 2 To 7
        days(i) = days(i - 1) + 1
        monat(i) = Month(days(i))
        jahren(i) = Year(days(i))
    Next i
 
 
Workbooks.Open ("M:\REPORT\Duplicate.xls")
 
    For i = 1 To 7
        If monat(i) = 1 Then
            sheettab = "Jan " & jahren(i)
        ElseIf monat(i) = 2 Then
            sheettab = "Feb " & jahren(i)
        ElseIf monat(i) = 3 Then
            sheettab = "March " & jahren(i)
        ElseIf monat(i) = 4 Then
            sheettab = "April " & jahren(i)
        ElseIf monat(i) = 5 Then
            sheettab = "May " & jahren(i)
        ElseIf monat(i) = 6 Then
            sheettab = "June " & jahren(i)
        ElseIf monat(i) = 7 Then
            sheettab = "July " & jahren(i)
        ElseIf monat(i) = 8 Then
            sheettab = "Aug " & jahren(i)
        ElseIf monat(i) = 9 Then
            sheettab = "Sept " & jahren(i)
        ElseIf monat(i) = 10 Then
            sheettab = "Oct " & jahren(i)
        ElseIf monat(i) = 11 Then
            sheettab = "Nov " & jahren(i)
        ElseIf monat(i) = 12 Then
            sheettab = "Dec " & jahren(i)
        End If
 
        count = count + Application.VLookup([COLOR=red][B]CLng(days(i))[/B][/COLOR], Sheets(sheettab).Range("B3:N33"), 9, False)
 
    Next i
ActiveWorkbook.Close
Range("F10") = count
End Sub

 

[cstimart]

YES THANKS!!!!