Excel中/ VBA - 使用动态范围索引匹配功能(Excel / VBA - Index Ma

如何有效地使用Index/Match的VBA公式？

背景：我有严重依赖于使用检索基于匹配特定的名称，以它的名字范围以及特定日期到其时间范围的输出的公式的一个工作表。

=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

问题：我想以产生返回输出上述选项相同的，但允许用户（ⅰ）通过参照各个小区和（ii）选择的每个范围（名称范围，时间范围选择名称和日期值的函数和输出范围）。基本上采用= examplefunction（名称值，名称范围，日期值，日期范围，输出范围）在Excel中。

我已经尝试了一些不同的解决方案，但没有成功。下面是我到目前为止已经试过一个例子，我觉得这是与匹配部分，因为即使当我尝试设置范围（用硬编码范围）的问题则返回错误。

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技能。

使用阵列式VBA代码集ReferenceStyle内，用于应用程序对象xlR1C1（ 暂时，仅在被执行的功能）。 最后调用评估 ，以获得公式的结果。

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

所以它看起来表：