如何用VBA实现基于可用行的INDEX&MATCH并固定查找范围

原代码中公式使用整列引用（如Sheet2!E:E），不仅计算效率低，也无法适配不同文件的实际数据范围。以下是修改后的代码，会自动基于Sheet2的实际数据行生成固定引用范围，实现宏的跨文件复用：

Sub ApplyFormulas()
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow As Long
    Dim lastRowSheet2 As Long
    Dim newCol As Long
    Dim indexRange As String
    Dim matchRangeA As String
    Dim matchRangeC As String

    ' 绑定工作表对象
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    ' 获取Sheet1最后一行数据（基于A列）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 获取Sheet2最后一行数据（基于A列，可根据实际数据列调整）
    lastRowSheet2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

    ' 生成动态固定范围的字符串
    indexRange = "Sheet2!$E$2:$E$" & lastRowSheet2
    matchRangeA = "Sheet2!$A$2:$A$" & lastRowSheet2
    matchRangeC = "Sheet2!$C$2:$C$" & lastRowSheet2

    ' 新增公式列并设置表头
    newCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
    ws.Cells(1, newCol).Value = "Formula"

    ' 批量写入带动态固定范围的公式
    ws.Range(ws.Cells(2, newCol), ws.Cells(lastRow, newCol)).Formula2 = _
        "=INDEX(" & indexRange & ", MATCH(1, (" & matchRangeA & "=Sheet1!A2)*(" & matchRangeC & "=Sheet1!C2), 0))"
End Sub

关键修改说明： #

• 新增ws2对象和lastRowSheet2变量，专门处理Sheet2的范围计算
• 通过ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row获取Sheet2的实际最后数据行（若数据以其他列为基准，可替换"A"为对应列标）
• 用字符串拼接生成固定范围引用，保留绝对引用符号（$），避免公式下拉时范围偏移
• 同时将MATCH函数中的整列引用改为固定范围，大幅提升公式计算效率

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