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の条件文の部分をいろいろ変えるだけです。

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

＜ゆんの電子書籍＞ゆんの電子書籍はすべてkindle unlimitedで読み放題です!

Excel VBA 高速化のテクニック: 理論から実践まで

ワンランク上のExcel VBAプログラミング: クラスとオブジェクトを身に付けるハンズオンガイド

Excel VBA 作りながらじっくり身に付けるユーザーフォームとクラス: 「なんとなく」で済ませない！手を動かして身に付けるホントのキホン

はじめてのアメリカ駐在　生活立ち上げがこれ１冊でまるわかり: 渡米前の準備から渡米後の帯同家族の受け入れまで実体験を徹底解説！

カナディアンロッキー小紀行

Kindle Unlimitedは読み放題！

Kindle Unlimitedは月額定額制で漫画や小説、雑誌が読み放題のサービスです。私の著書もすべてKindle Unlimitedで読み放題です。

電子書籍は購入すると著者の取り分が低下の３５％の書籍は９９円以上、著者の取り分が７０％なら２５０円以上の値段をつける必要があります。そう考えると２５０円の本なら４冊読めば元が取れる計算です。

私自身Kindle Unlimitedのヘビーユーザーです。私のKindle Unlimitedの使い方は個人が出している読み放題対象の本はどうせ読み放題なのでパンフレット的にざっと目を通して概要を把握するというものです。そして概要が分かってさらに深く知る必要があるという場合には紙の本を買うという具合です。

どうせ読み放題なので立ち読み感覚で目を通せるのが良いところです。

会員をやめるのも簡単なので使わない月は解除するのもアリです。私も会員になったりやめたりメリハリをつけて使っています。

Kindle Unlimitedで読み放題は下のリンクから簡単に可能です。

Amazon.co.jp