【VBA】ExcelマクロのFind(FindNext)による文字列検索

VBA
スポンサーリンク

VBAでExcelシート上にある特定のデータを検索する方法を解説します。

コピペの貼り付け先を設定するときに便利ですよ。

私はfor文すら知らないプログラミングのド素人からVBAとPythonを独学しました。

会社では通常業務に加えて作業自動化と機械学習をしています。

・VBAで特定の項目を見つけたい
・コピペを自動化したい
・必要な列だけを処理したい

こんな悩みを解決する内容です。

例えば”測定値A”の列をコピペするプログラムを組んだとします。

あるファイルには”測定値A”がA列に入っているのに、別のファイルではB列に入っているなんてことありますよね。

こんな時に項目名を自動で検索できると非常に便利です!!

Findで文字列を検索する

以下のデータの中から”測定値A”の位置を取得しましょう。

測定値が入っているデータ
Sub test()

target = Rows(1).Find("測定値A")
MsgBox target

End Sub

まずは検索したい範囲を指定します。今回は1行目なのでRows(1)ですね。

続いてFind(“検索文字”)で検索します。

1行目にある”測定値A”を見つけるコード

もし検索範囲内に指定の文字が存在すれば変数targetに検索した文字が入ります。

実行してみて”測定値A”が表示されればOKです。

でも本当に欲しいのは検索文字の行番号と列番号ですよね。

行番号or列番号を取得する方法

Sub test()

r = Rows(1).Find("測定値A").Row
c = Rows(1).Find("測定値A").Column
MsgBox r
MsgBox c

End Sub
Range().Find(“検索文字”).Row or .Column

行番号が欲しければ.Rowで、列番号なら.Columnとしてください。

RowとColumnの使い方

検索範囲を広げる方法

Sub test()

r = Range(Rows(1), Rows(2)).Find("測定値A").Row
c = Range(Rows(1), Rows(2)).Find("測定値A").Column
MsgBox r
MsgBox c

End Sub

上記コードは1,2行目の中から”測定値A”を探します。

Range(Rows(), Rows())で複数行を対象に指定可能です。

行単位で検索範囲を指定する図

例えば3~5行を対象にしたいならRange(Rows(3), Rows(5))ですね。

Cellsを使えばシート全体を指定できます。

Sub test()

r = Cells.Find("測定値A").Row
MsgBox r

End Sub

ただし、検索範囲を広げると不要なデータがヒットする可能性が上がります。

注意しましょう。

完全一致 or 部分一致にする方法

Sub test()

c = Rows(1).Find("測定値", LookAt:=xlPart).Column
MsgBox c

End Sub

引数LookAtを設定すると”完全一致”か”部分一致”かを選べます。

検索範囲.Find(検索文字, LookAt := xlWhole or xlPart)
 ・完全一致:xlWhole
 ・部分一致:xlPart

上記コードは部分一致にしているので”測定値”が含まれればヒットします。

またxlWholeにすると完全一致ですが、今回は”測定値”という項目がありません。

そのため実行すると下図のようなエラーになります。

検索した文字がない場合のエラー画面

複数のセルが条件に当てはまる場合

Sub test()

c = Rows(1).Find("測定値", LookAt:=xlPart).Column
MsgBox c

End Sub

複数のセルが条件に当てはまる場合は最も番号の若いセルがヒットします。

例えば上記コードの場合、”測定値”が含まれれば条件を満たします。

”測定値”が含まれる列が複数ある

つまり”測定値A,B,C”のどれでも当てはまります。

しかし実行すると2が表示されるはずです。つまり”測定値A”しかヒットしません。

Findは1つだけしか見つけてくれません。

FindNextで複数の文字を検索する

Findは1つしかセルを見つけられませんが、FindNextなら複数がヒットしても大丈夫です。

Sub test()

target1 = Rows(1).Find("測定値", LookAt:=xlPart).Column
target2 = Rows(1).FindNext(Rows(1).Find("測定値", LookAt:=xlPart)).Column

MsgBox target1
MsgBox target2

End Sub

まず変数target1ではさっきまでと同じくFindで1つ目の”測定値”を見つけます。

そしてFindNextの()に1行目で使ったFindをそのまま入れてください。

