Mutable_Yunの業務改善ブログ

VBA勉強中の非エンジニア会社員向けのブログです。業務改善についても触れています。

Excel VBA ループで検索して別シートに転記する

Excel VBAでシート内を検索する方法を単刀直入に解説します。検索した結果を別シートに転記します。

目次

For~Nextステートメントでループして検索する

検索するにはループで繰り返し値が条件に一致するかを確認します。今回は別シートに転記する事にします。
サンプルデータはこのようなものです。

今回の題材となるサンプルデータ
店舗ID、地域、業態の一覧表

図は他店舗展開している飲食業の店舗一覧のイメージです。A列に店舗ID、B列に地域、C列に業態が記入されています。これが100行までデータが存在します。

データ最終行とシート名
データ最終行とシート名

一致したら転記する

検索は繰り返し構文でループして値を確認し、条件に一致したものを抽出する、というパターンと決まっています。
まずは一番簡単な、文字列が一致していたら、店舗IDを転記すると言うコードを作ってみます。店舗一覧からラーメン屋の店舗IDのみを検索結果のシートに転記します

Sub 完全一致検索()

    Dim i As Long '店舗一覧の行数のカウントアップ用
    Dim j As Long '転記先の行数のカウントアップ用
    Dim rw As Long '最終行取得用
    
    Sheets("店舗一覧").Activate
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    j = 1 '初期設定
    
    For i = 2 To rw
        If Cells(i, 3) = "ラーメン屋" Then
            With Sheets("検索結果")
                .Cells(j, 1) = Cells(i, 1)
                j = j + 1
            End With
        End If
    Next i
    
End Sub

処理結果は下記の通りとなりました。

完全一致検索の作業結果
C列の値が「ラーメン屋」と等しいときのA列に値を転記した

完全一致の時はセルの値が等しいかどうかをIfで条件分岐すれば、簡単に検索が可能です。
今回は別シートに転記する、と言う内容なので、転記先の行番号をカウントアップする為の変数を宣言したのがポイントです。

含んでいたら転記する

今度は完全一致ではなく、「含む」む店舗を検索しましょう。店舗IDに5を含む店舗IDを抽出してみます。

Sub 含む検索()

    Dim i As Long '店舗一覧の行数のカウントアップ用
    Dim j As Long '転記先の行数のカウントアップ用
    Dim rw As Long '最終行取得用
    
    Sheets("店舗一覧").Activate
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    j = 1 '初期設定
    
    For i = 2 To rw
        If InStr(Cells(i, 1), 5) <> 0 Then
            With Sheets("検索結果")
                .Cells(j, 1) = Cells(i, 1)
                j = j + 1
            End With
        End If
    Next i
    
End Sub


実行結果
店舗IDに5を含む店舗IDを抽出した

Ifの条件文の部分を書き換えただけです。Instrは何文字目に含むかを返す関数で、含まれていなければ0を返します。よって、A列の値に5が含まれていない、と言う条件文にしました。

条件が合えば転記する

福岡のラーメン屋の店舗IDを検索しつつ、何件あるかをカウントしてみましょう。B列が「福岡」かつC列がラーメン屋ならOKです。

Sub 含む検索1()

    Dim i As Long '店舗一覧の行数のカウントアップ用
    Dim j As Long '転記先の行数のカウントアップ用
    Dim rw As Long '最終行取得用
    Dim 店舗数 As Long
    
    Sheets("店舗一覧").Activate
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    j = 1 '初期設定
    
    For i = 2 To rw
        If Cells(i, 2) = "福岡" And Cells(i, 3) = "ラーメン屋" Then
            With Sheets("検索結果")
                .Cells(j, 1) = Cells(i, 1)
                j = j + 1
                店舗数 = 店舗数 + 1
            End With
        End If
    Next i
    
    Sheets("検索結果").Cells(1, 2) = "福岡にラーメン屋は" & 店舗数 & "店舗あります。"
    
End Sub

Ifの条件文の所に2つの条件をAndでつないで2つの条件を一致するようにしています。他に考えられるのは、B列の値とC列の値を「&」でつないだ文字列を条件文に使う方法が考えられます。この方法は、「B列またはC列の少なくとも一方に○○を含む」と言ったときに便利です。

カスタム検索の結果
福岡かつラーメン屋

ここまでできたらいろんな検索ができる気がしてきませんか?○○と言う文字で始まる/終わるとか○○を含む、とか○○より大きいとか、Ifの条件文の部分をいろいろ変えるだけです。

いろいろと試してみましょう!検索結果の転記を高速化したい方は、配列、検索そのものを効率化したい場合は、予め検索する候補を絞る、と言った工夫をしてみて下さい!