VBAを使う中で文字や数値を検索し、その項目に当てはまる行数・行番号と列数・列番号を知りたいと
思ったことはないでしょうか。
今回は条件に当てはまる文字を検索する方法と
その検索結果をもとに行数と列数を割り出す方法について説明していきたいと思います。
当てはまる項目へ数値や文字を入力することもできるようになりますね。
それでは見ていきましょう。
■検索方法について
検索する際に使用するのが「Find」です。
英語の意味通りですね。
仕組みについてはこのようになります。
Set 変数 =検索範囲.Find(検索ワード,検索方法)
変数
こちらはデータが存在した際にセルの位置を格納するので
Rangeのオブジェクトとなります
例として
Dim FindCell As Range
とすると検索で当てはまれば「FindCell」にセルの位置が格納されるイメージです。
検索範囲
こちらは調査したい範囲を入力します。
例えばB2からM2の範囲で検索したい場合は
Range(“B2:M2”)
または
Range(CELLS(2,2),CELLS(2,13))
となります。
全セルで検索したい場合は「Cells」で構いません。
検索ワード
こちらには検索したい文字・数値を入れます。
実際に文字・数値を打ち込んでもいいですし
変数に格納されている文字・数値で検索しても、かまいません。
検索方法
こちらはどのように検索するか選択するオプションのようなものです。
方法については下記にまとめました。
| 項目 | 引数 | 定数 | 検索方法の内容 |
|---|---|---|---|
| 開始位置 | After | – | 検索開始位置 |
| 検索の対象 | LookIn | xlFormulas | 数式 |
| xlValues | 値 | ||
| xlComents | コメント | ||
| 検索の制約 | ＬookAt | xlPart | 部分一致 |
| xlWhole | 全一致 | ||
| 検索の方向 | SearchOrder | xlByRows | 列 |
| xlByColumns | 行 | ||
| 検索順 | SearchDirection | xlNext | 正方向 |
| xlPrevious | 逆方向 | ||
| 大・小文字を区別 | MatchCase | TRUE | 大・小文字を区別 |
| FALSE | 区別しない | ||
| 全・半角を区別 | MatchByte | TRUE | 全・半角を区別 |
| FALSE | 区別しない | ||
| 書式の検索 | SearchFormat | TRUE | 検索する |
| FALSE | しない |
こちらはすべて省略可能になっています。
では見本を見てみましょう
見本：
Set FindCell =Range(“B2:M2”).Find(“111”, After, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False, SearchFormat:=False)
内訳：
B2からM2の範囲で”111″を検索対象を値で、全一致で検索して、列・逆方向に検索し、大・小文字と全角・半角は区別せず書式を含めない条件で当てはまったものを「FindCell」に格納
となります。
■文字・数値を検索する
まず、検索する方法について学んでいきましょう。
下記のように月が並んでいるデータを用意しました。例で「4月」を検索してみましょう。
では式に当てはめていきましょう。
Set 変数 =検索範囲.Find(検索ワード,検索方法)
変数名は何でもいいですが「FindCell」としましょう
検索範囲は1月から12月なので「B2:M2」となります。
検索ワードは「4月」です。
検索方法は4月で固定したいのでLookAt:=xlWholeを宣言します。
当てはめるとこうなります。
Set FindCell = Range(“B2:M2”).Find(“4月”, LookAt:=xlWhole)
こちらを使い、仮にプログラムを作成しました。
Sub 検索する()
Dim FindCell As Range
Set FindCell = Range(“B2:M2”).Find(“4月”, LookAt:=xlWhole)
End Sub
実際にプログラムを実行してみましょう。
Set FindCell = Range(“B2:M2”).Find(“4月”, LookAt:=xlWhole)を読み込む前は
「FindCell」は「Nothing」だったのですが
読み込むと…
「FindCell」に「4月」が入りました。
こちらは検索範囲にデータがあったため読み込んでいることになります。
データがなければそのまま「Nothing」のとなります。
では検索できたのでこちらの知識を応用してみます。
■検索して当てはまったデータの行数と列数を変数に格納する
では応用です。
このように月をＣ2で選択して、E2に打ち込んだ数値・文字を選択した月の1つ下のセルに打ち込んでみましょう。
検索の方は先ほどの流れと同様にこのようになります。
Set FindCell = Range(“B4:M4”).Find(Cells(2,3), LookAt:=xlWhole)
ここまででできることはFindCellに検索結果の位置が把握できることです。
そこで行数と列数を変数に記録する際は下記の仕組みを利用します。
行数 変数 = 検索した変数.Row
列数 変数 = 検索した変数.Column
今回は求めたい行数を「SarchRow」、列数を「SarchColumn」と変数宣言します
となるとプログラムはこのようになります。
Sub 検索する()
Dim FindCell As Range
Dim SarchRow As Long, SarchColumn As LongSet FindCell = Range(“B4:M4”).Find(Cells(2, 3), LookAt:=xlWhole)
SarchRow = FindCell.Row
SarchColumn = FindCell.ColumnMsgBox Cells(2, 3) & “は” & SarchRow & “行目の” & SarchColumn & “列目です”
End Sub
実行すると・・・
行数と列数の検索結果もあってますね。
では入力してみましょう。
■検索した行数と列数を活用してみる
では選択した月から検索した行数と列数を元に数値・文字を入力してみましょう。
現在は5月を選択しており、入力する文字は「123件」です。
SarchRow = FindCell.Row
SarchColumn = FindCell.Column
により5月の位置はわかっているので
Cells(SarchRow + 1, SarchColumn) = Cells(2, 5)
となります。
先ほどのプログラムに組み込みましょう。
Sub 検索する()
Dim FindCell As Range
Dim SarchRow As Long, SarchColumn As LongSet FindCell = Range(“B4:M4”).Find(Cells(2, 3), LookAt:=xlWhole)
SarchRow = FindCell.Row
SarchColumn = FindCell.ColumnCells(SarchRow + 1, SarchColumn) = Cells(2, 5)
End Sub
では実行しましょう。
ちゃんと5月の位置に「123件」と入ってますね。
■検索エラー時の対応
Findを使用する中で検索して対象がなかった場合、エラーになっています。
先ほどの検索で、検索ワードを「13月」で実行してみましょう。
13月は存在しないのでエラーが出ました。
このような検索時のエラー対応について説明します。
Findの仕組みは復習にはなりますが下記の通りです。
Set 変数 =検索範囲.Find(検索ワード,検索方法)
ここで検索して該当がなければ変数は「Nothing」になります。
この仕組みとIFを合わせることで対応ができます。
そのプログラムがこちら。
If 変数 Is Nothing Then
MsgBox “該当データはありません”
Exit Sub
End If
仕組みとして変数に該当がなかったらNothingなのでIFで
変数がNothingだったときの分岐処理を行います。
処理方法としてメッセージボックスで「該当データはありません」と表示したのち
Exit Subにてプログラムを終了する仕組みです。
では先ほどにプログラムに組み込みましょう。
エラーの分岐はSetで変数に検索結果を格納した後です。
Sub 検索する()
Dim FindCell As Range
Dim SarchRow As Long, SarchColumn As LongSet FindCell = Range(“B4:M4”).Find(“13月”, LookAt:=xlWhole)
If FindCell Is Nothing Then
MsgBox “該当データはありません”
Exit Sub
End IfSarchRow = FindCell.Row
SarchColumn = FindCell.ColumnCells(SarchRow + 1, SarchColumn) = Cells(2, 5)
End Sub
こちらで実行すると・・・
エラーにも対応できるプログラムにできましたね。
⬛︎公式
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください☟
Range.Find メソッド (Excel) | Microsoft Learn
■まとめ
いかがでしょうか。
Findが活用できれば該当セルへの打ち込みが可能になったり
自動化でできることが増えます。
ぜひ参考にしてみてください。
それでは次回の記事でお会いしましょう。記事はこちらから☟
【VBA知識 15】行の挿入・削除/列の挿入・削除を様々なパターン・方向で行う ► 独学エクセル塾 (dokugakuexcel.com)