FindNextで2つ目のデータを見つけるコード

実行するとtarget1は2をtarget2は3を表示するはずです。

”測定値A”が1つ目、”測定値B”が2つ目の検索結果になっている

つまり”測定値”が含まれるセルを2つ見つけてくれたわけです。

Findを変数に格納する方法

このままだと同じコードを何度も書き続けることになるので、Setを使いましょう。

Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)

col1 = target.Column
col2 = Rows(1).FindNext(target).Column
MsgBox col1
MsgBox col2

End Sub
1つ目の結果を変数化する方法

Dimで変数targetがセルの位置であることを定義します。

Setで変数targetが最初に見つけたセルであると決めましょう。

するとFindNextにはtargetと入れるだけで済みます。

こうした方が後々楽になりますよ。

3つ以上のセルをヒットさせる方法

今回の例では”測定値”が3つあります。

”測定値”を含む列は3つある
Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)

col1 = target.Column
col2 = Rows(1).FindNext(target).Column
col3 = Rows(1).FindNext(target).Column
MsgBox col1
MsgBox col2
MsgBox col3

End Sub

このように.FindNextを2回書けば”測定値C”も見つかりそうですよね。

しかし実行してみるとcol3が4(D列)になりません。

targetは常に1個目のデータを指すので、col2もcol3も同じデータがヒットしているためです。

2つ目と3つ目の検索結果が同じになってしまう

つまりtargetを更新していく必要があります。

Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)

col1 = target.Column

col2 = Rows(1).FindNext(target).Column
Set target = Rows(1).FindNext(target)

col3 = Rows(1).FindNext(target).Column

MsgBox col1
MsgBox col2
MsgBox col3

End Sub

col2の段階でtargetを2個目に更新しました。

するとcol3が4になっているはずです。

2つ目の検索結果で変数を上書きする

このように1個ずつtargetを更新すれば3個目以降も見つけられます。

loopで複数のセルを検索する

Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)

i = 1
Do While i < 4
    MsgBox target.Column
    Set target = Rows(1).FindNext(target)
    i = i + 1
Loop

End Sub

同じ処理を何度も書くのはめんどくさいのでloop文で繰り返しました。

Do~Loopまでの処理が繰り返されます。

変数iは処理の実行回数をカウントするために用意しました。

3回で終わりなので条件はWhile i < 4です。

for文で検索と更新を繰り返す

loop文の中でtargetを更新しているので、実行されるたびに次の”測定値”を探してくれます。

実行して2,3,4が表示されればOKです。

FindNextの無限ループ

FindNextでは一通りすべてのデータを見つけた後に1個目のデータに戻ってきます。

Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)

i = 1
Do While i < 10
    MsgBox target.Column
    Set target = Rows(1).FindNext(target)
    i = i + 1
Loop

End Sub

さっきのloop文でi < 10に条件を変えました。

しかし”測定値”は3つしかありません。

”測定値”を含む列は3つしかない

なので4回目以降は何もしてほしくないのですが、実行してみると2,3,4,2,3,4…と何度も繰り返してしまうはずです。

このようにFindNextは無限ループします。

ループを解消するには以下のように書きましょう。

Sub test()

Dim target As Range
Set target = Rows(1).Find("測定値", LookAt:=xlPart)
Start = target.Address

Do
    MsgBox target.Column
    Set target = Rows(1).FindNext(target)
    If target.Address = Start Then
        Exit Do
    End If
Loop

End Sub

変数Startをつまり”測定値A”の位置として保存しました。

loop文の中でもしtargetの位置がStartと同じなら処理を終えるようにしましょう。

すると1周処理が終わってStartに戻ってくるタイミングでループが終わります。

1つ目のデータに戻ってきたらループを止めるコード

Find(FindNext)を使ったコピペプログラム

Findを使ってコピペする

以下2つのファイルがあると仮定します。

別ファイルから”測定値”を含む列をすべてコピーしたい

“Book1.xlsx”からそれぞれの測定項目をプログラム実行ファイルにコピーしましょう。

まずはSetでコピーするシートと貼り付けるシートを定義します。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

End Sub

これはしなくてもいいんですが。

後々ラクになります。

次に貼り付けたい項目名をそれぞれ取得しましょう。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

