[Excelマクロ]よく使う処理–テーブル検索–

プロシージャとcall後の操作のセットでよく使う処理について、メモとして残しておこうかと思います。

テーブル検索 レコードを取得

あるシート（”sheet1″）に以下のようなテーブルがあるとして、key=”key03″となるレコードを取得し配列”hitData”に格納する。

テーブルを2次元配列に格納する

テーブルを2次元配列に格納するプロシージャgetTableInfo()を作り、メイン処理で呼び出せるようにしておく。

' ----------グローバル変数----------'
Dim tableWs As Worksheet
Dim tableDataRange As Range
Dim tableDataArray

' 【関数】「sheet1」シートに設定したテーブルを取得し2次元配列に格納する'
' tableDataArray(i, 1)=項番'
' tableDataArray(i, 2)=key'
' tableDataArray(i, 3)=value'
' tableDataArray(i, 4)=説明'
'  i=1はヘッダ行'
Sub getTableInfo()
    
    ' シートを取得'
    Set tableWs = ActiveWorkbook.Worksheets("Sheet1")
    
    ' テーブルを取得し配列に格納する'
    Set tableDataRange = tableWs.Range("A1").CurrentRegion
    tableDataArray = tableDataRange
    
    Debug.Print tableDataRange.Address
    
End Sub

2次元配列から1行のデータを取得する

メイン処理でgetTableInfo()を呼び出しテーブルを2次元配列に格納した後、For文で2次元配列を順番に検索する。
検索にヒットした行数を引数にWorksheetFunction.Index()を呼び出すことで、2次元配列から1行のデータを取り出す。

Sub main01()

    Call getTableInfo

    ' key=key03である行を取得しhitDataに保持する'
    For i = 2 To UBound(tableDataArray)
        If (tableDataArray(i, 2) = "key03") Then
            hitData = WorksheetFunction.Index(tableDataArray, i)
            Exit For
        End If
    Next i
    
    ' key=key03のvalueを出力'
    Debug.Print hitData(3)
End Sub

テーブル検索 行番号を取得

行番号を取得したいだけであればFor文を回さずに、Find()メソッドで取得したセルの.Rowと取ればよい。

Sub main02()

    Call getTableInfo

    ' key=key03であるセルを取得しresultに保持する'
    Set result = tableDataRange.Find("key03", LookAt:=xlPart)
    
    If Not result Is Nothing Then
        ' key=key03の行番号を出力'
        Debug.Print result.Row
    End If

End Sub