Type mismatch...help!

C

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
I've got the following code that gives me an error on the vlookup portion, indicating a type mismatch.

Code: 
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Try:

Code: 
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")[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(days(i), Sheets(sheettab).Range("B3:N33"), 9, False)

    Next i
ActiveWorkbook.Close
Range("F10") = count
End Sub
 
Upvote 0
When it errors, what is the value that is supposed to be returned by the Application.Vlookup?
 
Upvote 0
As it progresses through, does it ever return a non-numeric value? I'm thinking what might be happening is it is trying to add a string value to the numeric value.
 
Upvote 0
MrKowz said:
As it progresses through, does it ever return a non-numeric value? I'm thinking what might be happening is it is trying to add a string value to the numeric value.
Click to expand...

It's crashing on the first attempt, so it's never setting a value. I did verify that source column B is a date format and column J is a number.
 
Upvote 0
Hmm... I'll continue to ponder on this, but in the mean time, your arrays aren't declared right:

Code: 
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(days(i), Sheets(sheettab).Range("B3:N33"), 9, False)

    Next i
ActiveWorkbook.Close
Range("F10") = count
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

dreid1011
  • Question Question
Recursive Sub Calls and Stack Overflow
2 3
Replies
21
Views
283
6StringJazzer
6StringJazzer
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
361
Trixterz
T
G
  • Solved
Skip Row When Column Word Meet
2
Replies
14
Views
376
Peter_SSs
Peter_SSs
J
  • Solved
Run time error for Worksheet Change in VBA
2
Replies
17
Views
446
jtemp57
J
P
  • Question Question
Copy a worksheet from one workbook to another workbook based on conditional statements
Replies
5
Views
366
HighAndWilder
H

Forum statistics

Threads
1,224,616
Messages
6,179,908
Members
452,949
Latest member
beartooth91

Share this page

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