【Excel VBA】For Next文の使い方!抜ける方法や別シート転記も!

【Excel VBA】For Next文の使い方!抜ける方法や別シート転記も!

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

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

・VBAでFor…Nextステートメントを使いセルの値を取得したいが方法がよくわからない
・VBAでFor…Nextステートメントを使いセルの値を条件付きで取得したり別シートに転記したいが方法がわからず困っている

ですよね。

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

・VBAでFor…Nextステートメントを使いセルの値を取得したり、途中で抜ける方法
・VBAでFor…Nextステートメントでセルの値を別シートへ転記する方法

についてまとめます!

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

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

Excel側に表を作成します。

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

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

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

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

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

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

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

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

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

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

For counter = start To end step [ statements ] [ Exit For ] Next counter

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

パーツ 説明
counter 必須。 ループ カウンターとして使用する数値変数を指定します。 この変数には、ブール値または配列要素を指定できません。
start 必須です。?counter?の初期値を指定します。
end 必須。?counter?の最終値を指定します。
step オプション。 ループを繰り返すたびに、counter?の数が変更されます。 省略されると、step?は既定値の 0 になります。
statements 省略可能。 指定した回数実行される?For?と?Next?の間の 1 つ以上のステートメント。
Exit For For ループを抜けます。

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

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

VBAコード

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

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

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

        For i = 2 To intLastRowNum
            Debug.Print .Cells(i, 1).Value & "|" & .Cells(i, 2).Value & "|" & .Cells(i, 3).Value
        Next i
        
    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

VBAの説明

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

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

With Sheets("Sheet1")

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

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

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

For i = 2 To intLastRowNum

CellsオブジェクトのValueプロパティでセルの値を取得し、 “|”区切りで文字結合をして出力します。

Debug.Print .Cells(i, 1).Value & "|" & .Cells(i, 2).Value & "|" & .Cells(i, 3).Value

For…Next ステートメントで変数の増分をStepで設定するパターン②

VBAコード

For…Next ステートメントで変数の増分をStepで設定するパターンとなります。
サンプルではStepに2を指定して2つ刻みで繰り返し処理を行っています。

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

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

        For i = 2 To intLastRowNum Step 2
            Debug.Print .Cells(i, 1).Value & "|" & .Cells(i, 2).Value & "|" & .Cells(i, 3).Value
        Next i
        
    End With

End Sub

VBAを実行する

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

2つ刻みでセルの値が表示されましたね!

1|A|100
3|C|120
5|E|140
7|G|160

VBAの説明

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

Step 2とすることで2つ刻みで繰り返し処理を行います。

For i = 2 To intLastRowNum Step 2

For…Next ステートメントとStepで降順に処理するパターン②a

VBAコード

For…Next ステートメントとStepで降順に処理するパターンとなります。
サンプルではStepに-2を指定して2つ刻みで降順に繰り返し処理を行っています。

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

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

        For i = intLastRowNum To 2 Step -2
            Debug.Print .Cells(i, 1).Value & "|" & .Cells(i, 2).Value & "|" & .Cells(i, 3).Value
        Next i
        
    End With

End Sub

VBAを実行する

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

2つ刻みでセルの値が降順で表示されましたね!

7|G|160
5|E|140
3|C|120
1|A|100

VBAの説明

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

変数iに最終行数、Toに先頭行数を指定し、Step -2とすることで2つ刻みで繰り返し処理を行います。

For i = intLastRowNum To 2 Step -2

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

VBAコード

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

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

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

        For i = 2 To intLastRowNum
            
            If i = 5 Then
                Exit For
            End If

            Debug.Print .Cells(i, 1).Value & "|" & .Cells(i, 2).Value & "|" & .Cells(i, 3).Value
            
        Next i
        
    End With

End Sub

VBAを実行する

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

2つ刻みでセルの値が降順で表示されましたね!

1|A|100
2|B|110
3|C|120

VBAの説明

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

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

If i = 5 Then
Exit For
End If

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

VBAコード

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

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

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

        For i = LBound(arrTemp, 1) To UBound(arrTemp, 1)
        
            For j = LBound(arrTemp, 2) To UBound(arrTemp, 2)
            
                Debug.Print arrTemp(i, j)
        
            Next j
        Next i

    End With

End Sub

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).Row

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

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

For…Next ステートメントで配列のはじめのインデックスから最終インデックスまで繰り返し処理をおこないます。
Lbound関数はインデックスの最小値、Ubound関数は配列のインデックスの最大値を返します。

二次元配列となっているので、Lbound関数、Ubound関数に1次元、2次元を引数に指定し、それぞれの配列インデックス数分処理を繰り返しています。

For i = LBound(arrTemp, 1) To UBound(arrTemp, 1)

For j = LBound(arrTemp, 2) To UBound(arrTemp, 2)

二次元配列の値を出力していきます。

Debug.Print arrTemp(i, j)

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

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

同じセル範囲へ「Sheet1」シートの値を転記していきます。

VBAコード

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

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

        For i = 2 To intLastRowNum
             Sheets("Sheet2").Cells(i, 1) = .Cells(i, 1).Value
             Sheets("Sheet2").Cells(i, 2) = .Cells(i, 2).Value
             Sheets("Sheet2").Cells(i, 3) = .Cells(i, 3).Value
        Next i
        
    End With

End Sub

VBAを実行する

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

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

VBAの説明

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

Sheets("Sheet2").Cells(i, 1) = .Cells(i, 1).Value
Sheets("Sheet2").Cells(i, 2) = .Cells(i, 2).Value
Sheets("Sheet2").Cells(i, 3) = .Cells(i, 3).Value

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

・VBAでFor…Nextステートメントを使いセルの値を取得したり、途中で抜ける方法
・VBAでFor…Nextステートメントでセルの値を別シートへ転記する方法

についてまとめました。

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



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

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




タカヒロ

プログラミングやマーケティングなどに興味あるけど
どのスクールが良いか悩むときはないですか?

タカヒロもスクール選びにとても苦労しました…

そんな時には数ある中から第三者目線でいまの貴方に合ったスクールを提案してくれるスクマドのカウンセリングサービスがおすすめです。

メリットだけでなくディメリットもズバリと伝えてくれます!

これでお悩み時間を短縮できますね!詳細は☟になります。

※完全無料です。ご安心ください。





コメントを残す

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