引用下一行以进行评估和索引匹配

Question

我有这个公式

Dim lastrow As Long
lastrow = Sheets("Tab 1").Range("A" & Rows.Count).End(xlUp).Row
With Range("AP2:AP" & lastrow)
.Value = Evaluate("=IFERROR(INDEX('Usages'!A:G,MATCH(1,('Usages'!F:F=""No"")*('Usages'!C:C=N2)*('Usages'!D:D<=AO2)*('Usages'!E:E>=AO2),0),2),""Not Found"")")
End With

如何使我的匹配函数引用下一行 例如 N2，N3 AO2，AO3 目前，我仅根据整个列的 N2 和 AO2 参数获取值。

此公式放置在与"用法"不同的工作表中。

任何帮助都非常感谢

Answer 1

尝试，

dim ulr as long, lastrow As Long
lastrow = Sheets("Tab 1").Range("A" & Rows.Count).End(xlUp).Row
with worksheets("Usages")
ulr = .cells(.rows.count, "B").end(xlup).row
end with
With Worksheets("Tab 1").Range("AP2:AP" & lastrow)
.Formula = "=IFERROR(INDEX('Usages'!B:B, AGGREGATE(15, 6, ROW($1:$" & ulr & ")/" & _
"(('Usages'!F$1:F$" & ulr & "=""No"")*('Usages'!C$1:C$" & ulr & "=N2)*('Usages'!D$1:D$" & ulr & "<=AO2)*('Usages'!E$1:E$" & ulr & ">=AO2)), 1)), ""Not Found"")"
.Value = .Value
End With