エクセルVBAで、分岐がうまくできません。

質問

A,B,,Cのりんごとみかんの3種類の仕入れパターンがあり仕入の数量を算出したいですが、適正値が算出されません。
どのようにしたら、適正値を算出できるにのか教えてください。

Sub test()
Dim i As Integer

'A リンゴは、500以下になったら1000個になるように仕入
'A みかんは、500以下になったら1000個になるように仕入
'A みかんまたはりんごの片方が500以下になったらみかんとりんごを1000個になるように仕入
i = 2

Do While Worksheets("sheet1").Cells(i, 1) <> ""
If Cells(i, 1) = "A" And Cells(i, 2) <= 500 Or Cells(i, 3) <= 500 Then
Worksheets("sheet1").Cells(i, 4) = 1000 - Cells(i, 2)
Worksheets("sheet1").Cells(i, 5) = 1000 - Cells(i, 3)

'End If
'i = i + 1
'Loop

'B リンゴは、400以下になったら2000個になるように仕入
'B みかんは、400以下になったら2000個になるように仕入
'A みかんまたはりんごの片方が400以下になったらみかんとりんごを2000個になるように仕入

i = 2
'Do While Worksheets("sheet1").Cells(i, 1) <> ""
ElseIf Cells(i, 1) = "B" And Cells(i, 2) <= 400 Or Cells(i, 3) <= 400 Then
Worksheets("sheet1").Cells(i, 4) = 2000 - Cells(i, 2)
Worksheets("sheet1").Cells(i, 5) = 2000 - Cells(i, 3)

'End If
'i = i + 1
'Loop

''C リンゴは、300以下になったら3000個になるように仕入
''C みかんは、300以下になったら3000個になるように仕入
'A みかんまたはりんごの片方が300以下になったらみかんとりんごを3000個になるように仕入
i = 2
'Do While Worksheets("sheet1").Cells(i, 1) <> ""
ElseIf Cells(i, 1) = "C" And Cells(i, 2) <= 300 Or Cells(i, 3) <= 300 Then
Worksheets("sheet1").Cells(i, 4) = 3000 - Cells(i, 2)
Worksheets("sheet1").Cells(i, 5) = 3000 - Cells(i, 3)

End If
i = i + 1
Loop

End Sub

回答No.3

WindFaller · ベストアンサー

こんにちは。
#7748670の質問は適当に締めたほうがよいかもしれませんね。
前回の質問との違いということで、見比べてみると、

>みかんまたはりんごの片方が500以下になったらみかんとりんごを1000個になるように仕入

という項目で、マクロの内容をみて、「みかんとりんごを、それぞれ1000 個……」というように解釈しました。もちろん、ワークシートの数式でできますし、その方がよいかもしれません。マクロでは、通常では「戻し」が利きません。

'//
Sub PurchasePlanning()
　Dim i As Long
　Dim n As String
　With Worksheets("Sheet1")
　　'2 は、データ行の始まり
　　Application.ScreenUpdating = False
　　For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
　　　n = .Cells(i, 1).Value
　　　If n <> "" Then
　　　　Select Case n
　　　　　Case "A"
　　　　　　'A リンゴは、500以下になったら1000個
　　　　　　'A みかんは、500以下になったら1000個
　　　　　　' みかんまたはりんごの片方が500以下になったらみかんとりんごを1000個
　　　　　　If .Cells(i, 2).Value < 501 Or .Cells(i, 3).Value < 501 Then
　　　　　　　.Cells(i, 4).Value = 1000 - .Cells(i, 2).Value
　　　　　　　.Cells(i, 5).Value = 1000 - .Cells(i, 3).Value
　　　　　　End If
　　　　　Case "B"
　　　　　　'B リンゴは、400以下になったら2000個
　　　　　　'B みかんは、400以下になったら2000個
　　　　　　' みかんまたはりんごの片方が400以下になったらみかんとりんごを2000個
　　　　　　If .Cells(i, 2).Value < 401 Or .Cells(i, 3).Value < 401 Then
　　　　　　　.Cells(i, 4).Value = 2000 - .Cells(i, 2).Value
　　　　　　　.Cells(i, 5).Value = 2000 - .Cells(i, 3).Value
　　　　　　End If
　　　　　Case "C"
　　　　　　'C リンゴは、300以下になったら3000個
　　　　　　'C みかんは、300以下になったら3000個
　　　　　　If .Cells(i, 2).Value < 301 Or .Cells(i, 3).Value < 301 Then
　　　　　　　.Cells(i, 4).Value = 3000 - .Cells(i, 2).Value
　　　　　　　.Cells(i, 5).Value = 3000 - .Cells(i, 3).Value
　　　　　　End If
　　　　End Select
　　　End If
　　Next i
　　Application.ScreenUpdating = True
　End With
End Sub

お礼

大変助かりました。
VBA・数式のどちらを使うかの切り分けもまだできませんが、参考にさせてもらいます。
ありがとうございました。

回答No.2

jacob-wk9

プログラムの問題でしたら、Loopネスティングの中にi=2が2箇所出て来ていますので、これを外す必要があります。
セルＢ８のりんごの在庫はまだ、発注点に達していませんので、リンゴ仕入れのところはゼロにしなければいけません。

Cells(i, 2) > 400ならば発注数０　Cells(i, 3) > 400ならば発注数０を代入する必要があります。

ところで、この程度であれば、ＶＢＡで処理するまでもなく、式で表現できます。
マクロ付きＥＸＣＥＬファイルを配布し、受け取るときにセキュリティ警告が出る事から、配布の予定があれば
なるべくＶＢＡを使うのは最後の手段としたほうが良いように思います。

お礼

参考になりました。
ありがとうございます。

回答No.1

mu2011

変数ｉが「Ａ・Ｂ」の条件に一致した場合、常に初期値の「２」に戻されているのが原因ではないでしょうか。
又、Ｃｅｌｌｓプロパティでシートオブジェクトを省略、指定しているコードが紛らわしいので揃えませんか。

お礼

記述についての指摘は、注意していこう思います。
ありがとうございました。