Syntax issue - Cannot figure out

Question

I get a Type Mismatch error only on certain choices. I have a userform that the user will click on the month they need (value held in Listbox1). The following code determines a couple of things in order to figure out how to populate Combobox2 (days of the month). For example, if this month is chosen, I only want to populate combobox2 with the remaining days of the month beginning with the 30th, if today is the 29th.

I also have to check the year and that is where the problem is coming in. It looks like I have the cell formatted as "General" for BM4:BM8. Yet, I get "Type Mismatch" for December and February.

FLOW OF CODE:

1. User clicks on month in Listbox1.
2. The following code is executed to make sure the correct year is picked up.
   yr = Application.VLookup(ListBox1.Value, Worksheets("RideSch").Range("BL4:BM8"), 2)

The above formula is highlighted when I click DEBUG on the error message.

The variable "yr" is DIM as Integer. It is later used in this formula. I do not know what it should be DIM as for this formula to work, but it works for three of the five months DIM as an Integer
d1 = DateValue(Month(DateValue(ListBox1.Value & "-1900")) & "/1" & "/" & yr)

Private Sub ListBox1_Click()    'Listbox of Months
    Dim d1 As Date
    Dim i As Long
    Dim m As Long
    Dim d2 As Integer
    Dim startday As Integer
    Dim numMonth As Integer
    Dim lbMonth As Integer
    Dim yr As Integer
    
    ActiveWindow.ScrollRow = (ListBox1.ListIndex * 38) + 1
    ComboBox2.Clear
    ComboBox2.Value = ""
    If ListBox1.Value <> "" Then
        yr = Application.VLookup(ListBox1.Value, Worksheets("RideSch").Range("BL4:BM8"), 2)
        MsgBox yr
        d1 = DateValue(Month(DateValue(ListBox1.Value & "-1900")) & "/1" & "/" & yr)   'Date for first day of month chosen
        d2 = Day(DateSerial(Year(d1), Month(d1) + 1, 1) - 1)
        numMonth = Month(d1)
        MsgBox "First day of the Month = " & d1 & "   Number of days/month = " & d2 & "   numMonth = " & numMonth
        'lbMonth = Month(DateValue("01-" & ListBox1.Value & "-1900"))
        'If month of ride time is <= current month, then startday must be no less than the day after entry is made,
        'If month of ride time is after current month, startday is first of the month, ie 1
        If Month(d1) = Month(Now()) Then
           startday = Day(Date) + 1
        Else
           startday = 1
        End If
        'MsgBox "startday = " & startday
        
        For i = startday To d2
            ComboBox2.AddItem i
        Next i
    End If
    
theend:
End Sub

Answer 1

Dear @Les M,

Thank you for reaching out to the Microsoft 365 Q&A forum community support team. I completely understand the frustration when a script works perfectly for some inputs but throws an error for others. Troubleshooting logical errors like "Type Mismatch" can be incredibly time-consuming, especially when the cause isn't immediately visible in the cell formatting. 

The reason you are seeing this error specifically for December and February is likely due to how Application.VLookup behaves: 

1. Missing Data or Range: Your lookup range is set to BL4:BM8, which only covers 5 rows. If December or February are not within those 5 rows, or if there is a tiny typo (like a hidden space) in those cells, VLookup returns an error value (like #N/A). 
2. Variable Type Conflict: You have yr defined as an Integer. An Integer can only hold numbers. When VLookup fails and returns an error code, VBA tries to "force" that error into your Integer variable, resulting in the Type Mismatch. 

To fix this, change your variable type to Variant. This allows the variable to hold either the year or the error code without crashing, letting us handle the problem gracefully. 

For more details, please refer to Microsoft’s official documentation:  
VLOOKUP function - Microsoft Support 

Type mismatch (Error 13) | Microsoft Learn 

We truly appreciate you're understanding that our Q&A forum has limited resources for deep code troubleshooting. Complex projects often require detailed code examples and extensive testing that go beyond the scope of this forum. 

My goal is to guide you in the right direction for immediate fixes, but for advanced scripting support, the best place is Stack Overflow. The community there is very active and can provide precise, specialized guidance for Newest 'vba' Questions - Stack Overflow 

When posting there, it is helpful to include: 

• A clear description of the problem. 
• Any specific error messages (like "Error 13"). 
• The relevant parts of your script and a sample of your data. 

I hope the above information is clear. Thank you for your understanding and cooperation. Please let me know how it goes after trying the steps above. I’m here to help further if needed.  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.