Cells × Range × Offset × Resize × CurrentRegion × SpecialCells × Find を組み合わせると、表全体を動的に扱いながら、特定セルを検索・抽出・加工することができます。
実務で役立つ応用テンプレをまとめました。
基本的な Find の使い方
- 表全体から特定値を検索
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find(What:="東京都")
If Not f Is Nothing Then f.Interior.Color = vbYellow
- 最初の一致セルを取得
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("神奈川県")
If Not f Is Nothing Then MsgBox "見つかったセル: " & f.Address
Find × Offset
- 検索結果の右隣セルに値を入れる
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("渋谷区")
If Not f Is Nothing Then f.Offset(0, 1).Value = "隣接セル処理"
- 検索結果の下のセルを強調
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("新宿区")
If Not f Is Nothing Then f.Offset(1, 0).Interior.Color = vbGreen
Find × Resize
- 検索結果から範囲拡張
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("横浜市")
If Not f Is Nothing Then f.Resize(2, 3).Interior.Color = vbCyan
- 検索結果から行全体を選択
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("千代田区")
If Not f Is Nothing Then f.EntireRow.Interior.Color = vbRed
Find × SpecialCells
- 検索結果範囲内の空白セルを補完
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("港区")
If Not f Is Nothing Then
On Error Resume Next
f.Resize(1, 5).SpecialCells(xlCellTypeBlanks).Value = "未入力"
On Error GoTo 0
End If
- 検索結果範囲内の数式セルを強調
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("江戸川区")
If Not f Is Nothing Then
On Error Resume Next
f.Resize(3, 3).SpecialCells(xlCellTypeFormulas).Font.Bold = True
On Error GoTo 0
End If
Find × CurrentRegion 応用
- 表全体で複数一致をループ処理
Dim tbl As Range, f As Range, firstAddr As String
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("東京都")
If Not f Is Nothing Then
firstAddr = f.Address
Do
f.Interior.Color = vbYellow
Set f = tbl.FindNext(f)
Loop While Not f Is Nothing And f.Address <> firstAddr
End If
- 検索結果を右隣の表にコピー
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("大阪市")
If Not f Is Nothing Then
f.Resize(1, 3).Copy Destination:=tbl.Offset(0, tbl.Columns.Count + 1).Cells(1, 1)
End If
Find × Cells 応用
- 検索結果の行番号・列番号を取得
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("札幌市")
If Not f Is Nothing Then
MsgBox "行: " & f.Row & " 列: " & f.Column
End If
- 検索結果のセルを動的に参照
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("名古屋市")
If Not f Is Nothing Then
Cells(f.Row, f.Column + 1).Value = "隣接セル更新"
End If
高度な応用
- 検索結果を右隣に集計列として追加
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("福岡市")
If Not f Is Nothing Then
f.Offset(0, tbl.Columns.Count).Value = "集計対象"
End If
- 検索結果を下に合計行として追加
Dim tbl As Range, f As Range
Set tbl = Range("A1").CurrentRegion
Set f = tbl.Find("京都市")
If Not f Is Nothing Then
tbl.Offset(tbl.Rows.Count, 0).Resize(1, tbl.Columns.Count).Value = "合計行"
End If
- 検索結果範囲を拡張してコピー
Dim f As Range
Set f = Range("A1").CurrentRegion.Find("仙台市")
If Not f Is Nothing Then
f.Resize(2, 2).Copy Destination:=f.Offset(0, 5)
End If
実務での使い分けポイント
- 表全体を扱う → CurrentRegion
- 検索 → Find / FindNext
- 隣接セル操作 → Offset
- 範囲拡張 → Resize
- 特定セル抽出 → SpecialCells (Blanks, Constants, Formulas)
- セル座標取得 → Cells(Row, Col)
💡 この「Cells × Range × Offset × Resize × CurrentRegion × SpecialCells × Find」テンプレを押さえておけば、表全体の動的検索・抽出・加工・コピーまで自在に扱えます。
