Geek asks and answers

Find row from Excel spreadsheet using VBA

In Excel, I use tables to store dynamic data inside a separate worksheet. However, entering new data works like a charm, however I would like to be able to dynamically extract one row from this table and store its data in variables. I would prefer to build a function so that I can do something like this:

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

Note that the table is dynamic, not a fixed area (therefore, it changes vertically). I want to reuse this function with minor changes in other tables. I just need an example of how to achieve this in VBA.

Thank,

+3
source share
1 answer

, 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
+8

More articles:


All Articles