検索した隣のセルを取得するExcelマクロ

「エクセル2010 vba findメソッド 検索セルの隣を返す」
という検索で、このサイト・インストラクターのネタ帳へのアクセスがあることに気づきました。

RangeオブジェクトのFindメソッドを使ってセルを検索したときに、その隣のセルを表すRangeオブジェクトを取得するには、どのようなオブジェクト式を書けばいいのかを探している方による検索でしょうか。

指定した行・列シフトしたセルを取得するにはOffsetプロパティが便利

隣のセルに限らず、あるセルから、指定した行数や列数だけずれた位置にあるセルを取得するには、RangeオブジェクトのOffsetプロパティを使うのが便利です。

Offsetプロパティの第1引数には何行シフトするのか、第2引数には何列シフトするのか、整数で指定します。

• OffsetとResizeの動作を確認するExcelマクロ

例えば、
右隣のセルを取得するのなら、Offsetプロパティの第1引数に「0」を、第2引数に「1」を指定してください。
左隣のセルを取得するのなら、Offsetプロパティの第1引数に「0」を、第2引数に「-1」を指定してください。

具体例をあげましょう。

検索セルの隣のセルの値を取得するサンプルマクロ

「エクセル 検索した隣のセルの値を取得するマクロ」
「エクセル vba メッセージボックス 検索した隣のセルの内容を表示」
といった検索でも、このサイトへのアクセスがありますので、そんなマクロを作ってみました。

以下のようなマクロで、検索した隣のセルの値を取得してメッセージボックスに表示することができます。

B列から「北海道」という文字列を検索して、
　Set rng = Range("B:B").Find("北海道")

みつからなかった場合には「みつかりませんでした。」というメッセージが、
　If rng Is Nothing Then
　　MsgBox "みつかりませんでした。"

みつかった場合には右隣のセルの値がメッセージボックスに表示されます。
　Else
　　MsgBox rng.Offset(0, 1).Value

• VBAのRange.FindメソッドでNothing判定を行ってエラーを回避する

MsgBox関数の引数に指定された「rng.Offset(0, 1)」というオブジェクト式が、検索した右隣のセルを表すRangeオブジェクトを取得している部分で、そのRangeオブジェクトのValueプロパティを使って、検索したセルの右隣のセルの値を取得しています。

このマクロの、
　　MsgBox rng.Offset(0, 1).Value
の部分を、
　　MsgBox rng.Offset(0, -1).Value
にすれば、左隣のセルの値がメッセージボックスに表示されるようになります。

Range.Offsetプロパティの第2引数に「-1」に指定されているので、検索されたセルの「-1」隣すなわち左隣のセルを表すRangeオブジェクトを取得することになります。

検索セルの隣のセルに値を入力するサンプルマクロ

「vba 検索セルの隣に入力」
「エクセル vba 検索した隣に文字を入力」
といった検索でも、このサイトへのアクセスがありますので、そんなマクロも作ってみました。

以下のようなマクロにすれば、検索した隣のセルを取得して値を入力できます。

先のマクロと違うのは、みつかった場合には右隣のセルに「←ここだよ！」という文字列を入力する部分だけです。
　　rng.Offset(0, 1).Value = "←ここだよ！"

先のマクロと同じく「rng.Offset(0, 1)」というオブジェクト式が、検索した右隣のセルを表すRangeオブジェクトを取得している部分で、そのRangeオブジェクトのValueプロパティを使って、今度は検索したセルの右隣のセルに値を入力しています。