エクセルVBAで繰り返し(ループ)マクロをつくる方法

Excel VBAを活用する上で欠かせないのが、繰り返し処理です。データの大量処理やループ処理など、作業を自動化や省力化するには、繰り返し処理のつかいかたをマスターすることが重要です。本記事では、VBAの繰り返し処理について基礎から解説し、実際のコード例を交えながらマクロのつくりかたをわかりやすく紹介します。

繰り返し処理の内、以前の文字入力を制限する方法として紹介した記事でも触れたDo While文やDo Until文については重なる部分はありますが、以前の記事では、使用用途を限定した紹介であったため、こちらでは繰り返し処理文の基本的な書き方を用いて紹介しています。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか？

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか？
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「１日速習講座」がおすすめです。

詳しくはこちら

【結論】VBAの繰り返し処理はFor文かDoWhileまたはUntil文をつかう

Excel VBAで繰り返し処理を実装する場合につかえるものは以下のとおり

独学の学習効率でお悩みの人必見！
＜動画学習見放題サービス＞

初心者にやさしいチューターなら今すぐにはじめられる

■オンラインで学習したい
■プログラミングで副業をはじめたい
■パソコン・エクセルの学習をしたい

詳しくはこちら

エクセルVBAで繰り返し処理マクロ記事の概要

この記事では、エクセルVBAで繰り返し処理を実行するマクロのつくりかたを紹介します。
VBAには、For Nextステートメント、For Eachステートメント、Do Whileステートメント、Do Untilステートメントなど、複数の繰り返し処理が用意されています。それぞれのステートメントの特徴や使い方を詳しく解説し、実際のコード例を交えて具体的な使い方を紹介します。
また、繰り返し処理のネストや、ループ内での条件分岐など、より複雑な処理の実現方法についても触れます。この記事を読むことで、VBAで繰り返し処理を実行する方法を理解し、効率的なプログラミングを行うことができます。

※ここではコンピュータープログラムの繰り返し処理を構成するための手続きや命令における文やブロックを、ステートメントと表現していますが、これはプログラミングという分野の学習を進める上で必ず出会う単語であり、恐らく本ブログを訪れた人は、他のウェブサイトを閲覧する機会があることを想定した表現としています。横文字表現が苦手な人も、表現を難しくとらえずに単なる作法(書きかた)と理解してもらえれば幸いです。

繰り返し処理についての基礎知識

繰り返し処理とは、同じ操作や条件にしたがった操作を何度も繰り返して実行することです。Excel VBAでは、ループ処理を使って繰り返し処理を実装できます。ループ処理には、以下のような種類があります。

01.For Nextステートメント

For Nextステートメントは、指定した回数だけ同じ処理を繰り返します。For Nextステートメントは、次のような構文を持ちます。

For 変数 = 初期値 To 終了値 Step 増分
    処理
Next 変数

サンプルコード

For i = 1 To 10
    Debug.Print i
Next i

02.For Eachステートメント

VBAのFor Eachステートメントは、配列やコレクションなどの要素を反復処理するために使用されます。For Eachステートメントは、次のような構文を持ちます。

For Each 要素 In コレクション
    処理
Next 要素

コレクションがオブジェクトなら要素はオブジェクト型の変数で宣言、配列であればバリアント型の変数で宣言することに注意が必要です。

サンプルコード

Sub sample()
    Dim arr() As Integer
    arr = Array(1, 2, 3, 4, 5)
    
    Dim num As Variant
    For Each num In arr
        MsgBox num
    Next num
End Sub

03.Do Whileステートメント

Do Whileステートメントは、条件がTrueの間だけ同じ処理を繰り返します。以下のように書きます。

Do While 条件式
    処理
Loop

サンプルコード

i = 1
Do While i <= 10
    Debug.Print i
    i = i + 1
Loop

04.Do Untilステートメント

Do Untilステートメントは、条件がFalseの間だけ同じ処理を繰り返します。以下のように書きます。

Do Until 条件式
    処理
Loop

サンプルコード

i = 1
Do Until i > 10
    Debug.Print i
    i = i + 1
Loop

繰り返し処理を使うことで、同じ処理を手作業で何度も繰り返す手間を省くことができます。また、繰り返し処理は、大量のデータを扱う場合や、複雑な計算を行う場合にも有効です。

