Excel / VBA - 使用动态范围的索引匹配功能 [英] Excel / VBA - Index Match function using Dynamic Ranges

问题描述

如何在VBA中有效地使用索引/匹配公式？

How to effectively use an Index/Match formula in VBA?

背景：我有一个工作表很大程度上依赖于使用基于匹配特定名称到其名称范围以及特定日期到其日期范围来检索输出的公式。

Background: I have a worksheet that relies heavily on the use of a formula that retrieves an output based on matching a specific name to its name range as well as a specific date to its date range.

=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)

此外，还有一个硬编码的VBA子产生相同的输出

Additionally, there is a hard-coded VBA sub that produces the same output

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）通过参考相应的单元格选择名称和日期值，（ii）选择每个范围（名称范围，日期范围和输出范围）。在excel中基本上使用= examplefunction（名称值，名称范围，日期值，日期范围，输出范围）。

Question: I’d like to produce a function that returns the same output as the above options but allows the user to (i) select the Name and Date values by referencing respective cells and (ii) select each range (name range, date range and output range). Essentially using =examplefunction(name value, name range, date value, date range, output range) in excel.

我尝试了一些不同的解决方案，但没有成功。下面是我迄今为止尝试过的一个例子，我认为匹配部分有问题，即使我尝试设置范围（使用硬编码的范围）它返回一个错误。

I’ve tried a number of different solutions but with no success. Below is an example of what I've tried so far, I think there is an issue with the match portion as even when I try to set the ranges (with hardcoded ranges) it returns an error.

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来正确使用index / match excel公式。不幸的是，对于我来说，我的excel技能远远超过了我的VBA技能。

I have an example workbook if it helps I can share. I'm not sure if this is very do-able but if so it'd really help a lot of users that aren't familiar enough with excel to use the index/match excel formula correctly. Unfortunately for me my excel skills far exceeds my VBA skills.

推荐答案

要在VBA代码中使用数组公式，将ReferenceStyle设置为应用对象到xlR1C1 （暂时只在你的函数执行时）。最后调用Evaluate 获取公式的结果。

To use array formulas within VBA code set ReferenceStyle for Application object to xlR1C1 (temporarily, only during your function is executed). Finally call Evaluate to get the result of the formula.

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

所以它在表单上：

这篇关于Excel / VBA - 使用动态范围的索引匹配功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！