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 SubVBAの実装
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 
VBAの説明
VBAについて説明をします。
“Sheet1″シートを対象にします。
With Sheets("Sheet1")対象の表のA列最終行数を取得します。
intLastRowNum = .Cells(Rows.Count, 1).End(xlUp).RowFor 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 SubVBAを実行する
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 IfFor 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 SubVBAを実行する
VBAを実行してみましょう。
イミディエイトウィンドウにセルの値が表示されましたね！
1
2
3
4
5
6
7
A
B
C
D
E
F
G
100
110
120
130
140
150
160
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は二次元配列となっており、その場合は１列目から処理が開始され、２列、最終列とそれぞれ配列要素数分処理を繰り返していきます。
Next
変数の値を出力していきます。
Debug.Print varTemp
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 SubVBAを設定する
取得したい列数を指定します。C列までであれば3を指定します。
intColumn = 3開始行数を指定します。
i = 2開始列数を指定します。通常は1に指定してください。B列開始にするときは2を指定してください。
j = 1 k = jVBAを実行する
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 = 2VBAを実行してみましょう。
はい、指定の位置へ出力されましたね！
VBAの実装手順
実装手順は以下の通りです。
Excel側にVBAを実装していきます。
①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。
②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。
③右ペインのウインドウに上記のVBAを入力します。
こちらで完了です。
VBAを実行する
では早速VBAの実行をしてみましょう。
①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。
②処理がされたことが確認できれば完了です。
さいごに
いかがでしょうか。
今回は、
・VBAでFor Each…Nextステートメントで配列操作をする方法
についてまとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
この記事の関連キーワード
「ループ」の関連記事
こちらの記事の内容「ループ」に関連する記事一覧です。
コメントを残す