使用 VBA 从 Excel 表中查找一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6249039/
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
Find a row from Excel table using VBA
提问by Shyam
Within Excel, I use tables to store dynamic data inside a seperate worksheet. Entering new data works like a charm, however, I would like to be able to dynamically retrieve a single row from that table and store its data in variables. I would prefer to build a function so I could do something like this:
在 Excel 中，我使用表格将动态数据存储在单独的工作表中。输入新数据就像一种魅力，但是，我希望能够从该表中动态检索一行并将其数据存储在变量中。我更愿意构建一个函数，这样我就可以做这样的事情：
findFromCatsByDate(searchterm) 'returns a single row if found with mathing date.
Note that the table is dynamic and not a fixed range (so it changes vertically). I want to reuse this function with slight modification on other tables. I kind of need an example how to achieve this in VBA.
请注意，该表是动态的，而不是固定范围（因此它会垂直变化）。我想在其他表上稍加修改后重用此函数。我需要一个如何在 VBA 中实现这一点的示例。
Thanks,
谢谢，
回答by chris neilsen
This will return a reference the row that matches Keyin a specified table
这将返回与Key指定表中匹配的行的引用
Function GetRow(TableName As String, ColumnNum As Long, Key As Variant) As Range
On Error Resume Next
Set GetRow = Range(TableName) _
.Rows(WorksheetFunction.Match(Key, Range(TableName).Columns(ColumnNum), 0))
If Err.Number <> 0 Then
Err.Clear
Set GetRow = Nothing
End If
End Function
Example use
示例使用
Sub zx()
Dim r As Range
Set r = GetRow("MyTable", 1, 2)
If Not r Is Nothing Then
r.Select
End If
End Sub
