vba 在excel vba中搜索多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21091948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Searching over multiple columns in excel vba
提问by Null Reference
I am able to search a text in column A of my spreadsheet by using this
我可以使用这个在我的电子表格的 A 列中搜索文本
With WB.Sheets("MySheet")
Set FindRow = .Range("A:A").Find(What:="ProjTemp1", LookIn:=xlValues)
End With
After which I can get the row number by doing FindRow.Row
之后我可以通过执行获得行号 FindRow.Row
How do I then get back the row number where Column A == "ProjTemp1" && Column B == "ProjTemp2" && Column C == "ProjTemp3"
然后我如何取回行号 Column A == "ProjTemp1" && Column B == "ProjTemp2" && Column C == "ProjTemp3"
回答by Dmitry Pavliv
Try to use Autofilter:
尝试使用自动过滤器：
Dim rng As Range
'disable autofilter in case it's already enabled'
WB.Sheets("MySheet").AutoFilterMode = False
With WB.Sheets("MySheet").Range("A1:C1")
'set autofilter'
.AutoFilter Field:=1, Criteria1:="=ProjTemp1"
.AutoFilter Field:=2, Criteria1:="=ProjTemp2"
.AutoFilter Field:=3, Criteria1:="=ProjTemp3"
End With
With WB.Sheets("MySheet")
On Error Resume Next
Set rng = .Range("A2:A" & .Rows.Count).Rows.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If Not rng Is Nothing Then
MsgBox rng.Row ' returns first visible row number
End If
WB.Sheets("MySheet").AutoFilterMode = False 'disable autofilter'
回答by Netloh
An alternative suggestion is to just loop through the table and use nested if-statements like this:
另一种建议是循环遍历表并使用嵌套的 if 语句，如下所示：
Sub ReturnRowNumber()
Dim i As Long, GetRow As Long
For i = 2 To Sheets("MySheet").Cells(Rows.Count, 1).End(xlUp).Row
'Criteria search
If Sheets("MySheet").Cells(i, 1).Value = "ProjTemp1" Then
If Sheets("MySheet").Cells(i, 2).Value = "ProjTemp2" Then
If Sheets("MySheet").Cells(i, 3).Value = "ProjTemp3" Then
'Returns row
GetRow = i
End If
End If
End If
Next i
End Sub
回答by Михаил Василенко
Just posted similar reply at MSDN and wanted to share here if anyone is still using VBA. The function for multiple match that works pretty fast.
刚刚在 MSDN 上发布了类似的回复，如果有人仍在使用 VBA，想在这里分享。多重匹配的功能运行速度非常快。
It might help a lot if you are interested in effective code since using Application.Match() is much much faster that Find() or INDEX() method or simple looping.
如果您对有效代码感兴趣，这可能会很有帮助，因为使用 Application.Match() 比使用 Find() 或 INDEX() 方法或简单循环要快得多。
The syntax is the same as COUNTIFS() but it returns the match index instead of counting.
语法与 COUNTIFS() 相同，但它返回匹配索引而不是计数。
Public Function MultiMatch(ParamArray X0() As Variant) As Variant
MultiMatch = CVErr(xlErrNA)
If UBound(X0) = -1 Then Exit Function
On Error GoTo ErrorHandler
Set Xws = X0(1).Parent
X_rFrow = X0(1)(1, 1).Row
X_rLrow = X_rFrow + X0(1).Rows.Count - 1
jLAST = UBound(X0)
l = X_rFrow
j = 0
Do While IsError(MultiMatch) And j + 1 <= jLAST And Not IsError(X1)
jCOL = X0(j + 1).Column
Set TRNG = Xws.Range(Xws.Cells(l, jCOL), Xws.Cells(X_rLrow, jCOL))
X1 = Application.Match(X0(j), TRNG, 0)
If Not IsError(X1) Then
l = TRNG(X1).Row
If X1 = 1 Then
If j + 1 = jLAST Then
MultiMatch = l - X_rFrow + 1
Else
j = j + 2
End If
Else
j = 0
End If
End If
Loop
Exit Function
ErrorHandler:
MultiMatch = CVErr(xlErrName)
End Function
