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

VBA
スポンサーリンク

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

1行目にある”測定値A”を見つけるコード
列番号:Find(“検索文字”).Row 
行番号:Find(“検索文字”).Column

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

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

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

列番号:Find(“検索文字”).Row 
行番号:Find(“検索文字”).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”しかヒットしません。

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つ目の検索結果になっている

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

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

実行してみると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で複数のセルを検索する

同じ処理を何度も書くのはめんどくさいので、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

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”からそれぞれの測定項目をプログラム実行ファイルにコピーしましょう。

手順は簡単です。

①Findで欲しい項目の行列番号を手に入れる
②手に入れた番号を使ってセルを指定し、コピペする

まずは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マクロで最終行(列)を取得する方法をご覧ください。

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

“測定値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です。

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

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

FindNextを使ってコピペする

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

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

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

このプログラムの手順は、

①Findで最初の”A”を見つける
②最初の”A”をコピペする
③loop文とFindNextで次の”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は貼り付け先の行番号です。最初は2で次は3…と増えていきます。

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

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

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

プログラムの実行結果

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

まとめ

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

Find       :1つのデータのみを見つける
FindNext:2つ以上のデータを見つける(無限ループに注意)

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

コメント

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