VBA UDF使用动态数组查找多列条件匹配 [英] VBA UDF to find multi column criteria match using dynamic arrays
问题描述
我发布了一个有关寻找符合多列标准的比赛的问题.提供的答案效果很好.但是我正在尝试使用多少列标准来使其成为我的项目的通用解决方案.
这是我要参考的问题:
为简化我想要的结果:
获取表1，其中包含客户列表:
A B C D-----------------------------------------------------------1 |姓名|电邮|电话|ISMATCH?|-----------------------------------------------------------2 |史蒂夫·乔布斯|stevejobs@gmail.com |123456 |真实|-----------------------------------------------------------3 |比尔·盖茨|billgates@apple.com |123456 |真实|-----------------------------------------------------------4 |史蒂夫·沃兹(Steve Woz)|stevewoz@outlook.com |123456 |错误|----------------------------------------------------------- 使用表2，其中对这些客户端进行了详细说明，但是每个客户端都使用不同的参数查询:
J K L M-----------------------------------------------------------1 |姓名|公司简介电话|电邮|-----------------------------------------------------------2 |史蒂夫·乔布斯|苹果|123456 |stevejobs@gmail.com |-----------------------------------------------------------3 |比尔·盖茨|苹果|123456 |billgates@apple.com |-----------------------------------------------------------4 |史蒂夫·旺德|苹果|123456 |steviewon@outlook.com |----------------------------------------------------------- 我希望能够选择要评估的条件，然后在表2中选择其对应的列.因此，回到表1 D2中，应该是这样的:
= MULTIMATCHEXISTS(CRITERIA([@ NAME]，[@EMAIL])，Table2 [名称]，Table2 [电子邮件]) 但是可以说，对于比尔·盖茨，我要检查的不只是这两个标准，因此表1 D3将是:
= MULTIMATCHEXISTS(CRITERIA([@ NAME]，[@ PHONE]，[@ EMAIL])，Table2 [名称]，Table2 [电话]，Table2 [电子邮件]) 对于史蒂夫·沃兹(Steve Woz)表1 D4:
= MULTIMATCHEXISTS([@ Name]，Table2 [Name]) 这些是我的UDF实际应用的实例.我试图使两个参数动态灵活.我生活在指定范围之外，但不必特定于该范围
我找到了适合自己和需要的解决方案；我玩弄了查尔斯的答案，但根据他的反馈无法弄清楚结构.虽然，我确实采纳了我从他的反馈中学到的一些信息并加以运用.希望这可以帮助其他人，无论是丑陋的还是粗糙的.我认为我很难尝试可视化循环中的循环.因此，我决定采用 Index/Match 方法.
更重要的是，我真的很想学习这种语言，因此，如果您是专业人士并且发现错误的地方，我应该着重于此.
公共函数MULTIMATCHEXISTS(args作为变量，ParamArray colmns()作为变量)作为布尔值Dim argsCount as Long，colmnsCount As Long尽可能长，只要长昏暗的match_candidate作为变体暗单元格范围发生错误时GoTo处理程序argsCount = UBound(args)-LBound(args)+ 1colmnsCount = UBound(colmns)-LBound(colmns)+ 1查看:MULTIMATCHEXISTS =假'在开始查询之前检查数组计数是否匹配，如果不抛出#value错误如果argsCount = colmnsCount那么错误时GoTo不存在:'检查是否满足最低要求如果argsCount = 1然后'如果仅给出一个参数，则找到第一个匹配项lRow = Application.WorksheetFunction.match(args，colmns(0)，0)MULTIMATCHEXISTS =真退出功能否则，如果argsCount>1然后'从colmns()数组中提供的第一列中获取所有值'其余的列无关紧要，因此我们不需要遍历它们，因为这是1:1的表搜索功能对于colmns中的每个单元格(0)如果UCase(args(1))= UCase(cell.value)然后'找到了一场比赛'将lRow设置为每个单元格的行号我不喜欢通过从HeaderRowRange中减去来获取ListObject单元格的行号，'有些人不使用表头导致错误的返回lRow = cell.Row-cell.ListObject.ListRows(1).Range.Row + 1对于i = 0到UBound(args)'在同一行的colmns()中获取每一列中的所有值match_candidate = Application.WorksheetFunction.index(colmns(i)，lRow，0)'检查所有值是否与各自的参数匹配如果args(i + 1)= match_candidate然后如果i + 1 = argsCount那么'所有值均与args相匹配；退出功能MULTIMATCHEXISTS =真退出功能万一别的'并非所有值都匹配，请转到下一个单元格迭代以检查是否有更多与args(1)匹配的单元格转到NextCell万一接下来我万一NextCell:下一个单元格万一别的转到处理程序万一处理程序:''处理错误如果Err.Number = 13，则错误清除如果不是IsEmpty(args)而不是IsEmpty(colmns(0))，则argsCount = 1colmnsCount = 1简历检查万一别的'肮脏的消息框1/0万一不存在:MULTIMATCHEXISTS =假退出功能结束功能 因此，基本上，我会进行动态的 INDEX/MATCH 验证并相应地进行处理.现在，我可以调用 = MULTIMATCHEXISTS ，只需将1个参数/列定义为undefined:
= MULTIMATCHEXISTS(CRITERIA(A2，A3，A4，A5，A6，A7)，Table2 [Column2]，Table2 [Column3]，Table2 [Column4]，Table2 [Column5]，Table2 [Column6]，Table2[Column7]) 其中1个参数是:
= MULTIMATCHEXISTS(A2，Table2 [Column5]) 尽管在这种情况下'multimatch'这个名字不太合适
如果您想用自己的2美分赚钱，我仍然很想看看别人会怎么想
I posted a question in regards to finding a match with multi-column criteria. The provided answer works great. But I'm trying to make it a universal solution for my project, in terms of how many columns criteria is used.
Here is the question I am referencing: Question & Answer I used
Here is what I've managed to come up with so far:
Public Function CRITERIA(ParamArray values() As Variant) As Variant
....
CRITERIA = values
End Function
Where the actual UDF referenced in the cells will be:
Public Function MULTIMATCHEXISTS(args As Variant, ParamArray colmns() As Variant) As Boolean
Dim argsCount As Long, colmnsCount As Long, cl As Long, a As Long
argsCount = UBound(args) - LBound(args) + 1
colmnsCount = UBound(colmns) - LBound(colmns) + 1
Dim tbl As ListObject
Dim ws As Worksheet
Dim lr As ListRow
Dim match_candidate As Variant, arg As Variant
If argsCount <> colmnsCount Then
....
Exit Function
Else
'Get the name of the table from any column provided (this of courses assumes a 1:1 table search)
Set tbl = colmns(0).ListObject
'Get tables worksheet from the table object
Set ws = ThisWorkbook.Sheets(tbl.Parent.Name)
'Iterate through columns?
For cl = LBound(colmns) To UBound(colmns)
'Get each value from column
For each lr In tbl.ListRows
match_candidate = Intersect(lr.Range, colmns(cl)).value
'Iterate through arguments?
For a = LBound(args) To UBound(args)
If match_candidate = args(a) Then
Debug.Print "its a match for " & args(a) & " in column " & colmns(cl)
MULTIMATCHEXISTS = True
Else
MULTIMATCHEXISTS = False
End If
Next a
Next lr
Next cl
End If
End Function
Where someone would use the UDF as follows:
=MULTIMATCHEXISTS(CRITERIA(A2,A3,A4), Table2[Column1], Table2[Column8], Table2[Column5])
Basically what I would like is for it to validate if the first value = it's respective queried column and so forth (I.e args(0) should = colmns(0) value, args(1) should = colmns(1) value)
So far, I can find matches using the above example, but I don't know how to check if ALL values match at the same time. Additionally I can't find any native functions to compare arrays on the MSDN site. It's an awkward site to navigate IMO.
Don't let my rep fool you. I'm new to VBA and will be the first to admit my newbiness, I'm having a hard time converting over. I don't find the MSDN documentation to be as helpful as other languages, personally. So if you can share any resources you use I would appreciate it.
In an effort to simplify my desired outcome:
Take table 1 that has a list of clients:
A B C D
-----------------------------------------------------------
1 | Name | Email | Phone | ISMATCH? |
-----------------------------------------------------------
2 | Steve Jobs | stevejobs@gmail.com | 123456 | True |
-----------------------------------------------------------
3 | Bill Gates | billgates@apple.com | 123456 | True |
-----------------------------------------------------------
4 | Steve Woz | stevewoz@outlook.com| 123456 | False |
-----------------------------------------------------------
Take table 2 that has a detailed description of those clients, but every client is queried by different arguments:
J K L M
-----------------------------------------------------------
1 | Name | Company | Phone | Email |
-----------------------------------------------------------
2 | Steve Jobs | Apple | 123456 | stevejobs@gmail.com |
-----------------------------------------------------------
3 | Bill Gates | Apple | 123456 | billgates@apple.com |
-----------------------------------------------------------
4 |Stevie Wonder | Apple | 123456 | steviewon@outlook.com |
-----------------------------------------------------------
What I would like is to be able to pick and choose which criteria to evaluate and then select their corresponding columns in Table 2. So back in Table 1 D2 it would be something like this:
=MULTIMATCHEXISTS(CRITERIA([@NAME], [@EMAIL]), Table2[Name], Table2[Email])
But lets say for bill gates I want to check more than those 2 criteria, so Table 1 D3 would be:
=MULTIMATCHEXISTS(CRITERIA([@NAME], [@PHONE], [@EMAIL]), Table2[Name], Table2[Phone], Table2[Email])
And for Steve Woz Table 1 D4:
=MULTIMATCHEXISTS([@Name], Table2[Name])
Those are practical examples of my UDF in action. Im trying to make both arguments dynamically flexible. I live off of named ranges, but it doesn't have to be specific to that
I found a solution that works for me and my needs; I played around with Charles' answer and couldn't quite figure out the structure based on his feedback. Although, I did take some of the information I learned from his feedback and applied it. Hopefully this can help someone else, as ugly or coarse it is. I think I was making it too hard on myself trying to visualize the loops within the loops within the loops. So I decided to settle for an Index/Match approach.
And more importantly, I really want to learn this language, so if you're a pro out there and spot something wrong I should focus on please let me know.
Public Function MULTIMATCHEXISTS(args As Variant, ParamArray colmns() As Variant) As Boolean
Dim argsCount As Long, colmnsCount As Long
Dim i As Long, lRow As Long
Dim match_candidate As Variant
Dim cell As Range
On Error GoTo Handler
argsCount = UBound(args) - LBound(args) + 1
colmnsCount = UBound(colmns) - LBound(colmns) + 1
check:
MULTIMATCHEXISTS = False
'Check if array counts match before even commencing a query, if not throw #value error
If argsCount = colmnsCount Then
On Error GoTo DoesNotExist:
'Check if minimum requirements are met
If argsCount = 1 Then
'If only 1 argument given find the first match
lRow = Application.WorksheetFunction.match(args, colmns(0), 0)
MULTIMATCHEXISTS = True
Exit Function
ElseIf argsCount > 1 Then
'Get all values from the first column provided in the colmns() array
'rest of the columns don't matter so we don't need to iterate through them because this is 1:1 Table search function
For Each cell In colmns(0)
If UCase(args(1)) = UCase(cell.value) Then
'Found a match
'Set the lRow to each cells row number
'I don't like getting the row number of a ListObject cell by substracting from HeaderRowRange,
'some people don't use table headers resulting in false returns
lRow = cell.Row - cell.ListObject.ListRows(1).Range.Row + 1
For i = 0 To UBound(args)
'Get all values in each column in colmns() within the same row
match_candidate = Application.WorksheetFunction.index(colmns(i), lRow, 0)
'Check if all values match their respective arguments
If args(i + 1) = match_candidate Then
If i + 1 = argsCount Then
'All values match args; exit function
MULTIMATCHEXISTS = True
Exit Function
End If
Else
'Not all values match, go to next cell iteration to check for more cells that match args(1)
GoTo NextCell
End If
Next i
End If
NextCell:
Next cell
End If
Else
GoTo Handler
End If
Handler:
''Handle Err
If Err.Number = 13 Then
Err.Clear
If Not IsEmpty(args) And Not IsEmpty(colmns(0)) Then
argsCount = 1
colmnsCount = 1
Resume check
End If
Else
'Dirty
MsgBox 1/0
End If
DoesNotExist:
MULTIMATCHEXISTS = False
Exit Function
End Function
So basically I do a dynamic INDEX/MATCH validation and process it accordingly. I can now call =MULTIMATCHEXISTS with as little as 1 argument/column to undefined:
=MULTIMATCHEXISTS(CRITERIA(A2,A3,A4,A5,A6,A7), Table2[Column2], Table2[Column3], Table2[Column4], Table2[Column5], Table2[Column6], Table2[Column7])
Where 1 argument is:
=MULTIMATCHEXISTS(A2, Table2[Column5])
Although the name 'multimatch' doesn't quite fit in that circumstance
I'm still interested to see what others come up with if you want to chime in with your 2 cents
这篇关于VBA UDF使用动态数组查找多列条件匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
