VBA执行时源文件已打开，INDEX/MATCH仍返回#N/A？及错误排查

1. 初始公式实现异常 #

使用以下VBA代码批量写入跨文件INDEX/MATCH公式：

Range("F2:F" & TotalRowsSQL).Formula = "=INDEX('PATH\" & RåfilDate & "HørkramRå.csv'!AB2:AB" & TotalRowsRAW & ",MATCH(A2,'PATH\" & RåfilDate & "HørkramRå.csv'!A2:A" & TotalRowsRAW & ",0))"

伪代码示意：

Range(MyRange).Formula = "=INDEX('FullPathToDifferentFile'!RangeInThatFile,MATCH(LookupValue,'FullPathToDifferentFile'!RangeInThatFile,0))"

执行过程无报错，但打开公式所在文件后，单元格仅返回#N/A。Excel提示需打开源文件（RåfilDate & "HørkramRå.csv"）才能更新值，但该文件在宏执行时已处于打开状态。

尝试将公式结果转为值：

Range("F2:F" & TotalRowsSQL).Value = Range("F2:F" & TotalRowsSQL).Value

结果仅得到文本型的N/A。

2. 直接用VBA解析公式（首次尝试） #

改用VBA直接计算匹配值，代码如下：

Dim wb1 As Workbook, wb2 As Workbook
Dim wb2path As String
Dim sh As Worksheet
Dim wf As WorksheetFunction

wb2path = "MyActualPath"

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(wb2path)
Set sh = wb1.ActiveSheet
Set wf = Application.WorksheetFunction


wb1.Activate

For x = 2 To TotalRowsSQL
    Range("F" & x).Value = Application.WorksheetFunction.Index(path & "!AB2:AB" & TotalRowsRAW, Application.WorksheetFunction.Match(A2, path & "!A2:A" & TotalRowsRAW, 0))
Next x

For x = 2 To TotalRowsSQL
    Range("G" & x).Value = Application.WorksheetFunction.Index(path & "!AC2:AC" & TotalRowsRAW, Application.WorksheetFunction.Match(A2, path & "!A2:A" & TotalRowsRAW, 0))
Next x

For x = 2 To TotalRowsSQL
    Range("H" & x).Value = Application.WorksheetFunction.Index(path & "!AU2:AU" & TotalRowsRAW, Application.WorksheetFunction.Match(A2, path & "!A2:A" & TotalRowsRAW, 0))
Next x

运行时触发错误1004：无法获取WorksheetFunction类的Match属性。

3. 修正代码后仍报错 #

调整代码，明确工作簿和工作表引用：

Dim wb1 As Workbook, wb2 As Workbook

Windows(Today & "HørkramProducentNavn").Activate
Set wb1 = ThisWorkbook

Windows(RåfilDate & "HørkramRå.csv").Activate
Set wb2 = ThisWorkbook

wb1.Activate

Dim sh As Worksheet
Set sh = wb1.ActiveSheet

Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction


For x = 2 To TotalRowsSQL
    sh.Range("F" & x).Value = wf.Index(wb2.Sheets(1).Range("AB2:AB" & TotalRowsRAW), wf.Match(sh.Range("A" & x), wb2.Sheets(1).Range("A2:A" & TotalRowsRAW, 0)))
Next x

For x = 2 To TotalRowsSQL
    sh.Range("G" & x).Value = wf.Index(wb2.Sheets(1).Range("AC2:AC" & TotalRowsRAW), wf.Match(sh.Range("A" & x), wb2.Sheets(1).Range("A2:A" & TotalRowsRAW, 0)))
Next x

For x = 2 To TotalRowsSQL
    sh.Range("H" & x).Value = wf.Index(wb2.Sheets(1).Range("AU2:AU" & TotalRowsRAW), wf.Match(sh.Range("A" & x), wb2.Sheets(1).Range("A2:A" & TotalRowsRAW, 0)))
Next x

仍触发相同的1004错误，调试发现循环到x=5时出错，且F2-F4单元格始终为空。经排查推测，错误原因是匹配项不存在，计划改用Application.Match替代WorksheetFunction.Match。

内容的提问来源于stack exchange，提问作者Jens-Emil