Excel VBA 在庫管理表式の入力を繰り返す

Question

Excel VBA 在庫管理表　式の入力を繰り返す

簡単な式を繰り返し入力し、自動で最終行まで有効在庫数を求めるマクロを作りたいのですが、なかなか出来ず困っています。
VBAの知識をお持ちの方，お力をかして頂けないでしょうか？よろしくお願いします。

・製品の受注数と在庫数、有効在庫数の管理表があります。
・各製品名毎に受注日と受注数がまとめられていて、それぞれ1行又は複数行になります。
・各製品名と製品名の間に空白セルが入ります。(←繰り返す際、区切りとして利用出来ないか？)
・A列には各製品名と製品名の間に｢品名｣が入ります。(←繰り返す際、これも利用出来ないか？)
・ここでF列の有効在庫欄にマクロで自動的に次の簡単な式を入れて、有効在庫を計算で導き出したいのですが･･･。

有効在庫セルに…(添付図参照下さい)
　(1) 1行目 ＝現在庫－受注数
　(2) 2行目 ＝1つ上の有効在庫－受注数(例：＝F4－D5)
　(3) 3行目以降 2行目の式を繰り返す

以上の式を最後の行(数千行)まで繰り返す命令を作りたいのですが･･･問題は製品名によって行数が違うことと、各製品名毎の間に空白行(セル)(←上手く区切りで使えるのかも…)が入るのですが、どの様に考えればいいでしょうか?

繰返しの構文は…
　  Do While
　  Loop
などを使えば良いと思うのですが、中身の命令文をどうしたら良いか分かりません。

例えば･･･
・A3から下に見ていって｢品名｣という言葉が出てくるまで行数をカウント。
・行数が1行の場合(1)の式、2行の場合(2)の式、3行以上･･･の場合で場合分け(Case文)

などとすれば良いのかなと思いますが、VBA初心者のため、どの様に命令文を書いたらいいか分らず困っています。

私は関数は多少扱えますが、VBAについては初心者の状態から今勉強中といったところです。
是非皆様のお力を貸して下さい！よろしくお願い致します。

※因みに現状は、1行の場合、２行の場合、3行以上の場合とそれぞれマクロ(の記録)を作って半手作業で対応していますので非常に手間が掛かっています。

Option Explicit
Sub Macro1セル3()
' Macro1 Macro
' Keyboard Shortcut: Ctrl+c
'
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-4]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-RC[-4]"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
 End Sub
 
Sub Macro2セル1()
' Macro2 Macro
' Keyboard Shortcut: Ctrl+z
'
    ActiveCell.Offset(0, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-4]"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
 
Sub Macroセル2()
' Macro3 Macro
' Keyboard Shortcut: Ctrl+x
'
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-4]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-RC[-4]"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

tatsu99 · Accepted Answer

以下のマクロを標準モジュールに登録してください。
Option Explicit
Public Sub 有効在庫設定()
    Dim ws As Worksheet
    Dim maxrow, row As Long
    Dim newname, oldname As String
    Set ws = Worksheets("在庫管理表")
    maxrow = ws.Cells(Rows.Count, "B").End(xlUp).row    'sheet B列の最大行取得
    oldname = ""
    For row = 2 To maxrow
        newname = ws.Cells(row, "B")
        If newname <> "" Then
            If newname = oldname Then
                '有効在庫=前行有効在庫-受注数量（製品名の2行目以降）
                ws.Cells(row, "F").Value = ws.Cells(row - 1, "F").Value - ws.Cells(row, "D").Value
            Else
                '有効在庫=現在庫-受注数量（製品名の1行目）
                ws.Cells(row, "F").Value = ws.Cells(row, "E").Value - ws.Cells(row, "D").Value
            End If
            oldname = newname
        End If
    Next
    MsgBox ("処理完了")
End Sub

tatsu99 · Answer

データが不正なために発生しています。
不正な箇所を表示して止まるようにマクロを修正しました。そのデータを正しく修正してください。

Option Explicit
Public Sub 有効在庫設定()
    Dim ws As Worksheet
    Dim maxrow, row As Long
    Dim newname, oldname As String
    Dim juchu, zaiko As Variant
    Set ws = Worksheets("在庫管理表")
    maxrow = ws.Cells(Rows.Count, "B").End(xlUp).row    'sheet B列の最大行取得
    oldname = ""
    For row = 2 To maxrow
        newname = ws.Cells(row, "B")
        If newname <> "" Then
            If newname = oldname Then
                '有効在庫=前行有効在庫-受注数量（製品名の2行目以降）
                juchu = ws.Cells(row, "D").Value
                If IsNumeric(juchu) = False Then
                    MsgBox ("受注数量不正:行番号=" & row)
                    MsgBox (juchu)
                    ws.Cells(row, "D").Select
                    End
                End If
                ws.Cells(row, "F").Value = ws.Cells(row - 1, "F").Value - ws.Cells(row, "D").Value
            Else
                '有効在庫=現在庫-受注数量（製品名の1行目）
                juchu = ws.Cells(row, "D").Value
                zaiko = ws.Cells(row, "E").Value
                If IsNumeric(juchu) = False Then
                    MsgBox ("受注数量不正:行番号=" & row)
                    MsgBox (juchu)
                    ws.Cells(row, "D").Select
                    End
                End If
                If IsNumeric(zaiko) = False Then
                    MsgBox ("現在庫不正:行番号=" & row)
                    MsgBox (zaiko)
                    ws.Cells(row, "E").Select
                    End
                End If
                ws.Cells(row, "F").Value = ws.Cells(row, "E").Value - ws.Cells(row, "D").Value
            End If
            oldname = newname
        End If
    Next
    MsgBox ("処理完了")
End Sub

tatsu99 · Answer

マクロ作成時の補足要求です。
A列が品名の行のD列に数字がありますが、これは無視して良いですか。
D3＝１５　D6=３９　の値です。

tatsu99 · Answer

>最終的には有効在庫数が求められれば良いので、他に何か良い方法があればご教授頂けると大変助かります。
②の方法でも可能です。
マクロで実行したあとに、
①の場合は、受注数を変えれば、有効在庫数にそれが即反映されますが、②の場合は、それが反映されません。
しかしながら、もう一度、マクロを実行すれば、有効在庫数が更新されるので、結果的に反映されます。

従って、今回①にするメリットを私自身、感じませんので、②の方法で良ければマクロの提供は可能です。
その場合、このシート名はなんでしょうか。Sheet1で良いですか。

tatsu99 · Answer

確認なのですが、あなたが求めているのは、
①有効在庫を求める関数式をマクロで設定することでしょうか。
②マクロで有効在庫の値を設定する（関数式ではなく値そのもの）ことでしょうか。

どうも①のように見えるのですが、もし、①なら、そのようにしたい理由は何でしょうか。

Excel VBA 在庫管理表 式の入力を繰り返す

以下のマクロを標準モジュールに登録してください。

データが不正なために発生しています。

マクロ作成時の補足要求です。

>最終的には有効在庫数が求められれば良いので、他に何か良い方法があればご教授頂けると大変助かります。

確認なのですが、あなたが求めているのは、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

Excel VBA 在庫管理表式の入力を繰り返す