VBA问题：通过匹配日期提取相邻的结算余额值

我有Control、WS1、WS2、Ws3等数量每日变化的工作表，需实现以下需求：

• Control工作表：O3:Z3区域为日期行，R列从R5开始记录所有其他工作表的名称
• 子工作表（WS1、WS2等）：Q3单元格为对应日期，需与Control表S3开始的日期行匹配；提取"Closing"右侧的结算余额值，匹配日期和工作表名称后，填入Control表的对应位置

现有代码在处理2023年10月4日之后的日期时失效，代码如下：

Option Explicit

Sub Demo()
    Dim ws As Worksheet, mainSht As Worksheet
    Dim rFind As Range, rDate As Range, rSht As Range
    Dim iDate, arrDate, arrSht
    Dim DateCnt As Long, ShtCnt As Long
    Dim i As Long, iRow As Long, iCol As Long
    Const KEYWORD = "Closing"
    Set mainSht = Sheets("Control")
    ' Load date and sheet name
    With mainSht
        arrDate = .Range("S3", .Cells(19, .Columns.Count).End(xlToLeft)).Value
        DateCnt = UBound(arrDate, 2)
        arrSht = .Range("R5", .Cells(.Rows.Count, 18).End(xlUp)).Value
        ShtCnt = UBound(arrSht)
    End With
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Control" Then
            ' Search KEYWORD
            Set rFind = ws.Cells.Find(What:=KEYWORD, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                iDate = ws.Range("Q3").Value
                ' Validate Q3 is Date
                If IsDate(iDate) Then
                    iRow = 0: iCol = 0
                    ' Match date in row 3
                    For i = 1 To DateCnt
                        If arrDate(1, i) = iDate Then
                            iCol = i + 1
                            Exit For
                        End If
                    Next i
                    If iCol > 0 Then
                        ' Match sheet name in Column B
                        For i = 1 To ShtCnt
                            If arrSht(i, 1) = ws.Name Then
                                iRow = i + 4
                                Exit For
                            End If
                        Next i
                        If iRow > 0 Then
                            ' Populate Control sheet
                            mainSht.Cells(iRow, iCol).Value = rFind.Offset(0, 1)
                        End If
                    End If
                End If
            End If
        End If
    Next ws
End Sub

工作表截图说明 #

• Control工作表：展示O3:Z3的日期行，R列从R5开始依次列出各子工作表名称，对应日期列下方为待填充结算余额的单元格
• Ws1工作表：Q3单元格显示对应日期，表格内存在"Closing"关键字，其右侧单元格为需提取的结算余额值

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