ただし、無限ループに陥ることがないよう、ループの条件を正確に設定することが大切です。

Excel VBAで繰り返し処理を行うことの重要性

Excel VBAで繰り返し処理を行うことは、プログラムの効率性を高めるために非常に重要です。

例えば、同じような処理を何度も行う場合、繰り返し処理を使えばコードを簡潔にまとめることができます。また、大量のデータを扱う場合には、繰り返し処理を使用することで、手動で1つずつ処理を行うよりも効率的にデータを処理できます。

さらに、繰り返し処理を使えば、複雑な処理も実現できます。例えば、Excel VBAでは、繰り返し処理の中で条件分岐ができます。これにより、複雑な条件に応じて、処理の流れの制御ができます。

繰り返し処理を使うことで、プログラムの作成時間やメモリ使用量を削減できます。より効率的なコードを書くことができれば、プログラムの実行速度も向上し、大規模な処理でも高速に処理ができます。

Excel VBAにおけるループ処理の種類

繰り返し(ループ)処理の種類について

既に紹介していますが、繰り返し(ループ)処理の書き方については、以下の４種類があります。
それぞれの特徴については、本記事の冒頭で紹介しましたのでこちらでは最低限のみで記載します。

各繰り返し(ループ処理)の使いどころ

For Nextステートメントのつかいどころ

For Nextステートメントは、処理を繰り返す回数が事前に分かっている場合に有効です。例えば5回繰り返したい、10行分だけ繰り返したいなど具体的な回数を指定する場合に使います。

また、エクセルワークシート上の表では、行数分や、列数分を繰りかえすといったコードをよく使います。このコードを使うために、表の最終行や、最終列を数値で取得し、For Next文の指定回数として利用します。

ワークシートに上の表の最終行や、最終列の取得方法については、別の記事で紹介しているため、ここでは詳しく紹介しませんが、詳しく知りたい人は以下の記事をご覧ください。

エクセルVBAのマクロをつかって処理する範囲を取得する方法

VBAでマクロを作る上で大切な点として"処理をする対象範囲を明確にする"ことが挙げられます。この記事では、エクセルのマクロでよく使われる、最終行や最終列の取得方法を紹介しています。正確な範囲を取得することは、VBAでワークシート関数を実装するときの引数としても利用できるため、"Sum"や"Count"、"Average...

kodamanomori.com

For Eachステートメントのつかいどころ

For Eachステートメントは、配列やオブジェクトなどに対して全ての要素に対して繰り返し処理するときに便利です。例えばエクセルを新規作成時、ワークシートの数は1つだけですが、複数のワークシート分の処理が必要な場合や、特定のワークシートのセル範囲を網羅的に処理したい場合につかえます。コレクション(複数の集合体)から、1つずつ取り出して順番に処理を繰り返していくようなイメージをすると分かりやすいかもしれません。

Do Whileステートメントのつかいどころ

Do Whileステートメントは条件式を指定して、この式に一致している間は処理を繰り返します。使いどころとして繰り返し(ループ)の実行回数が不明な場合に有用です。例えば、ある条件を満たしている間は処理を繰り返す場合などです。また、繰り返し(ループ)の条件が繰り返し(ループ)内の処理に依存する場合は、Do Whileループを使用します。具体的な例をあげるならば、あるセルが空でない場合にセルの値を処理する場合などです。

Do Untilステートメントのつかいどころ

Do Untilステートメントは条件式を指定して、この式に一致するまでの間は処理を繰り返します。UntilとWhileは逆説の条件ですが、こちらも繰り返し(ループ)の実行回数が不明な場合に有用です。例えば、ある条件が満たされるまで処理を繰り返す場合などです。また、Do Whileと同様に繰り返し(ループ)の条件が繰り返し(ループ)内の処理に依存する場合に使用します。具体的な例をあげるならば、あるセルが空である場合にセルの値を処理する場合などです。

独学の学習効率でお悩みの人必見！
＜動画学習見放題サービス＞

初心者にやさしいチューターなら今すぐにはじめられる

■オンラインで学習したい
■プログラミングで副業をはじめたい
■パソコン・エクセルの学習をしたい

詳しくはこちら

Excel VBAで繰り返し処理の実装方法

