excel: vba type mismatch using find

Question

I have this sheet where:

If the value in cell in column I is "Não marcou férias" then it should search the whole column A and find all times that the unique number for that specific person appears more than once.
If the unique number appears twice or more and the column I of that row is not "Não marcou férias" then it should compare with current year and write ATUAL in the corresponding cell in column K. If it's true then the row in column K that have "Não marcou férias" should be blank.

Here is my sheet:

And this is what I want to happen:

As you can see as I have two entries with number 59837 and one of them is "Não marcou férias", I want it to run through column A and find if exist another entry and as it is current, it should write "ATUAL" on it.

Here is my code:

Sub test()

Dim j As Long
Dim lastrow As Long
Dim ws1 As Worksheet
Dim date1 As Date, date2 As Date

Set ws1 = Sheets("Plan1")

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

For j = 2 To lastrow

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

If ws1.Cells(j, 9) = "Não marcou férias" Then
k = ws1.Cells(j, 1).Value
    With ws1.Range("A2:A" & lastrow)
    Set c = .Find(k, LookIn:=xlValues)

    If Not c Is Nothing Then
    firstAddress = c.Address
        Do
            If ws1.Cells(j, 9) <> "Não marcou férias" Then
                If Year(date1) = Year(Date) Or Year(date2) = Year(Date) Then
                    ws1.Cells(j, 13) = "ATUAL"
                Else
                    ws1.Cells(j, 13) = ""
                End If
            End If

    Set c = .FindNext(c)

        If c Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
    End If
DoneFinding:
    End With
End If

Next j

End Sub

But when I try to run I get:

Run-time error 13: Type mismatch

And this line is highlighted:

date1 = ws1.Cells(j, 9)

My Excel is in portuguese so here we use date format like dd/mm/yyyy and all my columns are set up as dates correctly.

Is there anyone with suggestions? I don't know why I'm getting this type mismatch again since I've already applied THIS solution.

EDIT: I've tested the suggested solutions, but I still have the same problem in the same line.

Option Explicit
Sub test2()

Dim j As Long
Dim lastrow As Long
Dim ws1 As Worksheet
Dim date1 As Date, date2 As Date
Dim k As Long
Dim c As Range
Dim firstAddress As String

Set ws1 = Sheets("Plan1")

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

For j = 2 To lastrow

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

If Not IsDate(ws1.Cells(j, 9)) Then
    k = ws1.Cells(j, 1).Value
    With ws1.Range("A2:A" & lastrow)
    Set c = .Find(k, LookIn:=xlValues)

    If Not c Is Nothing Then
    firstAddress = c.Address
        Do
            If IsDate(ws1.Cells(j, 9)) Then
                If Year(date1) = Year(Date) Or Year(date2) = Year(Date) Then
                    ws1.Cells(j, 13) = "ATUAL"
                Else
                    ws1.Cells(j, 13) = ""
                End If
            End If

    Set c = .FindNext(c)

        If c Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
    End If
DoneFinding:
    End With
End If

Next j

End Sub

If ws1.Cells(j, 9) = "Não marcou férias" Then: ws1.Cells(j, 9) is not a date, so date1 = ws1.Cells(j, 9) is of course type mismatch because of Dim date1 As Date :) . I think you should first check with If IsDate(ws1.Cells(j, 9) ) and take that into account. — A.S.H
@A.S.H I really got it although I need to do this comparison in that column. I tried the Vityata's solution and Message Box appoints I6 as a date, for example. I could try to change my comparison instead. — paulinhax

Vityata Vityata · Accepted Answer · 2017-07-07T14:53:47

Always make sure that you include Option Explicit on top of your code.
Simply write msgbox ws1.Cells(j, 9).address on the line above the line with the error.
Check the MsgBox coming before the error.
I can bet a virtual beer, that the address is pointing to is a value, which is not a date. For example I6 in your screenshot, saying something nice about vacations and booking in Portuguese.

In your code, you may add something like If IsDate(ws1.cells(j,9)) then. It would check whether the given value is a date or not. In your case Não marcou férias cannot be parsed as a Date format, thus you get an error. Or simply try to replace the code positions like this:

For j = 2 To lastrow    
   If ws1.Cells(j, 9) <> "Não marcou férias" Then
      date1 = ws1.Cells(j, 9)
      date2 = ws1.Cells(j, 12)
      k = ws1.Cells(j, 1).Value
      With ws1.Range("A2:A" & lastrow)

If the value is not "Nao marcou ferias" then its probably a date (although, its better to use the IsDate()).

Have fun, TGIF

excel: vba type mismatch using find

1 Answers