1

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:

enter image description here

And this is what I want to happen:

enter image description here

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
2
  • 3
    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. Commented Jul 7, 2017 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. Commented Jul 7, 2017 at 15:04

1 Answer 1

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

Sign up to request clarification or add additional context in comments.

9 Comments

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.
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?
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.
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...
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...
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.