入れ子(ネスト)の繰り返し
Ifステートメントで”ABCストア”のみの金額計算をしました
今回は、”ABCストア”のみ右の表に書き出してみましょう
行は６行目、列は１１列目～書き出します
変数ｊは、右表の行番号を表す変数です
”ABCストア”の売上表を作成するときのみ使います
６行目から始まり、４項目書き出したら、
ｊ＝ｊ＋１ ← 次の行の計算 をします
データが終わるまで(今回は３０件)
For Next i で 繰り返します
この部分に注目！
列も 右辺は 4， 5， 6， 7 と１つずつ増え
左辺も 11，12，13，14 と１つずつ増えていますね
この部分もFor Nextを使えそうです！
For Nextの中にもう一つFor Nextを書きます
この状態を入れ子またはネストといいます
入れ子のFor Next
For
For
～
Next
Next
変数ｋを用意します
ｋは、項目の位置（列）を表します
★注意 j = j + 1の計算式は、For Nextの中に入れないでください
ｊは書き出すデータの行位置を表しますから、入れてしまうと、
１項目転記するたびに、行が加算されてしまいます
Sub Uriage_Keisan3()
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim K As Long '売上の項目の位置(列)
j = 6
For i = 3 To 32
Cells(i, 7) = Cells(i, 5) * Cells(i, 6) '金額計算
If Cells(i, 3) = "ABCストア" Then '店名がABCストアかどうか
'売上表に転記
For K = 4 To 7
Cells(j, K + 7) = Cells(i, K) '商品名～金額
Next K
j = j + 1
End If
Next i
End Sub別シートに表示
この売上表を別シートに書いてみましょう
ABCストアのシートを作ってね！
上の表のB5セルに１番目の商品名が入りますが、
B5セルと言っても、どのシートのB5セルなのか 書く必要があります
売上明細シートにもB5セルがありますから…
セル情報の前にシート名を指定します
WorkSheets(“ABCストア”).Range(“B5”).Value
シート名は ” ” でくくる！
Worksheetsの最後のｓを忘れずに！ 複数形にします
Sub Uriage_Keisan4()
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim K As Long '売上の項目の位置(列)
j = 5
For i = 3 To 32
Cells(i, 7) = Cells(i, 5) * Cells(i, 6) '金額計算
If Cells(i, 3) = "ABCストア" Then '店名がABCストアかどうか
'売上表に転記
For k = 4 To 7
Worksheets("ABCストア").Cells(j, k - 2) = Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
End Sub項目をABCストアシートへ
Worksheets(“ABCストア”).Cells(j, k – 2) = Cells(i, k)
SelectとActivate
ちょっと話変わるけど、SelectとActivateについてやっとくね！
むっちゃ大事です！
この状態で、B2～D6までが選択されています
選択範囲は、Range(“B2:D6“)です
この状態を、Range(“B2:D6”).Selectと書きます
複数セルが選択されていますが、アクティブなのは、
Range(“B2“)だけです
今この状態が、
Range(“B2”).Activateです
シート状でクリックしたとき、そのセルがアクティブになります
シートも同様に複数枚選択することができます
売上明細シートの選択は、
WorkSheets(“売上明細”).Select
複数シートが選択されていますが、アクティブなのは
WorkSheets(“売上明細”)だけです
今この状態が
WorkSheets(“売上明細”).Activateです
シートを表示させると、そのシートがアクティブです
つまり、セルでもシートでも
SelectはたくさんあるがActivateはひとつだけ！
ひとつだけ指定するときはSelectでもActivateでもOK！
話しを売上明細に戻して、
WorkSheets(“売上明細”).Activate
または、WorkSheets(“売上明細”).Selectを加えます
シート名が書かれていないセルは、Activateされたシートのセルとみなされます
Sub Uriage_Keisan4_2()
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim K As Long '売上の項目の位置(列)
Worksheets("売上明細").Activate
j = 5
For i = 3 To 32
Cells(i, 7) = Cells(i, 5) * Cells(i, 6) '金額計算
If Cells(i, 3) = "ABCストア" Then '店名がABCストアかどうか
'売上表に転記
For k = 4 To 7
Worksheets("ABCストア").Cells(j, k - 2) = Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
End SubSelect あるいは Activateするか
このようにシート名を指定します
Worksheets(“ABCストア”).Cells(j, k – 2) = Worksheets(“売上明細”).Cells(i, k)
Uriage_Keisan4で
Worksheets(“売上明細”).Activateなしで
Worksheets(“ABCストア”).Cells(j, k – 2) = Cells(i, k)
でもうまくいったけど？
それは、今見ているシートが売上明細シートだったからです
今見ているシートを対象にする場合、
あえてシート名を書かなくても、Activate（Select）しなくても
アクティブなっている（見ている）シートが対象とされます
↑
ここ大事！
シート名を書かない場合、他のシートがアクティブなっていると
予期せぬ結果になることがあります（他のシートが更新される）
Worksheets(“ABCストア”).Cells(j, k – 2) =WorkSheets(“売上明細”).Cells(i, k)
あるいは、
Worksheets(“売上明細”).Activateを加えます
売上明細シートがアクティブな場合は、
Worksheets(“ABCストア”).Cells(j, k – 2) =ActiveSheet.Cells(i, k)
と書くことができます
Uriage_Keisan4（Worksheets(“売上明細”).Activateなし）の場合
これらのセルにも、シート名の指定が必要です
Sub Uriage_Keisan5()
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim K As Long '売上の項目の位置(列)
j = 5
For i = 3 To 32
Worksheets("売上明細").Cells(i, 7) = Worksheets("売上明細").Cells(i, 5) * Worksheets("売上明細").Cells(i, 6) '金額計算
If Worksheets("売上明細").Cells(i, 3) = "ABCストア" Then '店名がABCストアかどうか
'売上表に転記
For k = 4 To 7
Worksheets("ABCストア").Cells(j, k - 2) = Worksheets("売上明細").Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
End SubWorksheets(“売上明細”)だらけで、みずらいですよね！
Withステートメント
記述が重複している場合は、Withステートメントを使ってみましょう
Range(“B2:D6”)が同じなので
With ～ End Withまで Range(“B2:D6”)が 同じですよ！と言う意味です
. ドット からは始まるものは With指定されたものが省略されています
Uriage＿Keisan５をWithステートメントを使って書き換えよう！
Sub Uriage_keisan6()
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim k As Long '売上の項目の位置(列)
With Worksheets("売上明細")
j = 5
For i = 3 To 32
.Cells(i, 7) = .Cells(i, 5) * .Cells(i, 6) '金額計算
If .Cells(i, 3) = "ABCストア" Then '店名がABCストアかどうか
'売上表に転記
For k = 4 To 7
Worksheets("ABCストア").Cells(j, k - 2) = .Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
End With
End Subスッキリしたね～
Withステートメントを使う方が、処理が早くなるそうです～
可読性も上がるし、修正も楽で、一石三鳥ですね！
３重ループ
店名一覧シートに店名が格納されています
３行目～７行目に店名が入っている
店名を店名一覧シートから取り出してMsgBoxで表示してみましょう
Sub Uriage_Keisan7()
Dim Tenmei As String '作成する店名 文字列
Dim Tenmei_No As Long '店名一覧の行数
For Tenmei_No = 3 To 7
Tenmei = Worksheets("店名一覧").Cells(Tenmei_No, 2)
MsgBox "店名は " & Tenmei
Next Tenmei_No
End Sub
各シートに店名別売上シートを作成しましょう
各店舗のシートを作成してください
シート名は、店名一覧シートのとおりにしてくださいね！
MsgBox “店名は ” & Tenmei のプログラムを
先ほど作成した、入れ子のFor Nextに差し替えます
Sub Uriage_Keisan8()
Dim Tenmei As String '作成する店名 文字列
Dim Tenmei_No As Long '店名一覧の行数
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim k As Long '売上の項目の位置(列)
With Worksheets("売上明細")
For Tenmei_No = 3 To 7
Tenmei = Worksheets("店名一覧").Cells(Tenmei_No, 2)
'MsgBoxの代わりに ここから
j = 5
For i = 3 To 32
.Cells(i, 7) = .Cells(i, 5) * .Cells(i, 6) '金額計算
If .Cells(i, 3) = Tenmei Then '店名がTenmei かどうか
'売上表に転記
For k = 4 To 7
Worksheets(Tenmei).Cells(j, k - 2) = .Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
'ここまで入れる
Next Tenmei_No
End With
End Subこの２行は、MsgBoxで表示した Tenmeiに変更してね！
If .Cells(i, 3) = Tenmei Then ‘店名がTenmei かどうか
Worksheets(Tenmei).Cells(j, k – 2) = .Cells(i, k) ‘Tenmeiシートに 商品名～金額
店名別売上シート作成できました～
最終行の取得
今回はデータの数があらかじめ30件とわかっていしましたが、
件数がわからない場合、あるいは、どんどん増えて行く場合は
どうしたらいいでしょうか？
Cells(3,3)からCtrl + ↓を押します
このキー操作を End(xlDown)
Cells(3, 3)からの操作では、
Cells(3, 3).End(xlDown)と書きます
最終行に移動できました
これで最終行が求められそうに思うのですが…
途中に空欄のセルがある場合、
Ctrl + ↓では空欄のセルの前が最終行となる
Cells(6,3)でカーソルがとまる
Ctrl + ↓でシートの最終行に移動してから、
Ctrl +↑とすれば、途中に空白行があっても最終行に移動できます
Ctrl + ↓
シートの一番下に移動
Ctrl +↑
表の最終行に移動
この操作を…
このように書きます（１列・A列の場合）
Rows.Countは、シートの行数
1048576行ですね！
End(xlUp)は、Ctrl+↑を押す操作
求めたいのはこの操作をした行数ですね！
Cells(Rows.Count, 1).End(xlUp).Row と書きます
Cells(Rows.Count, 1).End(xlUp).Value Or Cells(Rows.Count, 1).End(xlUp)
では値が求まってしまいますから…
最終行の書き方
売上データのFor Next の終了値を変更しましょう
For i = 3 To 32
↓
For i = 3 To .Cells(Rows.Count, 3).End(xlUp).Row
. を忘れないでね！
売上明細シートの最終行です！
Sub Uriage_Keisan9()
Dim Tenmei As String '作成する店名 文字列
Dim Tenmei_No As Long '店名一覧の行数
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim k As Long '売上の項目の位置(列)
With Worksheets("売上明細")
For Tenmei_No = 3 To 7
Tenmei = Worksheets("店名一覧").Cells(Tenmei_No, 2)
'MsgBoxの代わりに ここから
j = 5
For i = 3 To .Cells(Rows.Count, 3).End(xlUp).Row
.Cells(i, 7) = .Cells(i, 5) * .Cells(i, 6) '金額計算
If .Cells(i, 3) = Tenmei Then '店名がTenmei かどうか
'売上表に転記
For K = 4 To 7
Worksheets(Tenmei).Cells(j, K - 2) = .Cells(i, K) '商品名～金額
Next K
j = j + 1
End If
Next i
'ここまで入れる
Next Tenmei_No
End With
End Subあっという間に、実行してしまうので、MsgBoxで最終行を表示させてみましょう！
変数に代入するのも、いいと思います♪
Dim SaisyuGyo As Long ‘売上データの最終行
With Worksheets(“売上明細”)
SaisyuGyo = .Cells(Rows.Count, 3).End(xlUp).Row
MsgBox “最終行は… ” & SaisyuGyo
For i = 3 To SaisyuGyo
Sub Uriage_Keisan10()
Dim Tenmei As String '作成する店名 文字列
Dim Tenmei_No As Long '店名一覧の行数
Dim i As Long '売上データの行数
Dim j As Long '作成する売上表の行数
Dim k As Long '売上の項目の位置(列)
Dim SaisyuGyo As Long '売上データの最終行
With Worksheets("売上明細")
SaisyuGyo = .Cells(Rows.Count, 3).End(xlUp).Row
MsgBox "最終行は… " & SaisyuGyo
For Tenmei_No = 3 To 7
Tenmei = Worksheets("店名一覧").Cells(Tenmei_No, 2)
'MsgBoxの代わりに ここから
j = 5
For i = 3 To SaisyuGyo
.Cells(i, 7) = .Cells(i, 5) * .Cells(i, 6) '金額計算
If .Cells(i, 3) = Tenmei Then '店名がTenmei かどうか
'売上表に転記
For k = 4 To 7
Worksheets(Tenmei).Cells(j, k - 2) = .Cells(i, k) '商品名～金額
Next k
j = j + 1
End If
Next i
'ここまで入れる
Next Tenmei_No
End With
End Sub最終行取得方法理解できましたか？
Rows.CountとEnd(xlUp)の
意味だけは、理解しましょう！ あとは、コピペでOKですよ！
コメント