Excel VBA查找日期始终返回空值问题排查与解决

我有一段VBA代码，在日期格式为d-mmm-yy的工作表中运行完全正常，但在日期格式为d/mm/yyyy的工作表中，查找操作始终返回空值。代码逻辑是：先在第一张工作表筛选出目标年月的日期，再到另一工作表查找该日期，复制对应行的单元格数据带回原工作表。

可运行版本代码 #

Set rngFnd = wsElog1.Range("A7:A37")

Dim i As Integer
i = 0
    For Each rngRow In rngInput.Rows
        vntInput = rngRow.Cells(1, 1)  ' Dates in Column A

        If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
        
            nYear = year(vntInput)
            nMonth = Month(vntInput)
        
            ' Is the date within target month/year?
            If nYear = nTargetYear And nMonth = nTargetMonth Then
               SrchDt = Format(rngRow.Value, "d-mmm-yy")
           
               Set rngFnd = wsElog1.Range("A7:A37").Find(SrchDt, LookIn:=xlValues)
           
                           
                    Set rngTarget = wsTEff.Range("B" & rngRow.Row)
            
                    wsElog1.Range("GW" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 0).PasteSpecial xlPasteValues
                    wsElog1.Range("GX" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 1).PasteSpecial xlPasteValues
                    wsElog1.Range("HL" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 2).PasteSpecial xlPasteValues
                    wsElog1.Range("HH" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 3).PasteSpecial xlPasteValues
                    wsElog1.Range("HK" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 4).PasteSpecial xlPasteValues
                    wsElog1.Range("GZ" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 5).PasteSpecial xlPasteValues
                    wsElog1.Range("HO" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 6).PasteSpecial xlPasteValues
                    wsElog1.Range("HP" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 7).PasteSpecial xlPasteValues
                
                         
                i = i + 1
            End If
        End If
    Next rngRow

不可运行版本代码 #

Dim c As Integer
c = 0
    For Each rngRow In rngInput.Rows
        vntInput = rngRow.Cells(1, 1)  ' Dates in Column A

        If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
        
            nYear = year(vntInput)
            nMonth = Month(vntInput)
        
            ' Is the date within target month/year?
            If nYear = nTargetYear And nMonth = nTargetMonth Then
               SrchDt1 = Format(rngRow.Value, "d/mm/yyyy")

               Set rngFndA = wsRlog.Range("A:A").Find(What:=SrchDt1, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
           
                    Set RngTarget1 = wsTEff.Range("M" & rngRow.Row)
                
                    wsRlog.Range("CP" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 0).PasteSpecial xlPasteValues
                    wsRlog.Range("CM" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 1).PasteSpecial xlPasteValues
                    wsRlog.Range("CX" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 2).PasteSpecial xlPasteValues
                    wsRlog.Range("CS" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 3).PasteSpecial xlPasteValues
                    wsRlog.Range("CU" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 4).PasteSpecial xlPasteValues
                    wsRlog.Range("CN" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 5).PasteSpecial xlPasteValues
                    wsRlog.Range("CL" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 6).PasteSpecial xlPasteValues
                    wsRlog.Range("CD" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 7).PasteSpecial xlPasteValues

                         
                c = c + 1
            End If
        End If
    Next rngRow

问题现象 #

不可运行版本中，Range.Find方法对rngFndA始终返回空值，无法定位到目标日期单元格。

解决方案 #

给SrchDt1添加明确的日期类型声明：

Dim SrchDt1 As Date

原因说明 #

未声明类型时，Format函数返回的是字符串类型，而目标工作表中的日期是Excel内部存储的日期序列号（数值类型），仅显示为d/mm/yyyy格式。用字符串去匹配日期值时，容易因系统区域设置、格式细节差异导致匹配失败。声明为Date类型后，VBA会自动处理日期的实际数值匹配，不受显示格式影响，从而能正确找到目标单元格。

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