Excel vba - 查找列数据所在的行号（多个子句）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18262403/
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
Excel vba - find row number where colum data (multiple clauses)
提问by asterix55
I need a function in VBA that finds the row number based on 2 where clauses.
我需要 VBA 中的一个函数，它根据 2 个 where 子句查找行号。
Here is the Excel sample:
这是 Excel 示例：
** A B C D**
1 id1 day1 val1 xxx
2 id2 day1 val2 xxx
3 id3 day1 val3 xxx
4 id1 day2 val1 xxx
5 id2 day2 val2 xxx
6 id3 day2 val3 xxx
I need to find the row number (in this case row number is 2) where B = "day1" and A = "id2".
我需要找到行号（在这种情况下行号是2），其中 B = "day1" 和 A = "id2"。
Based on the row number, I need to further get the values of other columns, i.e. C2, D2
根据行号，我需要进一步获取其他列的值，即C2，D2
Hope that the question is clear.
希望问题很清楚。
Thank you!
谢谢！
回答by tigeravatar
With your data setup like that, you can use the MATCH function to get the row number:
通过这样的数据设置，您可以使用 MATCH 函数来获取行号：
=MATCH(1,INDEX(($A:$A="id2")*($B:$B="day1"),),0)
If there are no matches for those criteria, the formula will return an #N/A error. You can also change the criteria to be cell references, for example:
如果这些条件没有匹配项，则公式将返回 #N/A 错误。您还可以将条件更改为单元格引用，例如：
=MATCH(1,INDEX(($A:$A=F1)*($B:$B=G1),),0)
For the second part of your question, returning values with the found row number, you can use the INDEX function to return a value from a column. So pretending the Match formula is in cell H1, these two formulas will return the value from column C and D respectively:
对于问题的第二部分，使用找到的行号返回值，您可以使用 INDEX 函数从列中返回一个值。因此，假设匹配公式在单元格 H1 中，这两个公式将分别返回 C 列和 D 列的值：
=INDEX($C:$C,H1)
=INDEX($D:$D,H1)
Alternately, you could put it all into a single formula:
或者，您可以将其全部放入一个公式中：
=INDEX($C:$C,MATCH(1,INDEX(($A:$A=F1)*($B:$B=G1),),0))
And if you don't want to be looking at errors, you can use an IFERROR on excel 2007+
如果您不想查看错误，可以在 excel 2007+ 上使用 IFERROR
=IFERROR(INDEX($C:$C,MATCH(1,INDEX(($A:$A=F1)*($B:$B=G1),),0)),"No Matches")
Error checking for Excel 2003 and below:
Excel 2003 及以下版本的错误检查：
=IF(ISNA(MATCH(1,INDEX(($A:$A=F1)*($B:$B=G1),),0)),"No Matches",INDEX($C:$C,MATCH(1,INDEX(($A:$A=F1)*($B:$B=G1),),0)))
[EDIT]: I am including a VBA solution per user request. This uses a find loop, which is very efficient and flexible, and shows how to extract values from other columns once a match has been found:
[编辑]：我根据用户请求包含了一个 VBA 解决方案。这使用了一个非常有效和灵活的查找循环，并展示了如何在找到匹配项后从其他列中提取值：
Sub tgr()
Dim rngFound As Range
Dim strFirst As String
Dim strID As String
Dim strDay As String
strID = "id2"
strDay = "day1"
Set rngFound = Columns("A").Find(strID, Cells(Rows.Count, "A"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
Do
If LCase(Cells(rngFound.Row, "B").Text) = LCase(strDay) Then
'Found a match
MsgBox "Found a match at: " & rngFound.Row & Chr(10) & _
"Value in column C: " & Cells(rngFound.Row, "C").Text & Chr(10) & _
"Value in column D: " & Cells(rngFound.Row, "D").Text
End If
Set rngFound = Columns("A").Find(strID, rngFound, xlValues, xlWhole)
Loop While rngFound.Address <> strFirst
End If
Set rngFound = Nothing
End Sub
回答by Ted
In VBA you can do a brute force check like the following:
在 VBA 中，您可以进行如下的蛮力检查：
Public Sub Test()
Dim message As String
Dim row As Long
row = Find("id1", "day2")
message = "Not Found"
If (row > 0) Then
message = ActiveSheet.Cells(row, 3).Value
End If
MsgBox message
End Sub
Function Find(aVal As String, bVal As String) As Long
Dim maxRow As Long
Dim row As Long
maxRow = Range("A65536").End(xlUp).row
For row = 2 To maxRow
Dim a As String
a = ActiveSheet.Cells(row, 1).Value
b = ActiveSheet.Cells(row, 2).Value
If a = aVal And b = bVal Then
Find = row
Exit Function
End If
Next row
Find = -1
End Function
