excel: vba type mismatch comparing dates

Question

I'm trying to make a comparison between years from two dates (in different columns) and the current year date. If the year is the same then it should write on the corresponding row at column 13 the word "ATUAL", if the year is different then it should write nothing.

This is what I've tried so far.

Sub CopyColumn2()

Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wbk As Workbook
Dim wb As Worksheet

Set wbk = Workbooks.Open("I:\CGP\DEOPEX\01 - Supervisão\01 - Administrativo\06- ADM - Taís e Natalia\Férias - Aprovadas\FÉRIAS TÉCNICOS EXTERNAS.xlsx")
Set ws1 = ThisWorkbook.Sheets("BASE_TOTAL")
Set ws2 = wbk.Worksheets("FUNCIONÁRIOS")

lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row


For j = 2 To lastrow
    If Year(ws1.Cells(j, 9)) = Year(Date) Or Year(ws1.Cells(j, 12)) = Year(Date) Then
        ws1.Cells(j, 13) = "ATUAL"
    Else
        ws1.Cells(j, 13) = ""
    End If
Next j   

End Sub

The dates are placed in columns I and L and all column is set as Date. My Excel is in portuguese so my date format is dd/mm/yyyy.

When I run my code, I receive this message:

Run-time error 13: Type mismatch

And this part is highlighted:

If Year(ws1.Cells(j, 9)) = Year(Date) Or Year(ws1.Cells(j, 12)) = Year(Date) Then

Is anyone knows what is the problem here? It should work since all my dates are formatted the same way.

Answer 1

Try this:

1. Declare date variables Dim date1 As Date, date2 As Date

2. Set value for variable

   date1 = ws1.Cells(j, 9) date2 = ws1.Cells(j, 12)

3. Use variable in your If statement

   If Year(date1) = Year(Date) Or Year(date2) = Year(Date) Then