Excel VBA

【Excel VBA】For Each Next文の使い方！配列操作や抜ける方法も！

【Excel VBA】For Each Next文の使い方！配列操作や抜ける方法も！

VBAでFor Each…Nextステートメントを使いセルの値を取得したいときはないでしょうか。

けど、そんな中で悩むことは、

・VBAでFor Each…Nextステートメントを使いセルの値を取得したいが方法がよくわからない
・VBAでFor Each…Nextステートメントを使い配列操作をしたいがが思うように取得できず困っている

ですよね。

今回はそんなお悩みを解決する

・VBAでFor Each…Nextステートメントを使いセルの値を取得したり、途中で抜ける方法
・VBAでFor Each…Nextステートメントで配列操作をする方法

についてまとめます！

VBAでFor Each…Nextステートメントを使いセルの値を取得するイメージ

VBAでFor Each…Nextステートメントを使いセルの値を取得するイメージについて説明をします。

Excel側に表を作成します。

VBAを実行すると、Excel表のループでセルの値を取得することができます。

また繰り返し処理に条件を付けて途中で抜けたり、
別シートへセルの値を転記したりしていきます。

それでは早速試してみましょう。

VBAでFor Each…Next ステートメントを使いセルの値を取得する方法

VBAでFor Each…Next ステートメントを使いセルの値を取得する方法について説明をします。

ループでセルの値を取得する表を準備する

Excel側に”Sheet1″シートを作成し、セルの値を取得する対象となる表を作成します。

ループでセルの最終行を取得する対象の列はA列となります。

For Each…Next ステートメントの構文

For Each…Next ステートメントの構文は以下の通りです。

For Each element In group
[statements] [Exit For] [statements] Next [element]

For Each…Next ステートメントの構文の指定項目は以下の通りです。

パーツ 説明
element 必須。 コレクションまたは配列の要素を反復処理するために使用される?変数です。 コレクションの場合、?要素?には?Variant?変数、汎用オブジェクト変数、または特定のオブジェクト変数のみを指定できます。 配列の場合は、element?に使用できるのは?Variant?変数だけです。
group 必須。 オブジェクトのコレクションまたは配列の名前です (ユーザー定義型の配列を除きます)。
statements 省略可能。?group?の各アイテムに対して実行される 1 つ以上のステートメントです。
Exit For For ループを抜けます。

参考：https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/for-eachnext-statement

For Each…Next ステートメント+セルを直接参照するパターン①

VBAコード

For Each…Next ステートメント+セルを直接参照するパターンとなります。

サンプルのVBAコードは以下の通りです。

Sub ForEachでセルの値を取得①()
    Dim intLastRowNum As Integer
    Dim rngTemp As Range
    
    '"Sheet1"シートを対象にします。
    With Sheets("Sheet1")
        '最終行数を取得します。
        intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row
        
        For Each rngTemp In .Range(.Cells(2, 1), .Cells(intLastRowNum, 3))
            Debug.Print rngTemp.Cells(1, 1)
        Next
    End With

End Sub

VBAの実装

VBAの実装方法については
VBAの実装手順
をご参照ください。

VBAを実行する

VBAを実行してみましょう。

イミディエイトウィンドウにセルの値が表示されましたね！

1
A
100
2
B
110
3
C
120
4
D
130
5
E
140
6
F
150
7
G
160

タカヒロ
タカヒロ
For Each…Next ステートメントでセル範囲を処理する場合は全列を出力した後次の行に行く順序となります。

VBAの説明

VBAについて説明をします。

“Sheet1″シートを対象にします。

With Sheets("Sheet1")

対象の表のA列最終行数を取得します。

intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row

For Each…Next ステートメントで2行目から最終行目まで繰り返し処理をおこないます。

For Each rngTemp In .Range(.Cells(2, 1), .Cells(intLastRowNum, 3))

rngTempのCellsオブジェクトのValueプロパティから値を出力します。

Debug.Print rngTemp.Cells(1, 1)

For Each…Next ステートメントとExit Forで条件に合致したら処理を抜けるパターン③

VBAコード

For Each…Next ステートメントとExit Forで条件に合致したら処理を抜けるパターンとなります。
サンプルでは5行目になったら処理を抜ける内容となっています。

サンプルのVBAコードは以下の通りです。

Sub ForEachでセルの値を取得②()
    Dim intLastRowNum As Integer
    Dim rngTemp As Range
    
    '"Sheet1"シートを対象にします。
    With Sheets("Sheet1")
        '最終行数を取得します。
        intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row

        For Each rngTemp In .Range(.Cells(2, 1), .Cells(intLastRowNum, 3))
            
            If rngTemp.Cells(1, 1) = 4 Then
                Exit For
            End If

            Debug.Print rngTemp.Cells(1, 1).Value
            
        Next
        
    End With

End Sub

VBAを実行する

VBAを実行してみましょう。

5より前の値が表示されましたね！

1
A
100
2
B
110
3
C
120
4
D
130

VBAの説明

