VBA使用动态工作簿名称调用Index与Match函数跨表取值问题求助

问题修正点 #

• 拼写错误：原代码中多次出现Applications，VBA中应用对象正确拼写为Application，多写的s会导致对象调用失败
• 缺失工作簿存在性校验：未匹配到符合Daily Dashboard*命名规则的已打开工作簿时，ActiveWorkbook会默认指向当前运行宏的Daily工作簿，相当于自己匹配自己，自然返回空值
• 单元格未绑定工作表：Range("T" & Counter)、Range("U" & Counter)未指定所属工作表，默认取当前激活的工作表数据，若激活表不是ws1会导致判断条件完全错误
• 错误处理逻辑不完善：On Error Resume Next仅跳过错误未做兜底，匹配失败时无感知，也没有关闭错误捕获的语句

修正后完整代码 #

Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbName As String
' 匹配历史表标识
wbName = "Daily Dashboard"
' 初始化wb2为Nothing用于后续判断
Set wb2 = Nothing
For Each wb In Application.Workbooks
    If wb.Name Like wbName & "*" Then
        Set wb2 = wb
        Exit For
    End If
Next wb

' 未找到历史表直接提示退出
If wb2 Is Nothing Then
    MsgBox "未找到已打开的历史日报表，请确认后重试", vbExclamation
    Exit Sub
End If

Set wb1 = Workbooks("Daily")

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = wb1.Worksheets("Fund Loan Details")
Set ws2 = wb2.Worksheets("Fund Loan Details")

Dim End_Row_1 As Long
Dim End_Row_2 As Long
End_Row_1 = ws1.Range("C" & ws1.Rows.Count).End(xlUp).Row
End_Row_2 = ws2.Range("C" & ws2.Rows.Count).End(xlUp).Row

Dim Counter As Long
Dim matchResult As Variant
For Counter = 3 To End_Row_1
    ' 所有单元格都绑定ws1，避免激活表变动影响
    If ws1.Range("T" & Counter) = "Y" And ws1.Range("U" & Counter) = "" Then
        ' 先用Variant接收Match结果，判断是否匹配成功
        matchResult = Application.Match(ws1.Cells(Counter, 3).Value, ws2.Range("C3:C" & End_Row_2), 0)
        ' 匹配成功再赋值，失败则留空/可自定义填充内容
        If Not IsError(matchResult) Then
            ws1.Cells(Counter, 21).Value = Application.Index(ws2.Range("U3:U" & End_Row_2), matchResult)
        End If
    End If
Next Counter
' 可选：操作完成提示
MsgBox "历史备注提取完成", vbInformation

额外注意事项 #

• 运行宏前请确保前一日/上周五的历史报表已经处于打开状态
• 如果需要避免手动打开历史报表的遗漏，可以在匹配逻辑前新增自动读取对应日期文件、自动打开的代码
• 基金唯一标识列（C列）如果存在格式差异（比如一表为文本型、一表为数值型）也会导致匹配失败，匹配前建议先统一两表C列的单元格格式

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