endCol = pasteWs.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To endCol
    MsgBox pasteWs.Cells(1, i)
Next i

End Sub

endColは処理対象にする最終列です。

Cells(1, Columns.Count)で末端の列(何万列目とか)に移動し、.End(xlToLeft)でCtrl+←を実行することで結果的に最終列になります。

詳しくはExcelマクロで最終行(列)を取得する方法をご覧ください。

今回はD列つまり4がendColです!

取得したい最後の列を定義する

“測定値A”が2列目にあるので、for文で2~endCol(4)まで処理を実行します。

それぞれの項目名をCellsで取り出して確認しましょう。

“測定値A”~”測定値C”が表示されればOK。

項目名が取り出せたのでFindで”Book1.xlsx”から該当セルの位置を検索しましょう。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

endCol = pasteWs.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To endCol
    target = pasteWs.Cells(1, i)
    MsgBox target
    targetCol = copyWs.Rows(1).Find(target).Column
    MsgBox targetCol
Next i

End Sub

項目名を変数targetとしてcopyWsの1行目からFindで見つけました。

見つかったらその列番号を.Columnで取り出して変数targetColに入れます。

それぞれ項目名と列番号をMsgBoxで表示してあってるかを確かめておいてください。

ここまでくれば後はコピペするだけです。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

endCol = pasteWs.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To endCol
    target = pasteWs.Cells(1, i)
    targetCol = copyWs.Rows(1).Find(target).Column
    copyWs.Columns(targetCol).Copy
    pasteWs.Columns(i).PasteSpecial
    Application.CutCopyMode = False
Next i

End Sub
プログラムを実行した結果

貼り付けたい列番号はfor文で使っているiで、コピーしたい列番号はtargetColです。

その点に注意してコピペしてください。

このようにFindを使って貼り付け先を自動取得できます。

コピペの方法については以下の記事で解説しています。

FindNextを使ってコピペする

例えば以下のファイルがあるとします。

サンプルAだけをコピペしたい

サンプルAだけのデータを集計しましょう。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

sample = "A"
Dim target As Range
Set target = copyWs.Columns(1).Find(sample)
Start = target.Address

End Sub

シートを変数に定義する部分はさっきと同じです。

今回集計したいサンプルAを変数sampleとしました。

最初にヒットする”A”の位置を変数targetとして定義しておきましょう。

後々ループを終わらせるための初期位置Startも作っておいてください。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

sample = "A"
Dim target As Range
Set target = copyWs.Columns(1).Find(sample)
Start = target.Address

Do
    MsgBox target.Row
    Set target = copyWs.Columns(1).FindNext(target)
    If target.Address = Start Then
        Exit Do
    End If
Loop

End Sub

loop文とFindNextで”A”の行番号を確認していきます。

2,5,8…と続けばOK。

後はコピペするだけですね。

Sub test()

Dim pasteWs As Worksheet
Dim copyWs As Worksheet
Set pasteWs = ThisWorkbook.Worksheets(1)
Set copyWs = Workbooks("Book1.xlsx").Worksheets(1)

sample = "A"
Dim target As Range
Set target = copyWs.Columns(1).Find(sample)
Start = target.Address

i = 2
Do
    targetRow = target.Row
    copyWs.Cells(targetRow, 2).Copy
    pasteWs.Cells(i, 1).PasteSpecial
    Application.CutCopyMode = False
    i = i + 1
    Set target = copyWs.Columns(1).FindNext(target)
    If target.Address = Start Then
        Exit Do
    End If
Loop

End Sub

変数iは貼り付け先の行番号です。最初は2で次は3…と増えていきます。

targetRowをコピー先の”A”の行番号として、その隣にあるB列のデータをコピーしましょう。

貼り付け先はCells(i, 1)で大丈夫です。

貼り付けたら1個下の行に移りたいのでiに1を足してくださいね。

プログラムの実行結果

この通り”A”のデータだけを抽出することができました。

まとめ

今回はFindとFindNextを解説しました。

Find       :1つのデータのみを見つける
FindNext:2つ以上のデータを見つける

コピペのプログラムと非常に相性が良いのでぜひ使ってみてください!!

VBA
スポンサーリンク
HTOMblog

コメント

タイトルとURLをコピーしました