excel：vba类型不匹配比较日期 [英] excel: vba type mismatch comparing dates

问题描述

我试图从两个日期（不同列）和当前年份之间的年份进行比较。如果年份相同，那么它应该写在第13列的相应行ATUAL，如果年份不同，那么它应该什么都不写。

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

日期放在列I和L中，所有列设置为日期。我的Excel是在葡萄牙语，所以我的日期格式是dd / mm / yyyy。

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:

运行时错误13：类型不匹配

Run-time error 13: Type mismatch

此部分已突出显示如果Year（ws1.Cells（j，9））=年份（日期）或年份（ws1.Cells（j）），则

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.

推荐答案

尝试这样：

1. 声明日期变量 Dim date1 As Date，date2 As Date



2. 设置变量的值

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）

在中使用变量如果语句

如果年份（date1）=年份（日期）或年份（date2）=年份（日期）然后

这篇关于excel：vba类型不匹配比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！