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, Jul 07 '17 at 14:56
@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, Jul 07 '17 at 15:04

Vityata · Answer 1 · 2017-07-07T15:17:22.873

1

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

edited Jul 07 '17 at 15:17

answered Jul 07 '17 at 14:53

Vityata

42,633
8
55
100

Let's see if this beer will happen! Anyway, I've tested two things: when I don't use `Option Explicit` the code runs fine and the message box appoints to `I6` also as a date. And then it gets type mismatch again. Then when I try with `Option Explicit` I get `k = ws1.Cells(j, 1).Value` highlighted and variable not defined. – paulinhax Jul 07 '17 at 15:03
The idea of `Option Explicit`, is to tell you that you have forgotten to write `Dim k as Something` on the top. And in your screenshot `I6` is not a date format. And what do you mean by saying MessageBox appoints to `I6` also as a date? – Vityata Jul 07 '17 at 15:10
Yes, yes, I'm declaring all my variables now. What I've tryed to say is that `I6` is not a date, but the whole column is formatted as a date even if there is a text on it. So I thought it would work the same way. – paulinhax Jul 07 '17 at 15:12
Well, if the whole column is formatted as a `Date`, but the value in `I6` is not a date, the VBA engine is still looking for a date, to parse into `date1` And the Portuguese string cannot be parsed to a date in any way... – Vityata Jul 07 '17 at 15:13
Oh, now I got it. So what I've thought doesn't work. Anyway, I can't change this value. It should be `Não marcou férias` so I must change my comparison, I guess... – paulinhax Jul 07 '17 at 15:16
@paulinhax - you have plenty of options to fix it. Take a look at my edited answer, I point out 2. The `IsDate()` check is the better one. – Vityata Jul 07 '17 at 15:19
I've seen that update now, thanks. Just another doubt, when I declare a variable with address as `firstAddress` it should be a string or a range? Because I know `.Adress` property returns a string but I get type mismatch again. – paulinhax Jul 07 '17 at 15:23
@paulinhax - in this case - String. But if you are not sure, declare it as a Variant like this `Dim firstAddress` and later see what VBA thinks it is. To do it, put a stop on the line after the variable and click `Shift + F9` It would write something like `Variant/String` in the VB Editor. – Vityata Jul 07 '17 at 15:26
Actually, it is a String! But I've return to my first problem. I'll post my edited code. – paulinhax Jul 07 '17 at 15:36

excel: vba type mismatch using find

1 Answers1