如何在VBA中有效使用 Index/Match
公式?
背景:我的工作表在很大程度上依赖于使用公式来检索输出,该公式基于将特定名称与其名称范围匹配以及特定日期到其日期范围 .
=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)
此外,还有一个硬编码的VBA子产生相同的输出
Sub ExampleHardCode()
Dim Result As Variant
Result = Evaluate("INDEX($C$4:$C$13,MATCH(1,($G$6=$A$4:$A$13)*($G8=$B$4:$B$13),FALSE),1)")
ActiveCell.Value = Result
End Sub
问题:我想生成一个函数,它返回与上述选项相同的输出,但允许用户(i)通过引用相应的单元格选择Name和Date值,以及(ii)选择每个范围(名称范围,日期范围)和输出范围) . 基本上在excel中使用= examplefunction(名称值,名称范围,日期值,日期范围,输出范围) .
我尝试了许多不同的解决方案但没有成功 . 下面是我到目前为止尝试过的一个例子,我认为匹配部分存在问题,即使我尝试设置范围(使用硬编码范围)也会返回错误 .
Function TestIndexMatch1(NameVal As Variant, DateVal As Date)
Dim NameRng As Range
Dim DateRng As Range
Dim OutputRng As Range
Dim Var1 As Variant 'should this be a range or integer?
Dim Result As Variant
Set NameRng = Range("$A$4:$A$13")
Set DateRng = Range("$B$4:$B$13")
Set OutputRng = Range("$C$4:$D$13")
With Application.WorksheetFunction
Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
Result = .Index(OutputRng, Var1, 1)
End With
End Function
我有一个示例工作簿,如果它可以帮助我分享 . 我不确定这是否非常可行,但如果是这样,它确实能帮助很多不熟悉excel的用户正确使用索引/匹配excel公式 . 不幸的是,我的excel技能远远超过了我的VBA技能 .
1 回答
在VBA代码 set ReferenceStyle for Application object to xlR1C1 中使用数组公式(暂时,仅在执行函数期间) . 最后 call Evaluate 得到公式的结果 .
它看起来如下: