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行の場合、2行の場合、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
No.4ベストアンサー
- 回答日時:
以下のマクロを標準モジュールに登録してください。
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様、
この度は迅速な対応で、的確なプログラムをご指導いただき、ありがとうございました。
バッチリ処理が出来ました!!非常にシンプルで解りやすいプログラムで関心してしまいました。
また、私のミスで回答No.5の高度なプログラムを考えて頂く様なお手間を取らせてしまい、すみませんでした。
ということで、今回のベストアンサーは、あえて最初の回答No.4のシンプルで美的なプログラムにさせて頂きたいと思います。
どうもありがとうございました
No.5
- 回答日時:
データが不正なために発生しています。
不正な箇所を表示して止まるようにマクロを修正しました。そのデータを正しく修正してください。
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
No.2
- 回答日時:
>最終的には有効在庫数が求められれば良いので、他に何か良い方法があればご教授頂けると大変助かります。
②の方法でも可能です。
マクロで実行したあとに、
①の場合は、受注数を変えれば、有効在庫数にそれが即反映されますが、②の場合は、それが反映されません。
しかしながら、もう一度、マクロを実行すれば、有効在庫数が更新されるので、結果的に反映されます。
従って、今回①にするメリットを私自身、感じませんので、②の方法で良ければマクロの提供は可能です。
その場合、このシート名はなんでしょうか。Sheet1で良いですか。
No.1
- 回答日時:
確認なのですが、あなたが求めているのは、
①有効在庫を求める関数式をマクロで設定することでしょうか。
②マクロで有効在庫の値を設定する(関数式ではなく値そのもの)ことでしょうか。
どうも①のように見えるのですが、もし、①なら、そのようにしたい理由は何でしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) サブフォルダ(データ)にある複数の.xlsxファイルのSheet3のA2セルの値で01から左側をB2 2 2022/08/14 15:46
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Visual Basic(VBA) vbaでセルに入力したときに,その横にあるセルを保護し入力不可にするマクロを作りたいです。 2 2022/04/24 20:59
- Visual Basic(VBA) マクロで最終行を取得してコピーしたい 3 2022/04/06 19:07
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) エクセルのVBAにショートカットキーの割り当て 3 2022/07/13 14:19
- Excel(エクセル) 並べ替え、ソートの構文がわからない。 お世話になります。VBA超初心者です。 エクセルでワークシート 2 2023/06/28 21:00
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
- Excel(エクセル) エクセルマクロで教えてください 2 2022/05/04 09:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ヨドバシカメラのネット通販で...
-
在庫日付順に先入先出しをエク...
-
売上在庫の評価減後の取扱い
-
製造業ですが、廃棄処分(資材...
-
在庫評価単価について
-
在庫引当とは?
-
回転在庫の意味
-
海外現地法人に日本法人所有の...
-
マクロで最終行から上に検索を...
-
在庫管理について
-
エクセルで在庫管理(日付で制...
-
iPhone 15を昨日購入し、今日の...
-
Excelのマクロで品番から在庫数...
-
在庫計上とは?
-
エクセルで在庫表作成、数量が...
-
解き方がわかりません。力を貸...
-
VBA内でSQL(UPDATEをループ処...
-
売上原価がマイナスの場合の処...
-
在庫月数の求め方について
-
生産完了品と在庫限定品の違い
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
なんでレグザは大きい方が安い...
-
解き方がわかりません。力を貸...
-
製造業ですが、廃棄処分(資材...
-
マクロで最終行から上に検索を...
-
現在のセリアかダイソーで、SD...
-
生産完了品と在庫限定品の違い
-
在庫日付順に先入先出しをエク...
-
在庫引当とは?
-
在庫評価単価について
-
ACCESS2013でバーコードリーダ...
-
VBA内でSQL(UPDATEをループ処...
-
在庫計上とは?
-
エクセルで在庫表作成、数量が...
-
回転在庫の意味
-
コンビニに在庫確認の為に何度...
-
「数を把握する」って英語でな...
-
在庫月数の求め方について
-
iPhone 15を昨日購入し、今日の...
-
不良品を交換したいけど在庫が...
-
在庫にかかる税金
おすすめ情報
①になるかと思います。
毎日、データベースから前日の受注数などが纏められた表が送られてきて、有効在庫数が計算されていないので、毎朝、手作業で有効在庫数を求め、生産部へ情報をフィードバックしています。
非常に手間がかかる作業なのでマクロで1発で自動的に処理が出来ないかと思い質問致しました。
最終的には有効在庫数が求められれば良いので、他に何か良い方法があればご教授頂けると大変助かります。どうかよろしくおねがいします。
>その場合、このシート名はなんでしょうか。Sheet1で良いですか。
シート名は「在庫管理表」でお願いします。
宜しくお願い致します。
>A列が品名の行のD列に数字がありますが、これは無視して良いですか。
>D3=15 D6=39 の値です。
特に意味があるわけではありませんが、残しておいて頂けると助かります。
宜しくお願いします。
>'有効在庫=現在庫-受注数量(製品名の1行目)
>ws.Cells(row, "F").Value = ws.Cells(row, "E").Value - ws.Cells(row, "D").Value
上記の文で、
実行時エラー’13’:
型が一致しません。
と言うエラーが出てしまうのですが、どうしたら良いでしょうか?
すみません。私が間違えていました。
元のファイルでは在庫データが始まるのが上から3行目でした。
ですので・・・
>For row = 2 To maxrow
を
For row = 3 To maxrow
に変えることで、バッチリ処理ができました。
すみませんでした。ありがとうございます。
すみません。私が間違えていました。
回答No.4のプログラムで正解でした。
元のファイルでは在庫データが始まるのが上から3行目でした。
ですので・・・
>For row = 2 To maxrow
を
For row = 3 To maxrow
に変えることで、バッチリ処理ができました。
余計な手間を取らせてしまい、すみませんでした。
また、回答No.5の高度なプログラムも今後の勉強のために
参考にさせて頂きたいと思います。ありがとうございました。