VBAについて説明をします。

IFで変数の値が5であればExit Forで繰り返し処理を抜ける設定をしています。

If rngTemp.Cells(1, 1) = 5 Then
Exit For
End If

For Each…Next ステートメント+値を代入した配列で処理するパターン③

VBAコード

For Each…Next ステートメント+値を代入した配列で処理するパターンとなります。

サンプルのVBAコードは以下の通りです。

Sub ForEachでセルの値を取得③()
    Dim intLastRowNum As Integer
    Dim arrTemp As Variant
    Dim varTemp As Variant

    
    '"Sheet1"シートを対象にします。
    With Sheets("Sheet1")
        '最終行数を取得します。
        intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row
        
        'セル範囲を二次元配列として配列に代入します。
        arrTemp = .Range(.Cells(2, 1), .Cells(intLastRowNum, 3))

        For Each varTemp In arrTemp
            Debug.Print varTemp
        Next

    End With

End Sub

VBAを実行する

VBAを実行してみましょう。

イミディエイトウィンドウにセルの値が表示されましたね！

1
2
3
4
5
6
7
A
B
C
D
E
F
G
100
110
120
130
140
150
160

タカヒロ
タカヒロ
For Each…Next ステートメントで配列を処理する場合は１列目の行をすべて出力した後次の列に行く順序となります。

VBAの説明

VBAについて説明をします。

“Sheet1″シートを対象にします。

With Sheets("Sheet1")

対象の表のA列最終行数を取得します。

intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row

セル範囲を二次元配列として配列に代入します。

arrTemp = .Range(.Cells(2, 1), .Cells(intLastRowNum, 3))

For Each…Next ステートメントで配列arrTempを１要素づつ取得し、変数varTempに代入していきます。
配列arrTempは二次元配列となっており、その場合は１列目から処理が開始され、２列、最終列とそれぞれ配列要素数分処理を繰り返していきます。

For Each varTemp In arrTemp
Next

変数の値を出力していきます。

Debug.Print varTemp

タカヒロ
タカヒロ
変数に様々な形式の値が入るため、形式はVariant型にしましょう。

For Each…Next ステートメントで取得したセルの値を別シートへ転記するパターン④

For Each…Next ステートメントで取得したセルの値を別シートへ転記するパターンとなります。
「Sheet2」シートを用意し、同じセル範囲へ「Sheet1」シートの値を転記していきます。

VBAコード

サンプルのVBAコードは以下の通りです。

Sub ForEachでセルの値を取得④()
    Dim intLastRowNum As Integer
    Dim rngTemp As Range
    Dim intColumn As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    '取得したい列数を指定します。C列までであれば3を指定します。
    intColumn = 4
    '開始行数を指定します。
    i = 2
    '開始列数を指定します。通常は1に指定してください。B列開始にするときは2を指定してください。
    j = 1
    k = j

    '"Sheet1"シートを対象にします。
    With Sheets("Sheet1")
        '最終行数を取得します。
        intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).Row

        For Each rngTemp In .Range(.Cells(2, 1), .Cells(intLastRowNum, intColumn))
            
            Sheets("Sheet2").Cells(i, k) = rngTemp.Cells(1, 1).Value
            
            If k = intColumn + j - 1 Then
                i = i + 1
                k = j
            Else
                k = k + 1
            End If
        Next
        
    End With

End Sub

VBAを設定する

取得したい列数を指定します。C列までであれば3を指定します。

intColumn = 3

開始行数を指定します。

i = 2

開始列数を指定します。通常は1に指定してください。B列開始にするときは2を指定してください。

j = 1 k = j

VBAを実行する

VBAを実行してみましょう。

「Sheet2」シートへ「Sheet1」シートの値が転記されていますね！

VBAの説明

VBAについて説明をします。

Sheets(“Sheet2”)のCellsオブジェクトへ「Sheet1」シートのセルの値を代入しています。

For Each rngTemp In .Range(.Cells(2, 1), .Cells(intLastRowNum, intColumn))

Sheets(“Sheet2”).Cells(i, k) = rngTemp.Cells(1, 1).Value

For Each…Next ステートメントは行/列まとめて繰り返し処理を行う特性がありますので、改行したい条件をIFで指定していきます。
IFの条件の内容は最終列になったら開始列に戻す内容となっています。

If k = intColumn + j - 1 Then
i = i + 1
k = j
Else
k = k + 1
End If

別シートの出力位置を変更する

別シートの出力位置をA列はじまりからB列はじまりへ、また２行目開始から３行名開始に変更をしてみましょう。

開始行数を指定します。

i = 3

開始列数を指定します。B列開始の2を指定します。

j = 2

VBAを実行してみましょう。

はい、指定の位置へ出力されましたね！

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

・VBAでFor Each…Nextステートメントを使いセルの値を取得したり、途中で抜ける方法
・VBAでFor Each…Nextステートメントで配列操作をする方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。







コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA ImageChange Image

RECOMMENDこちらの記事も人気です。