使用VBA从Excel表中查找一行 [英] Find a row from Excel table using VBA

问题描述

在Excel中，我使用表将动态数据存储在一个单独的工作表中。输入新数据的作用就像一个魅力，但是，我想能够从该表中动态地检索一行，并将其数据存储在变量中。我想建立一个函数，以便我可以这样做：

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:

findFromCatsByDate(searchterm) 'returns a single row if found with mathing date.

请注意，表格是动态的，不是固定的范围（因此它会垂直变化）。我想重复使用这个功能对其他表略微修改。我需要一个例子，如何在VBA中实现这一点。

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.

谢谢，

推荐答案

这将返回与指定表格中 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

示例使用

Sub zx()
    Dim r As Range
    Set r = GetRow("MyTable", 1, 2)
    If Not r Is Nothing Then
        r.Select
    End If
End Sub

这篇关于使用VBA从Excel表中查找一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！