Excel VBAで繰り返し処理を実装方法について

ここでは繰り返し処理の実装方法を紹介します。For Nextステートメント、For Eachステートメント、Do Whileステートメント、Do Untilステートメントの4種類で同じ実行結果(イミディエイトウィンドウに1から10の数値の出力)となるプログラムを書いているため、それぞれのステートメントで書き方を見比べることができると思います。

実行結果

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10

For Nextステートメントの実装方法

Sub ForNextステートメント()

'カウンタ変数を宣言する
Dim count As Integer

'For Nextステートメントで変数countは1から10まで繰り返す
For count = 1 To 10
    Debug.Print (count) '繰り返す処理を記述する
Next count

End Sub

For Eachステートメントの実装方法

Sub ForEachステートメント()

'配列の宣言と初期化
Dim myArray As Variant

'配列にArray関数つかって1から10の数値を代入する
myArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

'要素を代入する変数itemを宣言する(バリアント型)
Dim item As Variant

'For Eachステートメントをつかって配列内の要素分の回数繰り返す
For Each item In myArray
    Debug.Print item '繰り返す処理を記述する
Next item

End Sub

Do Whileステートメントの実装方法

Sub DoWhileステートメント()

'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1

'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
    Debug.Print (count) '繰り返す処理を記述する
    count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop

End Sub

Do Untilステートメントの実装方法

Sub DoUntilステートメント()

'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1

'Do Untilステートメントをつかってcountが10になるまで繰り返す
Do Until count > 10
    Debug.Print (count) '繰り返す処理を記述する
    count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop

End Sub

ループ処理のコードの制御構文の比較

ここでは、前に紹介した繰り返し処理のステートメントの制御構文を比較していきます。

Sub ForNextステートメント()

'カウンタ変数を宣言する
Dim count As Integer

'For Nextステートメントで変数countは1から10まで繰り返す
For count = 1 To 10
    Debug.Print (count) '繰り返す処理を記述する
Next count

End Sub

Sub ForEachステートメント()

'配列の宣言と初期化
Dim myArray As Variant

'配列にArray関数つかって1から10の数値を代入する
myArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

'要素を代入する変数itemを宣言する(バリアント型)
Dim item As Variant

'For Eachステートメントをつかって配列内の要素分の回数繰り返す
For Each item In myArray
    Debug.Print item '繰り返す処理を記述する
Next item

End Sub

For NextとFor Eachステートメントの比較で注目しておきたい点は、宣言する変数のデータ型です。
※データ型については、こちらの記事で紹介しています。

For Nextは繰り返す回数を指定できるステートメントなので、宣言した変数countは整数型に指定し、数値の1～10が処理の度に1ずつカウントしていきます。
なお、この場合、別途 count = 1 の変数に値を代入するコードは不要で、代わりに For count = 1 To 10 のコードが変数countに1～10を代入しています。さらに言えば、For Nextよりも前に変数countに値の代入をしていても、繰り返し処理の中では、変数countは1～10の値に書き変わってしまう点はおぼえておきましょう。

For Nextステートメントの末尾にあるNext countの部分で変数countに代入された数値が1加算されます。For Nextでは、ループごとにステップする数の指定もできます。Stepを指定した場合は、加算される数値に応じた数値が加算されます。

一方、For Eachステートメントでは、繰り返す回数を指定するのではなく、集合体となる配列やオブジェクトの要素の数だけ繰り返します。ここでは、バリアント型の配列myArrayを宣言し、Array関数で1～10の数値を代入。これにより、10コの要素(数値)の集合体となったmyArrayに対する繰り返す回数は10回です。

そして、For eachステートメントでは、要素を代入するための変数をつかいます。サンプルコードでは、itemという名称で変数を宣言していますが、要素を代入する変数はバリアント(Variant)型でなければエラーが生じる点です。

Sub DoWhileステートメント()

'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1

'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
    Debug.Print (count) '繰り返す処理を記述する
    count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop

End Sub

Sub DoWhileステートメント()

'カウンタ変数を宣言し、1を代入する
Dim count As Integer: count = 1

'Do Whileステートメントをつかってcountが10になるまでの間繰り返す
Do While count <= 10
    Debug.Print (count) '繰り返す処理を記述する
    count = count + 1 '変数countに1を足す(※書かないと無限ループします)
Loop

End Sub

Do WhileとDo Untilステートメントでは、Forステートメントのように回数や集合体の要素数分を繰り返すのではなく、条件を指定します。Whileは条件判定の結果がTrueの間は、繰り返しの処理を継続し、判定結果がFalseになった時点で終了。逆にUntilは、Falseの間は繰り返しの処理を継続。Tureになったら終了します。

サンプルコードのコメントでも分かるとおり、Do WhileやUntilステートメントでは、Forステートメントのように末尾にNextがないので、無限ループを回避するための対策として、繰り返しのたびに変数countに＋1の処理を実行しています。

繰り返し(ループ)変数の使い方や、変数のスコープについて

ここまでで、VBAで繰り返し処理を行う場合、ForステートメントやDo While・Untilステートメントを使用することが一般的であることは理解できたかと思います。
これらのステートメントでは、繰り返し(ループ)変数を使用する手段が多くつかわれます。繰り返し(ループ)変数とは、繰り返しの処理内で変化する変数ですが、ここでは繰り返し(ループ)変数のつかいかたのポイントと変数のスコープについて説明します。

繰り返し(ループ)変数のつかいかたのポイント

●繰り返し(ループ)変数の初期値を設定する

繰り返し(ループ)変数を使用する場合、最初に変数の初期値を設定する必要があります。初期値を設定しない場合、繰り返しが正しく動作しない可能性があります。

●繰り返し(ループ)変数の値を更新する

繰り返し(ループ)内で、繰り返し(ループ)変数の値を更新することが必要です。更新しない場合、繰り返し(ループ)が正しく動作しない可能性があります。

●繰り返し(ループ)変数の値を使用する

繰り返し(ループ)内で、繰り返し(ループ)変数の値を使用することができます。例えば、繰り返し(ループ)変数の値に応じて処理を分岐するなど、様々な用途があります。

変数のスコープについて

変数のスコープとは、変数が有効な範囲のことです。

ここではVBAにおける変数の有効範囲(スコープ)について紹介します。スコープについて詳しく説明するためには、VBAにおけるモジュールやプロシージャなどの単位・範囲の事前説明が必要だと考えているため、別の記事で詳しい内容を書く予定です。

ここでは変数の宣言のしかたによって、その変数が使える範囲が異なるといった理解で大丈夫です。

●モジュールレベルの変数[Public／Private]

モジュールの宣言セクションに記入ができます。
モジュールレベルの変数において、Publicで宣言したものは、他のモジュールからも使用ができます。
例えば、Module1で宣言した変数をModule2をつかって値の代入ができます。これはグローバル変数と呼ばれたりもしますが、広い範囲で変数を利用できるメリットの反面、プログラム全体の可読性が悪くなるデメリットを発生させることを理解しておきましょう。

一方、Privateで宣言したモジュールのスコープは、同じモジュール内で使用ができます。同じモジュール内の他プロシージャからでも使用ができます。

●プロシージャレベルの変数

プロシージャレベルの変数は、変数を宣言したプロシージャ内でのみ使用ができます。プロシージャレベルの変数は、プロシージャの先頭でDimステートメントを使用して宣言できます。こちらはローカル変数と呼ばれ、プロシージャ実行中のみの有効期間となる変数です。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか？

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか？
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「１日速習講座」がおすすめです。

詳しくはこちら

Excel VBAの繰り返し処理の例題

実際の問題を解決するために、繰り返し処理を使ったコード例

For Nextステートメントで行または列に対する計算

入力内容

(1)合計列の緑色のセルに入力されている点数を合計する

Sub For_Next_sample1()

Dim i As Long
Dim value As Long

For i = 2 To 11 Step 3
     value = value + ThisWorkbook.Worksheets("Sheet5").Range("K" & i).value
Next i

MsgBox ("K列の緑色のセルの合計は" & value)

End Sub

ポイントは、Step 3の部分です。増量の指定により、変数iの値の変化は以下のとおり。.Range(“K” & i) でK列の行数を変数iに指定しているため、Ｋ列の2行目・5行目・8行目・11行目に記入された値を変数valueに足していきます。

実行結果

緑色に着色したセルの合計を算出することができました。

(2)6行目(小林みどりさん)の青色のセルを合計する

入力内容

Sub For_Next_sample2()

Dim i As Long
Dim value As Long

For i = 6 To 12Step 2
     value = value + ThisWorkbook.Worksheets("Sheet5").Cells(6, i).value
Next i

MsgBox ("6行目の青色のセルの合計は" & value)

End Sub

こちらのプログラムでは、変数iの値は6～12となり、増量は Step 2で指定しています。今度は行を固定して、該当する列に入力された値の加算を繰り返しています。

実行結果

青色に着色したセルの合計を算出することができました。

For Nextステートメントで条件に合わせたセルの操作

今度は、5教科の点数が入力されているF列～J列の中で、30点以下のセルを赤色にするプログラムを作成してみます。

入力内容

Sub For_Next_sample3()

Dim i As Long, j As Long

For i = 2 To 11
    For j = 6 To 10
        If ThisWorkbook.Worksheets("Sheet5").Cells(i, j).value <= 30 Then
            ThisWorkbook.Worksheets("Sheet5").Cells(i, j).Interior.Color = RGB(255, 0, 0)
        End If
    Next j
Next i

MsgBox ("30点以下を赤色にしました。")

End Sub

For Nextステートメントで繰り返し処理を入れ子(ネスト)して、行の繰り返しと、列の繰り返しを実現しています。処理部分はIfステートメントで、条件判定により30点以下のセルを赤色に着色します。

変数iが2の間に、内側のループで変数jが6～10と更新されていき、変数jが10まで到達すると、外側のループが一回りし、変数iは3に更新されます。これを繰り返し変数iが11、変数jが10になるまで処理を継続します。

実行結果

For Eachステートメントで範囲内の全てのセルに数値を入力する

以下のセル範囲をオブジェクト型の変数(area)に代入し、繰り返し処理で全てのセルに1つずつ数値を入力するプログラムを作成します。

入力内容

Sub ForEachステートメント_Image()

Dim ws As Worksheet 'Worksheet型の変数を宣言
Dim area As Range 'Range型の変数を宣言

Set ws = ThisWorkbook.Sheets("テストシート") '作業対象シートを代入
Set area = ws.Range("A1", "J5") '作業対象範囲を代入

Dim cell As Variant'繰り返し用の要素を代入する変数を宣言(オブジェクト)
Dim i As Integer: i = 1 '入力する数値を代入する変数を宣言する(オブジェクト)

'作業対象範囲から全てのセルに数値を入力する繰り返し処理
For Each cell In area
    cell.value = i
    i = i + 1
Next cell

End Sub

実行結果

A1からJ5セルの範囲に1セルずつに数値を記入することができました。このサンプルコードでは、変数iの値を範囲内のセルに入力しますが、入力後に変数iに1が足されていくので、次のセルに入力される数値は i + 1です。

入力された数値に着目すると、For Eachステートメントで繰り返し処理を実行した場合、値を入力する順番がA1→B1→C1…J1と列を移動しながら処理を進め、最終列のJ列まで完了した後で、行を移動し、A2セルを処理しています。それを繰り返し、最後のJ5セルまで処理されたことが分かります。

For Eachステートメントでオブジェクトの色を変更する

For Eachステートメントでは、セルを対象にするだけでなく、ワークシートに挿入した図形(オブジェクト)の色を変えるプログラムも作成できます。

入力内容

Sub ChangeShapeLineColor()
    Dim shp As Shape 'Shape型の変数を宣言する(objectでも良い)
    
    'カラーという名前のシート上にある図形の数だけ繰り返す
    For Each shp In Sheets("color").Shapes
    'Select Caseステートメントで現在の色を判定して処理を実行
        Select Case shp.Fill.ForeColor.RGB
            Case RGB(255, 0, 0) '元の色が赤色なら緑色に変更
                shp.Line.ForeColor.RGB = RGB(0, 255, 0)
                shp.Fill.ForeColor.RGB = RGB(0, 255, 0)
                shp.Fill.BackColor.RGB = RGB(0, 255, 0)
            Case RGB(0, 255, 0) '元の色が緑色なら青色に変更
                shp.Line.ForeColor.RGB = RGB(0, 0, 255)
                shp.Fill.ForeColor.RGB = RGB(0, 0, 255)
                shp.Fill.BackColor.RGB = RGB(0, 0, 255)
            Case RGB(0, 0, 255) '元の色が青色なら赤色に変更
                shp.Line.ForeColor.RGB = RGB(255, 0, 0)
                shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
                shp.Fill.BackColor.RGB = RGB(255, 0, 0)
        End Select
    Next shp

