VBA - 具有多个条件的索引/匹配功能 [英] VBA - Index / Match function with multiple criteria
问题描述
示例:
我有大量的数据一个指定的范围: Sheets(CustomerAccounts)。Range(CustomerSheetRange))
- 我需要VBA通过检查三个标准从列返回数据:Customer = X ,Type = External,OriginCountry = UAE(原始电子表格中的列不相邻)
条件存储在由用户预先设置的单独变量中。
客户|类型|原产地|目的地|值
X |内部|阿联酋| SA |价值1
Y |内部|阿联酋| SA |价值2
X |外部|阿联酋| SA |价值3
X |外部| ZA |阿联酋|价值4
目前我有以下(相当笨重)的代码,它使用一个条件找到该值 - OriginCountry变量。
代码在预先指定的列 - OriginCountryColumn中搜索它。
ResultString = Application.Index(Sheets CustomerAccounts)范围(CustomerSheetRange),Application.Match(OriginCountry,Sheets(CustomerAccounts)。Range(OriginCountryColumn),0),Application.Match(Values,Sheets(CustomerAccounts)。Range(TitleRowCust) ,0))
我想修改代码也匹配类型和客户。
是否可以扩展以上Index / Matxh函数 - 或者我应该使用其他方法?
任何建议都不胜感激。
您可以通过行检查匹配:
Dim row as Long
With Sheets(CustomerAccounts)。Range(CustomerSheetRange))
For row = 2 To .Rows.Count'从2开始忽略标题!
如果.Cells(row,costumerCol).Value Like costumerCriteria And .Cells(row,typeCol).Value Like typeCriteria And .Cells(row,originCol).Value Like originCriteria Then
'这是一个匹配!
Debug.Print .Cells(row,valueCol)
如果
结束
结束
您必须替换 costumerCol
, typeCol
, originCol
和 valueCol
与相应的列号和 costumerCriteria
, typeCriteria
和 originCriteria
具有指定的条件。
如果列索引也是可变的,请进行搜索对于他们在第一行行走之前。
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.
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.
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.
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?
Any advice is appreciated.
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
, originCol
and valueCol
with corresponding column number and costumerCriteria
, typeCriteria
and originCriteria
with criteria specified.
If column indexes are also variable, make a search for them in first row before walking through rows.
这篇关于VBA - 具有多个条件的索引/匹配功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!