スポンサーリンク

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

VBA
2020.05.03

Findを使って特定の文字列が入っているセルを見つけることができます。

Findで文字を見つけるコード

別のファイルから特定の項目を見つけて、さらに別のファイルにコピペする際に便利です。

Findで文字列を検索する

Findの使い方

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

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

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

End Sub

検索範囲を指定してから、Find(“検索文字”)と書きます。

Findで文字を見つけるコード

もし検索範囲内に指定の文字が存在すれば、変数targetに検索した文字が入ります。
実行してみて”測定値A”が表示されれば成功です。

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

Sub test()

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

End Sub

行番号が欲しければ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

上記コードは１,２行目の中から”測定値A”を探します。
Range(Rows(), Rows())で複数行を対象に指定可能です。

1,2行を示した例

例えば３～５行を対象にしたいなら、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にすると完全一致ですが、今回は”測定値”という項目がありません。
そのため実行すると下図のようなエラーになります。

xlWholeでエラーになる例

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

Sub test()

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

End Sub

複数のセルが条件に当てはまる場合は最も番号の若いセルがヒットします。
例えば上記コードの場合、”測定値”が含まれれば条件を満たします。

項目名に測定値が入っている図

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

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

すべての”測定値”が欲しい場合はFindNextを使いましょう。

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

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で１つ目の”測定値”を見つけます。
そしてFindNextの()に１行目で使ったFindをそのまま入れてください。

FindNextで２つまでデータを見つけるコード

実行するとtarget1は２をtarget2は３を表示するはずです。

FindNextで測定値ABを見つける図

このようにFindNextなら複数ヒットしたセルを順番に取り出すことができます。

Findを変数に格納する方法

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

同じコードを何度も書き続けると面倒なので、Setを使ってまとめましょう。

FindNextでtargetを変数化した例

Dimで変数targetがセルの位置であることを定義します。
Setで変数targetが最初に見つけたセルであると決めましょう。
するとFindNextにはtargetと入れるだけで済みます。

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

項目名に測定値が入っている図

“測定値A” ~ “測定値C”の３つを取得してみましょう。

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
FindNextで３つデータを見つけるコード

“測定値A” ~ “測定値Cの列番号を”col1” ~ “col3″として取得しました。
検索対象が見つかったら、それを変数targetとし、さらに次を探していきます。

ちなみに、下記コードのように更新を忘れると失敗します。

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

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

FindNextで３つ目のファイルを間違って見つけた例

なので、検索対象が見つかったら必ず更新するようにしましょう。

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までの処理が繰り返されます。

LoopでFindNextの対象を更新する例

３回で終わりなので条件はWhile i < 4です。
loop文の中でtargetを更新しているので、実行されるたびに次の”測定値”を探してくれます。

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

FindNextでは、一通りすべてのデータを見つけた後に１個目のデータに戻ってきます。
ここで、変数Startをつまり”測定値A”の位置として保存しましょう。
そして、loop文の中でもしtargetの位置がStartと同じなら、処理を終えるようにします。

LoopでFindNextを最後まで実行するコード

すると、１周処理が終わってStartに戻ってくるタイミングでループを終えてくれます。

使用例：Findを使ってコピペする

状況例

Findで測定値ABCをコピーしたい図

このように２つのファイルがあると仮定します。
“Book1.xlsx”からそれぞれの測定項目をプログラム実行ファイルにコピーしましょう。

シートを定義する

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はデータが入っている最終列です。

測定値ABCを見つけたい図

【関連記事】Excelマクロで最終行(列)を取得する方法

“測定値A”が２列目にあるので、for文で２～endCol(4)まで処理を実行します。
それぞれの項目名をCellsで取り出して確認しましょう。
“測定値A”～”測定値C”が表示されればOKです。

貼り付け先を見つける

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

項目名が取り出せたので、Findで”Book1.xlsx”にある貼り付け先を見つけましょう。
見つかったらその列番号をColumnで取り出して、変数targetColに入れます。
ここまでくれば、あとはコピペするだけです。

コピペする

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を使ってABCがコピーできた後の図

うまくいったら、各項目にコピペができていると思います。
【関連記事】Excelマクロでコピペをする方法

使用例：FindNextを使ってコピペする

状況例

FindでSampleAをコピーしたい図

このように、別ファイルにあるサンプル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”として定義しておきましょう。
あとでloop文を終わらせるために、最初の位置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は貼り付け先の行番号です。最初は２で次は３…と増えていきます。
targetRowをコピー先の”A”の行番号として、その隣にあるB列のデータをコピーしましょう。
貼り付けたら１個下の行に移りたいので、iに１を足していきます。

FindNextでサンプルAを見つけてコピーした後の図

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

まとめ

今回はFindとFindNextを解説しました。
１つだけ見つけたいならFindで、複数見つけたいならFindNextをループさせましょう。
コピペのプログラムと非常に相性が良いのでぜひ使ってみてください！

>>Excelマクロでコピペをする方法
VBAの次にPythonを学ぶメリットと勉強方法
VBAも使えるようになってきたし別の言語に挑戦したいな。。。というかVBAだけじゃスキルとして不安だ。。。 そんな人におすすめな言語がPythonです。 おすすめする理由は、シンプ...
htomblog.com
2023.03.11

コメント

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