Reputation: 602
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.
Upvotes: 3
Views: 5765
Reputation: 3573
Try this:
Dim date1 As Date, date2 As Date
Set value for variable
date1 = ws1.Cells(j, 9)
date2 = ws1.Cells(j, 12)
Use variable in your If
statement
If Year(date1) = Year(Date) Or Year(date2) = Year(Date) Then
Upvotes: 2