3

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.

paulinhax
  • 602
  • 3
  • 13
  • 23
  • 3
    Did you check which line throws an error? – Egan Wolf Jul 03 '17 at 16:20
  • 1) step through your code line by line as eluded to above and see where the error occurs. 2) while your dates are all formatted the same way, are they formatted as text or as excel dates? Having them formatted all in the same way but all as text is not going to help you with your formulas. Important to have date in the correct format or you will need a step first to convert it to the format you need. – Forward Ed Jul 03 '17 at 18:17
  • @EganWolf yes, I edited the question. – paulinhax Jul 04 '17 at 11:11
  • @ForwardEd I edited the code with the line highlighted. They're all formatted as Excel dates not text. I guess the problem is with `Date`... I think that the format is mm-dd-yyyy or something different from what I'm used. – paulinhax Jul 04 '17 at 11:14

1 Answers1

2

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

Egan Wolf
  • 3,533
  • 1
  • 14
  • 29
  • It works, thank you! Why did this made difference in my code? I got curious. – paulinhax Jul 04 '17 at 12:01
  • @paulinhax To be honest, I have no idea. It's somehow connected to differences in date format. VBA uses american format, which is `mmddyyyy`, Excel uses your regional, which in Europe (I guess it's everywhere in Europe) is `ddmmyyyy`. Because of this VBA doesn't understand dates taken directly from cell, no matter what format is set. Trick from answer somehow translates dates from Excel format into VBA format, and does it properly. It can be used also for setting dates from VBA to cells. If you do it directly, you can get month and day misplaced. – Egan Wolf Jul 04 '17 at 12:35