VBA - 具有多个条件的索引/匹配功能

VBA - Index / Match function with multiple criteria

提问by Mr_Oppenheimer

I am facing an issue when trying to compile a multiple criteria Index/Match code in VBA. This might be simple - but i am fairly new to VBA and nothing i have found around here worked.

尝试在 VBA 中编译多条件索引/匹配代码时遇到问题。这可能很简单 - 但我对 VBA 相当陌生，我在这里没有发现任何有用的东西。

Example: I have a large amount of data in a specified range: Sheets("CustomerAccounts").Range(CustomerSheetRange))- I need VBA to return data from column titled "Values" by checking three criteria: Customer = X, Type = External, OriginCountry = UAE (columns are not adjacent in the original spreadsheet) The criteria are stored in separate variables set by user of the macro beforehand.

示例：我在指定范围内有大量数据：Sheets("CustomerAccounts").Range(CustomerSheetRange))- 我需要 VBA 通过检查三个条件从标题为“值”的列中返回数据：Customer = X、Type = External、OriginCountry =UAE（列在原始电子表格）标准存储在由宏的用户预先设置的单独变量中。

Customer   | Type      | Origin        | Destination        |  Values
X          | Internal  | UAE           |  SA                |  Value 1
Y          | Internal  | UAE           |  SA                |  Value 2
X          | External  | UAE           |  SA                |  Value 3
X          | External  | ZA            |  UAE               |  Value 4

At the moment i have the following (quite bulky) code which finds the value using one criteria - OriginCountry variable. The code searches for it in a pre-specified column - OriginCountryColumn.

目前我有以下（相当庞大的）代码，它使用一个标准来查找值 - OriginCountry 变量。代码在预先指定的列 - OriginCountryColumn 中搜索它。

ResultString = Application.Index(Sheets("CustomerAccounts").Range(CustomerSheetRange), Application.Match(OriginCountry, Sheets("CustomerAccounts").Range(OriginCountryColumn), 0), Application.Match("Values", Sheets("CustomerAccounts").Range(TitleRowCust), 0))

I would like to modify the code to also match the Type and The customer. Is it possible to expand the above Index/Matxh function - or should i use a different approach?

我想修改代码以匹配类型和客户。是否可以扩展上述 Index/Matxh 函数 - 还是应该使用不同的方法？

Any advice is appreciated.

任何建议表示赞赏。

回答by LS_???

You may walk through rows checking matches:

您可以遍历行检查匹配：

Dim row as Long
With Sheets("CustomerAccounts").Range(CustomerSheetRange))
    For row = 2 To .Rows.Count 'Starts in 2 to ignore header!
        If .Cells(row, costumerCol).Value Like costumerCriteria And .Cells(row, typeCol).Value Like typeCriteria And .Cells(row, originCol).Value Like originCriteria Then
            'This is a match!
            Debug.Print .Cells(row, valueCol)
        End if
    Next
End With

You must replace costumerCol, typeCol, originColand valueColwith corresponding column number and costumerCriteria, typeCriteriaand originCriteriawith criteria specified.

您必须更换costumerCol，typeCol，originCol并valueCol与相应的列数和costumerCriteria，typeCriteria并originCriteria与指定的标准。

If column indexes are also variable, make a search for them in first row before walking through rows.

如果列索引也是可变的，请在遍历行之前在第一行中搜索它们。

回答by Tarik

First, format the range containing your data to a Table (See http://office.microsoft.com/en-001/excel-help/quick-start-create-an-excel-table-HA010359200.aspxon how to do that). Once done, use the following VBA code:

首先，将包含数据的范围格式化为表格（有关如何操作，请参阅http://office.microsoft.com/en-001/excel-help/quick-start-create-an-excel-table-HA010359200.aspx那）。完成后，使用以下 VBA 代码：

SomeCustomer = Range("...").Value
SomeType = Range("...").Value
SomeOrigin = Range("...").Value
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria=SomeCustomer
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria=SomeType
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria=SomeOrigin

Notes:

笔记：

• You might have to customize this macro for your specific needs
• Name of table can be found/modified through Formulas>Name Manager
• ActiveSheet might be modified to the actual sheet you are using

• 您可能需要根据您的特定需求自定义此宏
• 表格名称可以通过公式>名称管理器找到/修改
• ActiveSheet 可能会修改为您正在使用的实际工作表