VBA中CountIfs统计日期区间数据返回0的问题求助

问题背景 #

需要统计治疗次数，但VBA代码中的CountIfs函数始终返回0。已确认执行到该步骤时，StartDate、EndDate和MachineSerial参数均正确，对应的Excel公式可正常运行。

相关代码 #

VBA中的CountIfs代码 #

Worksheets("AnalysisMachine").Range("D" & i).Value = Application.WorksheetFunction.CountIfs(Worksheets("MachineData").Range("A:A"), MachineSerial, Worksheets("MachineData").Range("D:D"), ">=" & CDate(StartDate), Worksheets("MachineData").Range("D:D"), "<=" & CDate(EndDate) + 0.999988, Worksheets("MachineData").Range("L:L"), ">=1")

可正常运行的Excel公式 #

=COUNTIFS(MachineData!A:A,C3,MachineData!D:D,">="&$A$14,MachineData!D:D,"<="&$A$27+0.999988,MachineData!L:L,">=1")

日期获取逻辑 #

StartDay = Worksheets("AnalysisMachine").Range("A6").Value
StartMonth = Worksheets("AnalysisMachine").Range("A9").Value
StartYear = Worksheets("AnalysisMachine").Range("A12").Value

StartDate = StartDay & "/" & StartMonth & "/" & StartYear
Worksheets("AnalysisMachine").Range("A14").Value = CDate(StartDate)

EndDay = Worksheets("AnalysisMachine").Range("A19").Value
EndMonth = Worksheets("AnalysisMachine").Range("A22").Value
EndYear = Worksheets("AnalysisMachine").Range("A25").Value

EndDate = EndDay & "/" & EndMonth & "/" & EndYear
Worksheets("AnalysisMachine").Range("A27").Value = CDate(EndDate)

MachineSerial获取逻辑 #

For i = 3 To lrow3
    MachineSerial = Worksheets("AnalysisMachine").Range("C" & i).Value
Next i

注：已添加校验确保日期存在且StartDate小于EndDate，月份以January、February等英文全称形式选择，避免日期格式识别错误。

问题原因及修复方案 #

核心原因 #

Excel公式中直接引用单元格时，会自动处理日期的数值格式；但VBA中把日期通过CDate()转换后拼接成字符串，可能因系统区域设置（如日期格式为MM/DD/YYYY或DD/MM/YYYY）导致条件字符串与数据列中的日期格式不匹配，最终无法匹配到有效数据，返回0。

修复方案 #

方案1：直接引用已写入单元格的日期值（与Excel公式逻辑一致）

既然已经将转换后的日期写入A14和A27单元格，直接引用这些单元格的数值作为条件，彻底避免字符串拼接的格式问题：

Worksheets("AnalysisMachine").Range("D" & i).Value = Application.WorksheetFunction.CountIfs( _
    Worksheets("MachineData").Range("A:A"), MachineSerial, _
    Worksheets("MachineData").Range("D:D"), ">=" & Worksheets("AnalysisMachine").Range("A14").Value, _
    Worksheets("MachineData").Range("D:D"), "<=" & Worksheets("AnalysisMachine").Range("A27").Value + 0.999988, _
    Worksheets("MachineData").Range("L:L"), ">=1" _
)

方案2：将日期转换为Excel数值格式拼接

Excel中日期本质是浮点数值，直接使用CDate()返回的数值形式拼接条件，而非字符串：

Dim startDateVal As Double, endDateVal As Double
startDateVal = CDate(StartDate)
endDateVal = CDate(EndDate) + 0.999988

Worksheets("AnalysisMachine").Range("D" & i).Value = Application.WorksheetFunction.CountIfs( _
    Worksheets("MachineData").Range("A:A"), MachineSerial, _
    Worksheets("MachineData").Range("D:D"), ">=" & startDateVal, _
    Worksheets("MachineData").Range("D:D"), "<=" & endDateVal, _
    Worksheets("MachineData").Range("L:L"), ">=1" _
)

优化建议 #

避免使用整列引用（如Range("A:A")），改为引用实际数据区域，大幅提升代码运行效率：

With Worksheets("MachineData")
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    Worksheets("AnalysisMachine").Range("D" & i).Value = Application.WorksheetFunction.CountIfs( _
        .Range("A1:A" & lastRow), MachineSerial, _
        .Range("D1:D" & lastRow), ">=" & Worksheets("AnalysisMachine").Range("A14").Value, _
        .Range("D1:D" & lastRow), "<=" & Worksheets("AnalysisMachine").Range("A27").Value + 0.999988, _
        .Range("L1:L" & lastRow), ">=1" _
    )
End With

内容的提问来源于stack exchange，提问作者Sam