Having issue with year() function

Question

Im trying to create a module to pull data from a giant spreadsheet that I have on another sheet by year. Every part of the code works except for the part that matches the year to a user entered year.

Here's how I defined the user input and how I tried to write the if statement.

Dim y As variant
y = InputBox("Input year here")

If Year(RptSht.Cells(i, 2)) = y

At this point I get a type mismatch (I've tried setting y as an integer instead). Also just as a note I can use

Year(RptSht.Cells(i, 2)) 

to get a value, it just mismatches with y. Any help would be appreciated.

Answer 1

If Year(RptSht.Cells(i,2)) = y

That's doing too many things. Split it up.

First you want to get the cell at (i, 2):

Dim yearCellValue As Variant
yearCellValue = RptSht.Cells(i, 2)

Now, we can't just assume that yearCellValue is a valid date. We have to know it, otherwise if anything is wrong with the assumption, we'll likely run into a type mismatch error. Use the IsDate function to make sure you're looking at a Date value:

If IsDate(yearCellValue) Then

End If

Inside that conditional block, Year(yearCellValue) is safe. Outside of it, it isn't.

If IsDate(yearCellValue) Then
    If Year(yearCellValue) = y Then
        '...
    End If
End If

Problem is, we don't know that y is a valid value either.

Dim y As variant
y = InputBox("Input year here")
If Not IsNumeric(y) Then Exit Sub 'bail out, we can't go any further.

Answer 2

Here is one way to handle the issue:

Sub gotimm()
    Dim y As Long, RptSht As Worksheet, i As Long

    y = Application.InputBox(Prompt:="Input year here", Type:=1)
    Set RptSht = ActiveSheet
    i = 1

    With RptSht
        If IsDate(.Cells(i, 2)) Then
            If .Cells(i, 2) = y Then
                MsgBox "match"
            Else
                MsgBox "nomatch"
            End If
        Else
            MsgBox "no date in cell " & .Cells(i, 2).Address
        End If
    End With
End Sub