【EXCEL VBA】検索キーを中心に上下左右の値を取得するサンプル

Excel VBAで特定の検索キー（項目名など）を指定し、そのセルを中心に上下左右の相対位置にあるセルの値を取得するサンプルコードを紹介します。

この記事でできること

ワークシート上の指定範囲から文字列（検索キー）を検索し、見つかったセルを基準として行・列のオフセットを指定し、周囲のセルの値を取得・確認することができます。

サンプルコード

以下のVBAコードを標準モジュールに貼り付けて実行してください。

Sub test()
    Dim r As Range
    Set r = Range("C2:G6")
    
    ' ラベル項目と、そのラベル項目からみたオフセット位置を指定する。
    ' （プラス値：下または右にずらす）
    ' （マイナス値：上または左にずらす）
    Call GetTargetParameter(r, "項目", 0, -1)
    Call GetTargetParameter(r, "項目", 0, 0)
    Call GetTargetParameter(r, "項目", 0, 1)
End Sub

' ある指定の範囲からラベルをキーにデータを取得する。
Function GetTargetParameter(ByRef serchArea As Range, ByVal targetLabel As String, ByVal RowOffset As Integer, ByVal ColumnOffset As Integer) As String
    Dim r As Range
    
    GetTargetParameter = "0"
    For Each r In serchArea
        If r.Value = targetLabel Then
            ' オフセット回、セルのアドレスを再設定（起点をずらす）
            Call setOffsetRow(r, RowOffset)
            Call setOffsetColumn(r, ColumnOffset)
            Debug.Print "address=" & r.Address & " value=" & r.Value
            GetTargetParameter = r.Value
            Exit For
        End If
    Next r
End Function

Sub setOffsetRow(ByRef r As Range, ByVal RowOffset As Integer)
    Dim i As Integer
    If RowOffset = 0 Then
        Exit Sub
    ElseIf RowOffset < 0 Then
        For i = 1 To Abs(RowOffset)
            Set r = r.Offset(-1, 0)
        Next i
    Else
        For i = 1 To RowOffset
            Set r = r.Offset(1, 0)
        Next i
    End If
End Sub

Sub setOffsetColumn(ByRef r As Range, ByVal ColumnOffset As Integer)
    Dim i As Integer
    If ColumnOffset = 0 Then
        Exit Sub
    ElseIf ColumnOffset < 0 Then
        For i = 1 To Abs(ColumnOffset)
            Set r = r.Offset(0, -1)
        Next i
    Else
        For i = 1 To ColumnOffset
            Set r = r.Offset(0, 1)
        Next i
    End If
End Sub

使い方と注意点

シート上のレイアウトに合わせて検索範囲やオフセット値を調整してください。イミディエイトウィンドウに取得したセルのアドレスと値が出力されます。