VBA - 通过匹配日期选取相邻值

问：

控制表

伊索

我有 3 个工作表 Control、WS1、WS2、Ws3。 每天的工作表数量可能不同。在控制表中，O3-Z3 有一个如下表 每个工作表名称都存在于 R5 的 R 列的控制表中。 在 Ws1、ws2、ws3 中，Q3 中有一个日期与 S3 ROW 中的日期匹配。我想从除控件之外的每个工作表中获取关闭值，匹配每个工作表的控件和Q3中的日期，找到相应的工作表名称并填充关闭余额值（在下一个单元格中找到。请看图2。我的代码在那里，我得到了帮助，但它在 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