End Sub

実行結果

ワークシート上にある全てのオブジェクトの色を変化するプログラムの実行結果は以下のとおり。
Select Caseステートメントで元の色を判定して、赤なら緑、緑なら青、青なら赤へと元の色に応じて他の色に塗りかえます。

以下の画像は、サンプルプログラムを実行している様子の画像です。
実行される度に図形の色を変えるプログラムですが、For Eachステートメントで、ワークシート上の3つのオブジェクト(図形)を集合体として、全てのオブジェクト(図形)を繰り返し処理で1つずつ色をかえています。

Do Whileステートメントでデータのフィルタリング

Do Whileステートメントをつかって、配列から指定条件に該当する値をフィルタリング(抽出)するサンプルコードを書いてみます。

入力内容

Sub Do_While_sample()

Dim Arr1 As Variant
Dim i As Integer, j As Integer
Dim Arr2() As String

' テストデータを設定
Arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

' フィルタリングを実行
i = 0
Do While i <= UBound(Arr1)
    If Arr1(i) Mod 3 = 0 Then
        ReDim Preserve Arr2(j)
        Arr2(UBound(Arr2)) = Arr1(i)
        j = j + 1
    End If
    i = i + 1
Loop


' 結果を表示
Debug.Print "フィルタリングされた値は" & Join(Arr2, ",") & "です。"
End Sub

まずは、テストデータを代入する入れる変数Arr1を宣言します。その他に繰り返し用の変数iと配列のインデックスとなる変数jを宣言します。最後に該当した値を代入する配列Arr2を宣言します。

次に、変数Arr1にArray関数をつかって1～10の値で配列を作成します。これによりArr1は以下になります。

Do Whileステートメントにて繰り返し処理ですが、Arr1の全ての要素分を繰り返す条件にしたいため、Ubound関数で要素数を取得します(サンプルコードでは9となる)。変数iが9以下の間は繰り返し処理を実行するため、結果としてArr1の要素の分だけ繰り返します。

続いて処理の部分ですが、IfステートメントにてArr1の要素の全ての値を3で割って余りが0かどうかを判定しています。判定の結果がTrueであれば、動的配列Arr2の要素数を変更して、該当したArr1の値をArr2に代入していきます。Arr1の値の内、3で割ったときに余りが0となる数値は、3、6、9ですのでArr2の配列にはこの3つの数値が代入されます。

さいごに以下のコードで、配列Arr2の値を、Join関数でつなげてイミディエイトウィンドウに表示します。

Debug.Print "フィルタリングされた値は" & Join(Arr2, ",") & "です。"

実行結果

フィルタリングされた値は3,6,9です。

Do Untilステートメントでデータのフィルタリング

Do Untilステートメントで、特定の範囲内のセルの内、指定の文字列が入力されているセルがいくつ該当したのか、また、どこのセルに該当文字が入力されているかを返すプログラムを作成します。

上記のリストから、”ABC”の文字列が該当するセルがいくつあるか。また、そのセルの座標(位置)を返します。

入力内容

Sub Do_Until_sample()

    Dim rng As Range
    Dim cell As Range
    Dim hit As Integer
    Dim address() As Variant
    
    ' 対象の範囲を指定
    Set rng = ThisWorkbook.Worksheets("Sheet6").Range("A1:A21")
    
    ' フィルタ条件
    Dim filterValue As String
    filterValue = "ABC"
        
    ' フィルタ処理
    Set cell = rng.Columns(1).Cells(1)
    Do Until cell.Value = ""
        If cell.Value = filterValue Then
            ReDim Preserve address(hit)
            address(hit) = cell.address
            hit = hit + 1
        End If
        Set cell = cell.Offset(1, 0)
    Loop
    Debug.Print ("ABCがヒットした数：" & hit)
    Debug.Print ("セル番地は以下のとおり" & vbCrLf & Join(address, vbCrLf))
