初心者のためのExcel(エクセル)マクロVBA入門-実践プログラミング - 初心者のためのExcelマクロ超入門（絶対できるVBA開発）

前回の答え

A列に500個のランダムな文字列データがあります。その中から文字列[abc]が含まれているものを検索して、そのセルの色を黄色にするだけの簡単なマクロになります。ぶっちゃけ、エクセルの関数とかを使ってやればマクロなんて組まなくてもできてしまうことだと思いますが、今回は勉強のつもりでやってみましょう。

順番にやっていきましょう。

VBEを開いて標準モジュールを作成します。VBEのプロジェクトエクスプローラーの[Microsoft Excel Objects]フォルダを右クリックして[挿入]→[標準モジュール]を選択します。すると標準モジュールフォルダが作成され、その中にModule1が作成されます。後は、Module1にマクロ書いて行きます。

プロシージャのはじめと終わり

まずはtest1という名前のプロシージャを作成します。基礎でやってますので覚えてますでしょうか？

' abcを検索して色を黄色に変えるマクロです。
Sub test1()


End Sub

これで、このマクロの最初と最後を作ることができました。

プログラムを日本語で書く！

次にやりたいことを全部言葉で書いて行きましょう。

Sheet1のA列からabcを検索する
検索結果があれば、その時のセルを黄色にする

まぁ、ざっくりいうとこれで終わりなのでまずはこれをVBAで書いてみましょうか？

' abcを検索して色を黄色に変えるマクロです。
Sub test1()

   ' A列からabcを検索する
   Sheet1.Range("A:A").Find(What:="abc")
   
   ' 検索結果があれば、その時のセルを黄色にする
   ’？あれ検索結果ってどこ？

End Sub

さて、まずは検索する命令文を書くことができましたが、検索する結果があればセルを黄色にするには検索結果がわからないといけません。前回見た通りヘルプで確認するとFindメソッドはRangeオブジェクトを戻してくれますし、なければNothingを戻します。これを評価するのは・・・

変数を使う

そう、こういう場合は変数を使います。では変数を使うためにまず変数を使いましょう。日本語で書くと・・・

検索結果を格納する変数を作成する
Sheet1のA列からabcを検索結果を変数に入れる
検索結果があれば、その時のセルを黄色にする

' abcを検索して色を黄色に変えるマクロです。
Sub test1()

   ' 検索結果を格納する変数を作成する
   Dim findRet As Range
   
   ' A列からabcを検索する
   Set findRet = Sheet1.Range("A:A").Find(What:="abc")
   
   ' 検索結果があれば、その時のセルを黄色にする
   ’あれ？検索結果があっても、Rangeの何で判断すればいいの？

End Sub

このようになります。変数findRetを作成して、それに検索結果を代入しています。Rangeはオブジェクトなので必ずSetを忘れずにつけるようにしましょう。

Nothingで判断する

上のプログラムでも書きましたが、検索結果はRangeオブジェクトです。検索した結果、見つかったセルのRangeオブジェクトが入りますが、どのセルかもわからないので、判断できません。こういう時は・・・

Rangeオブジェクトが存在してるかどうか？で判断する。

ということです。ちゃんとヘルプに検索結果がなかったらNothingが戻ってくるということは「Nothiingじゃなかったら検索結果があったということ」になります。はい、日本語で書き直し。

検索結果を格納する変数を作成する
Sheet1のA列からabcを検索結果を変数に入れる
検索結果がNothingじゃないなら、セルの色を黄色にする

プログラミングでー。

' abcを検索して色を黄色に変えるマクロです。
Sub test2()

   ' 検索結果を格納する変数を作成する
   Dim findRet As Range
   
   ' A列からabcを検索する
   Set findRet = Sheet1.Range("A:A").Find(What:="abc")
   
   ' 検索結果がNothingじゃないなら・・・
   If Not findRet Is Nothing Then
   
       ' セルの色を黄色にする
       Sheet1.Cells(findRet.Row, 1).Interior.ColorIndex = 6
   
   End If

End Sub

これを実行すると、まず最初に検索されたセルが黄色になるはずです。

繰り返す時は無限ループに注意

さて、あとはこれを全部繰り返す！ということになるのですが、、、このままではたとえ500回繰り返しても、同じセルしか黄色になりません。それは、Findのヘルプをよく読むとわかります。前回の・・・

After

オプション

バリアント型 (Variant)

セル範囲内のセルの 1 つを指定します。
このセルの次のセルから検索が開始されます。
引数 After で指定するセルは、コードからではなく、通常の画面上で検索を行う場合のアクティブセルに該当します。
このセルの次から検索が開始されるため、範囲内の他のセルがすべて検索され、このセルに戻るまで、このセル自体は検索されません。
この引数を省略すると、対象セル範囲の左上端のセルが検索の開始点になります。

これです。つまりFindするときに必ずAfterを指定してあげないと何回繰り返してもA1から検索するので最初に検索されたセルしか黄色にならないということです。

さて、では何回繰り返したらいいのでしょう？abcを含むセルがいくつあるかわからないので、ここではDoLoopを使います。DoLoopは特に条件がなければ永遠に繰り返し続ける繰り返し構文です。気を付けないと無限ループとなりいつまでたってもマクロが終わらないという事態になってしまいます。
ではループが終わる条件はなんでしょうか？残念ながら検索は最後まで検索したら、次は一番最初に戻り、永遠にループしています。ですのでこの場合は

最初に検索したセルを覚えておいて
検索結果が最初のセルだったら、ループを終了する

ということになります。では、また日本語で書いてみましょう。

検索結果を格納する変数を作成する
Sheet1のA列からabcを検索した結果を変数に入れる（最初のセル）
検索結果がNothingじゃないなら、セルの色を黄色にする
繰り返しスタート
- 検索開始セルを指定して再検索
- 検索結果が最初のセルと同じか？
- 同じならループを抜ける
- 違うかつNothingでないならセルを黄色にする

となりますね。ではプログラミングで

Sub test1()

    ' 検索結果を格納する変数を作成する
    Dim findRet As Range
    '最初の検索結果を覚えておく変数を作成する
    Dim findRetFirst As Range
    
    ' A列からabcを検索する
    Set findRet = Sheet1.Range("A:A").Find(What:="abc")
    ' 最初の検索結果を覚えておく
    Set findRetFirst = findRet
    
    ' 検索結果がNothingじゃないなら・・・
    If Not findRet Is Nothing Then
    
        ' セルの色を黄色にする
        Sheet1.Cells(findRet.Row, 1).Interior.ColorIndex = 6
    
    End If
    
    ' 繰り返しスタート
    Do
        
        ' 検索開始セルを指定して再検索
        Set findRet = Sheet1.Range("A:A").Find(What:="abc", After:=findRet)
        
        ' 検索結果が最初のセルと同じか？
        If findRet.Address = findRetFirst.Address Then
            ' ループ終了
            Exit Do
        Else
            '違うかつ検索結果がNothingじゃないなら・・・
            If Not findRet Is Nothing Then
                ' セルの色を黄色にする
                Sheet1.Cells(findRet.Row, 1).Interior.ColorIndex = 6
            End If
        End If
   
    Loop
   

End Sub

これが正解になります。実行してみてくださいね。検索結果が最初のセルかどうか？については、Addressプロパティを使います。それか今回はA列と決まっているのでRowプロパティで行を比較してもOKだと思います。

次回はFindメソッドを使わないで書いてみましょう。