VBAの繰り返し処理？の方法

質問

VBAを勉強し始めた者です。
下記、やりたい処理があるのですがやり方が分からないのでアドバイス頂ければと思います。
よろしくお願いします。

　　　　A　　　　　　　B　　　　　　　C　　　　　　　D　　　　　　　E　　　　　　　F
１　発注単位　１月末在庫　２月使用数　２月注文数　２月末在庫　安全在庫
２　　　50　　　　　　500　　　　　　400　　　　　　　　　　 　=B2-C2+D2　　301
３

ある品種について２月末時点で３０１以上の在庫を持ちたい場合、
いくつ注文するか（D2セルに入る数値）を求める方法を教えてください。
ただし、注文する数は発注単位（５０）の倍数とします。

上記は１品種ですが、縦に色々な条件の品種が並んでいると想定してください。

自分のイメージはE2セルが３０１未満なら５０買う、５０買っても３０１未満なら
さらに５０買う、３０１を超えたら終了するというイメージです。
今回の場合、２５０買うという結論を導きたいです。

もっと簡単な方法があればそれでもかまいません。

回答No.2

nishi6 · ベストアンサー

質問の意図がよく分からないのですが、この条件なら算式で直接計算できます。

　セルD2: =MAX(CEILING(F2-(B2-C2),A2),0)

モジュールを書くとcalcCyumon1のようになりますが、わざわざ分かりにくくしたようです。ユーザー定義関数にするのも何か間が抜けた感じです。（関数で計算できるので）

シートのコードウィンドウに貼り付けています。

'// 直接計算
Sub calcCyumon1()
　　Dim rw As Long '行カウンタ

　　rw = 1
　　With Range("A1")
　　　　While .Offset(rw, 0) <> ""
　　　　　　.Offset(rw, 3) = Application.Max(Application.Ceiling( _
　　　　　　　　 .Offset(rw, 5) - (.Offset(rw, 1) - .Offset(rw, 2)), _
　　　　　　　　 .Offset(rw, 0)), 0)
　　　　　　rw = rw + 1
　　　　Wend
　　End With
End Sub

発注単位を条件を満たすまで追加するなら下のようになります。

マクロは実行する必要があるわけで、作り方によっては1件の再計算のために全件計算することになることもあります。この事例の場合は関数が便利に思えます。VBAの勉強のための事例ならまた別の話ですね。

'// 発注単位を追加
Sub calcCyumon2()
　　Dim rw As Long　　　　 '行カウンタ
　　Dim Zaiko As Double　　 '在庫
　　Dim Cyumon As Double　　'注文数

　　rw = 1
　　With Range("A1")
　　　　While .Offset(rw, 0) <> ""
　　　　　　Cyumon = 0
　　　　　　Zaiko = .Offset(rw, 1) - .Offset(rw, 2)
　　　　　　While Zaiko < .Offset(rw, 5) '安全在庫未満
　　　　　　　　Cyumon = Cyumon + .Offset(rw, 0)
　　　　　　　　Zaiko = Zaiko + .Offset(rw, 0)
　　　　　　Wend
　　　　　　.Offset(rw, 3) = Cyumon

　　　　　　rw = rw + 1
　　　　Wend
　　End With
End Sub

お礼

早速のご回答ありがとうございました。
エクセルも詳しくなく、CEILING関数も言われればそうかと思い出す感じです。
関数の方法で試してみます。

回答No.1

anmochi

こんな感じかな。

Public Sub KeisanChumonSu()
Dim y As Long
y = 2
Do While Me.Range("A" & CStr(y)).Value <> ""
Me.Range("D" & CStr(y)).Value = 0
Do While Me.Range("E" & CStr(y)).Value < Me.Range("F" & CStr(y)).Value
Me.Range("D" & CStr(y)).Value = Me.Range("D" & CStr(y)).Value + Me.Range("A" & CStr(y)).Value
Loop
y = y + 1
Loop
End Sub

各行を読んでもらえれば解説も要らないレベルだと思う。A列が0だと無限ループになるので対策を。
ByRef sht As Excel.Sheetを引数にしてMeをshtに変えれば同じフォーマットの複数シートで汎用的に使えそう。
Do While ～ Loopを使うのは当方の好みなのでWhile ～ WEndでもDo Until ～ Loopでもお好きなものを使ってください。

Excel VBAではセルの値の参照や設定って実は割りとコストが高い処理なのでこのマクロは結構時間がかかると思う。
改良するとしたら以下のような感じか。こちらはA列が0だとエラー停止するので勝手に1にするようにした。

Public Sub KeisanChumonSuSpeedy()
Dim y As Long
Dim tani As Long
Dim chumon As Long
Dim amari As Long
y = 2
Do While Me.Range("A" & CStr(y)).Value <> ""
tani = Me.Range("A" & CStr(y)).Value
If tani <= 0 Then
tani = 1
End If
chumon = Me.Range("F" & CStr(y)).Value - Me.Range("B" & CStr(y)).Value + Me.Range("C" & CStr(y)).Value
If chumon < 0 Then
chumon = 0
End If
amari = chumon Mod tani
If amari > 0 Then
chumon = chumon + tani
End If
Me.Range("D" & CStr(y)).Value = chumon - amari
y = y + 1
Loop
End Sub

VBAマクロは元々プログラミング言語ではなく、セルの計算式では難しいような処理を力技でがりがりする用途で生まれてきた事を考えると前者のように極めて単純なアルゴリズムでがりがり計算させる方が後で見て分かりやすいかも知れないね。

お礼

早速のご回答ありがとうございました。
まだまだ勉強不足ですが、頑張ってみます！