End Sub

作業対象範囲を代入するためのオブジェクト(Range)型変数として、変数rngを準備します。また対象範囲の内の1つのセルを代入する変数cellとヒット数を代入する目的で変数hit・該当セルのアドレス格納するための配列としてaddressを宣言しています。
また、検索キーワードを代入するための変数は文字列(String)型にしてfilterValueという名称にしています。

作業対象である.Range(“A1:A21”)を変数rngに、検索キーワードとして文字列を変数filterValueに”ABC“を代入します。さらに、変数cellにはrng.Columns(1).Cells(1)を代入するコードがありますが、これはA1からA21セルの1列目のひとつめのセルという意味です。
rngに代入されているセル範囲は、A1：A21であり1列しかありませんので、変数cellの最初の参照先は、A1セルです。

Do Untilステートメントで、変数cellの値が空白になるまで繰り返す条件式です。
具体的には、A1から順番に処理を繰り返していくとA22セル以降は値が入っていないため、A21セルまで処理が繰り返されます。
そしてポイントとなるコードは「Set cell = cell.Offset(1, 0)」です。
これは、現在のセル座標を(0, 0)として、Offset(行数, 列数)と表記することで、行数や列数に記載した数値分シフトします。サンプルコードでは、1行下にシフトする処理をしています。これによって、A1→A2→A3…と処理を1セルずつシフトしていく仕組みを実装しています。

Ifステートメントをつかって条件判定をしています。cellが参照したセルに記入された値がfilterValueに代入された”ABC”と同じであるかを判定し、Trueであれば配列addressに該当したセルのアドレスを代入していきます。また、変数hitにも1を加算することで該当する件数を数えています。

変数hitによって、文字列”ABC”と同じ文字列が入力されたセルの個数を返すことができます。さらに配列address内の値を、Join関数をつかってイミディエイトウィンドウに表示します。

実行結果

ABCがヒットした数：3
セル番地は以下のとおり
$A$3
$A$12
$A$20

A1セルからA21までの範囲で、文字列”ABC”が入力されているものは3件見つかり、該当のセル番地はA3セル、A12セル、A20セルとなることが結果として返ってきました。

「VBAで繰り返し(ループ)マクロをつくる方法」まとめ

今回は、VBAの繰り返し(ループ)処理について紹介しました。
こちらでは、これまで紹介した内容をまとめます。

1. For Nextステートメント
2. For Eachステートメント
3. Do Whileステートメント
4. Do Untilステートメント

• 繰り返し処理ができると実務で大きなメリットとなる
• 人力とは比べられないほどの大量のデータ処理が可能になる
• 条件分岐によりさらに複雑な処理もできるようになる

1.For Nextステートメント

繰り返す回数が明確な場合に有効

2.For Eachステートメント

繰り返す対象がオブジェクトや配列である場合に有効

3.Do Whileステートメント

繰り返す回数が不明確で、条件による指定をしたい場合に有効。条件判定でTrueの間は繰り返しを継続します。Untilステートメントとは逆の関係です。

4.Do Untilステートメント

繰り返す回数が不明確で条件による指定をしたい場合に有効。条件判定でFalseの間は繰り返しを継続します。Whileステートメントとは逆の関係です。

繰り返し処理で利用する変数においてスコープ(有効範囲)は、宣言のしかたによってローカル変数やグローバル変数となるが、グローバル変数をあつかう場合は、他モジュールからの影響で変数に意図しない値が代入されないことや、可読性が低下することについて考慮する必要がある。

今回は、VBAの繰り返し(ループ)処理について紹介しました。繰り返し処理は、条件分岐と並んでプログラムにおける重要なポイントだと言えます。また、エクセル関数では実行できないものとなるので、VBAの真骨頂とも言える部分だと言えます。
繰り返し処理がつかえると、実務でもVBAを活用できる幅は多くなってきます。そのため、ぜひここで紹介した内容を文字通り”繰り返し”見ながら自身のスキルとして身につけていって頂けたら幸いです。

今回はここまで、さいごまでお読みいただきありがとうございます。

コダマのもりブログはにほんブログ村に登録しています。
今回の記事が役に立ったと感じて頂けたら、フォローお願いいたします。

にほんブログ村