初心者のためのExcel エクセルマクロVBA入門-実践：繰り返し処理（発注書マクロ）
繰り返し処理で一気に処理を行う！
前回までのプログラムです。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 ' 在庫表シートの仕入れ先の列から会社名を検索 Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) ' 検索結果があれば・・・ If Not rngFindResult Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(11, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(11, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(11, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(11, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) End If End If ' 最後まで検索しきるまで繰り返す。 End Sub
さて、後は最後の日本語なのですね。
最後まで検索しきるまで繰り返す。
ですね。これだけでは実は不十分なのです。この日本語はかなり多くの処理を含んでいます。
( ﾟдﾟ)？ドユコト？
ですよね。最後まで検索しきる！と簡単に書いてますが、いくつか疑問があるのです。
- エクセルで検索するとずーっと検索し続けるのだけど？
- エクセルで検索すると必ず1番最初が検索されるけど？
- 最後までって具体的にどこ？
- 繰り返すって具体的に何回繰り返すの？
などなどがあります。少なくともこれらの疑問を解決しないと繰り返し処理に翻訳できないわけです。
Findは実行するたび、セルの頭から検索する（引数After）
ですね、普通にCtrl＋Fを押して検索すると、必ず1番最初に検索されたセルへ移動します。おそらく日本語化するためにエクセルの手作業で最初にやった時には何度も検索ボタンを押したはずです。連続の検索をすると、検索結果の次のセルから検索してくれます。これをVBAでやるには、ここでで書いたように引数にAfterを追加すればよいのです。ですので検索文は・・・
' 在庫表シートの仕入れ先の列から会社名を検索 Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole, Afer:=rngFindResult)
となります。
検索結果が最初のセルになるまで繰り返す
具体的に繰り返す回数についてですが、これは当然不明ですよね？1000件以上ある在庫表で特定の仕入れ先の商品が「何個」あるかわからないです。ですので、今回はこの条件を使うわけです。Find関数の特徴をうまく利用してあげます。
Find関数は最後まで検索し終えたらまたはじめに戻る
この特徴を利用すると、「検索結果が最初のセルになるまで繰り返す」ということになります。さらに翻訳すると、検索結果が最初に検索したRangeオブジェクトになるまでずっと繰り返す。ということです。
「～するまでずっと」は繰り返しのDo Untilを使えばよさそうですね。またはIf文を利用して条件を満たしたらExit Doで繰り返しを終了でもよさそうです。
ですので、ここでちょっと立ち戻って改めて日本語にしてみます。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 ' 最初の検索結果を取得する ' 検索結果があればさっきの処理をする ' 繰り返しをスタートする ' 在庫表シートの仕入れ先の列から会社名を検索（検索結果の次のセルから） Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) ' 検索結果があれば・・・ If Not rngFindResult Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(11, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(11, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(11, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(11, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) End If End If ' 検索結果が最初のRangeオブジェクトになるまでずっと End Sub
とこうなります。では改めて「翻訳」です。結果はこうなりました。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 引数：corpName（会社名） ' 会社名に該当して、発注が必要な商品をリストアップする ' メインの処理 ' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub outputOrderProduct(ByVal corpName As String) '変数の宣言 Dim rngFindResultFirst As Range ' 1回目の検索結果を保持用 Dim rngFindResult As Range ' 仕入れ先検索結果格納用 Dim lngStock As Long ' 現在の在庫数 Dim lngNeedOrder As Long ' 必要在庫数 Dim outputRow As Long ' 発注リストの出力行 outputRow = 11 ' 最初の検索結果を取得する Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole) Set rngFindResultFirst = rngFindResult ' 検索結果があればさっきの処理をする If Not rngFindResultFirst Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If End If ' 繰り返しをスタートする Do ' 在庫表シートの仕入れ先の列から会社名を検索（検索結果の次のセルから） Set rngFindResult = shtStock.Range("F:F").Find(What:=corpName, LookAt:=xlWhole, After:=rngFindResult) ' 検索結果があれば・・・ If Not rngFindResult Is Nothing Then ' 検索結果の行の商品在庫と必要在庫数を比較 lngStock = shtStock.Cells(rngFindResult.Row, "D").Value lngNeedOrder = shtStock.Cells(rngFindResult.Row, "E").Value ' 在庫 <= 必要在庫数 If lngStock <= lngNeedOrder Then ' その行で必要な情報を発注リスト表へ出力する ' No shtOrderTool.Cells(outputRow, "B").Value = shtStock.Cells(rngFindResult.Row, "A").Value ' 商品名 shtOrderTool.Cells(outputRow, "C").Value = shtStock.Cells(rngFindResult.Row, "B").Value ' 現在庫 shtOrderTool.Cells(outputRow, "D").Value = lngStock ' 仕入れ値＝価格*0.7の切り捨て shtOrderTool.Cells(outputRow, "E").Value = Int(shtStock.Cells(rngFindResult.Row, "C").Value * 0.7) outputRow = outputRow + 1 End If End If ' 検索結果が最初のRangeオブジェクトになるまでずっと Loop Until rngFindResult.Address = rngFindResultFirst.Address End Sub
これで翻訳は完了です。新しく宣言した変数はrngFindResultFirstとoutputRowですね。それぞれ、最初の検索結果をとっておく変数と発注リストへ出力する時に同じ行ではなくて、1つ出力したら次の行に出力したいので、その行を管理する変数です。
後は、日本語で書いてあることを翻訳しました。
これで完成？でしょうか？
いえいえ、実は違います。この後に、プログラムの整理とデバッグが待っています。それはまた次回で！なかなか完成しなくてもやもやですよね。このブログはプログラムを「見せて終わり」ではありません。プログラムを作る過程を疑似体験しながら、理解してもらうためのブログです。できればみなさんも同じようにやってみて、先取りしてみるといいと思います。
今日はここまで！
かしこ
