在庫状況にもとづいて、売上表の行を削除するマクロを組みたいと思います。(2つのシートに分かれています)
<共通条件>
お客様Noは一意です。
お客様No2以降は他のアカウントで別の品名が入っています。
<作業内容>
お客様No2以降も同様に売上表シートの行を削除します。
お客様ごとに同じ品名で在庫状況シートの本数まで、売上表シートの在庫状況列の「No」の行を削除します。在庫状況シートの本数を超えると売上表シートの在庫状況列の「Yes」の行を削除します。
上記の作業はマクロで可能でしょうか。
下記に表を記載します。
<在庫状況>シート
お客様Noお客様名 品名本数在庫状況
1はなまる商事XYS Beta 5 Yes
2
3
4
5
<売上表>シート
お客様Noお客様名 品名本数在庫状況
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
1はなまる商事XYS Beta1Yes
<マクロ実行後の結果>
お客様Noお客様名品名本数在庫状況
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1Yes
1はなまる商事XYS Beta1No
※表がずれていますが、品名はアルファベットです。
No.6ベストアンサー
- 回答日時:
おはようございます。
期待通りに動いて良かったです。
しかし、質問に提示されたデータで動いても
実際の運用場面ではさまざまに予期せぬ動きをするかもしれません。
いろいろと条件を変えて試し、不具合が無いか確認してください。
前回のコードでは、
1)<売上表>シート の本数は全て1とは限らず、
Yesの行の数量を合計して判断するようにしています。
2)<売上表>シートのYesの数がそもそも、
<在庫状況>シート の数量より少ない場合には
<在庫状況>シート の数量に一致するように、
データを加えています。 この処理が不要なら教えてください。
さて、本題の同じお客様で複数の異なる製品がある場合ですが、
前回のコードを応用して、C列でもフィルターで絞り込むように
修正しました。
Sub test4()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim myLastRow1 As Long
Dim myLastRow2 As Long
Dim myCustomer As Long
Dim myStocks As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim flg As Boolean
Dim myRange As Range
Dim mySum As Long
Dim myGoods As String
Application.ScreenUpdating = False
Set Ws1 = Worksheets("在庫状況")
Set Ws2 = Worksheets("売上表")
myLastRow1 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row
With Ws2
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, _
Key3:=.Range("E1"), Order3:=xlDescending, _
Header:=xlGuess
For i = 2 To myLastRow1
myCustomer = Ws1.Cells(i, "A").Value
myGoods = Ws1.Cells(i, "C").Value
myStocks = Ws1.Cells(i, "D").Value
k = 0
mySum = 0
flg = False
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCustomer
.AutoFilter Field:=3, Criteria1:=myGoods
.AutoFilter Field:=5, Criteria1:="No"
End With
Set myRange = .Range("A1:A" & myLastRow2).SpecialCells(xlCellTypeVisible)
Set myRange = Intersect(.Range("A2:A" & myLastRow2), myRange)
If Not myRange Is Nothing Then myRange.EntireRow.Delete
If .AutoFilterMode Then .AutoFilterMode = False
myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row
With .Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCustomer
.AutoFilter Field:=3, Criteria1:=myGoods
.AutoFilter Field:=5, Criteria1:="Yes"
End With
Set myRange = .Range("D1:D" & myLastRow2).SpecialCells(xlCellTypeVisible)
Set myRange = Intersect(.Range("D2:D" & myLastRow2), myRange)
If Not myRange Is Nothing Then
mySum = Application.WorksheetFunction.Subtotal(9, myRange)
End If
If myStocks < mySum Then
With myRange
For j = 1 To .Cells.Count
k = .Cells(j).Value + k
If k > myStocks Then
If flg = False Then
.Cells(j).Value = .Cells(j).Value - k + myStocks
.Cells(j).EntireRow.Copy
Ws2.Rows(myLastRow2 + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
Ws2.Cells(myLastRow2 + 1, "D").Value = k - myStocks
Ws2.Cells(myLastRow2 + 1, "E").Value = "No"
myLastRow2 = myLastRow2 + 1
flg = True
Else
.Cells(j).Offset(, 1).Value = "No"
End If
ElseIf k = myStocks Then
flg = True
End If
Next j
End With
ElseIf mySum < myStocks Then
Ws1.Rows(i).EntireRow.Copy
.Rows(myLastRow2 + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
.Cells(myLastRow2 + 1, "D").Value = myStocks - mySum
myLastRow2 = myLastRow2 + 1
End If
Next i
.AutoFilterMode = False
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("C1"), Order2:=xlAscending, _
Key3:=.Range("E1"), Order3:=xlDescending, _
Header:=xlGuess
End With
Application.ScreenUpdating = True
Set Ws1 = Nothing
Set Ws2 = Nothing
Set myRange = Nothing
End Sub
'「世界でいちばん簡単なExcel VBAのe本」を購入されたのですね。
'私は他の本で勉強したあとに、この本に出会いましたが、
'最初にこれを読めば良かったなあと心から思っています。
' For Each...Next 文の解説が無いことだけが不満です。
ご回答ありがとうございます。実際の運用データを使用して試していきます。また、ご提供頂いたマクロを1文1文確認して理解していきます。
No.5
- 回答日時:
#2です。
作り直しました。問題無いか試してみてください。
問題がなければ、教えてください。
追加補足質問の(2)に移ります。
Sub test3()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim myLastRow1 As Long
Dim myLastRow2 As Long
Dim myCustomer As Long
Dim myStocks As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim flg As Boolean
Dim myRange As Range
Dim mySum As Long
Application.ScreenUpdating = False
Set Ws1 = Worksheets("在庫状況")
Set Ws2 = Worksheets("売上表")
myLastRow1 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row
With Ws2
For i = 2 To myLastRow1
myCustomer = Ws1.Cells(i, "A").Value
myStocks = Ws1.Cells(i, "D").Value
k = 0
mySum = 0
flg = False
If .AutoFilterMode Then .AutoFilterMode = False
With .Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCustomer
.AutoFilter Field:=5, Criteria1:="No"
End With
Set myRange = .Range("A1:A" & myLastRow2).SpecialCells(xlCellTypeVisible)
Set myRange = Intersect(.Range("A2:A" & myLastRow2), myRange)
If Not myRange Is Nothing Then myRange.EntireRow.Delete
If .AutoFilterMode Then .AutoFilterMode = False
myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row
With .Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:=myCustomer
.AutoFilter Field:=5, Criteria1:="Yes"
End With
Set myRange = .Range("A1:A" & myLastRow2).SpecialCells(xlCellTypeVisible)
Set myRange = Intersect(.Range("A2:A" & myLastRow2), myRange)
If Not myRange Is Nothing Then
mySum = Application.WorksheetFunction.Subtotal(9, myRange.Offset(, 3))
End If
If myStocks < mySum Then
With myRange.Offset(, 3)
For j = 1 To .Cells.Count
k = .Cells(j).Value + k
If k > myStocks Then
If flg = False Then
.Cells(j).Value = .Cells(j).Value - k + myStocks
.Cells(j).EntireRow.Copy
Ws2.Rows(myLastRow2 + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
Ws2.Cells(myLastRow2 + 1, "D").Value = k - myStocks
Ws2.Cells(myLastRow2 + 1, "E").Value = "No"
myLastRow2 = myLastRow2 + 1
flg = True
Else
.Cells(j).Offset(, 1).Value = "No"
End If
ElseIf k = myStocks Then
flg = True
End If
Next j
End With
ElseIf mySum < myStocks Then
Ws1.Rows(i).EntireRow.Copy
.Rows(myLastRow2 + 1).Insert Shift:=xlDown
Application.CutCopyMode = False
.Cells(myLastRow2 + 1, "D").Value = myStocks - mySum
myLastRow2 = myLastRow2 + 1
End If
Next i
.AutoFilterMode = False
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("E1"), Order2:=xlDescending, _
Header:=xlGuess
End With
Application.ScreenUpdating = True
Set Ws1 = Nothing
Set Ws2 = Nothing
Set myRange = Nothing
End Sub
この回答への補足
修正ありがとうございます。
内容を確認しました。
問題ございません。
引き続きよろしくお願いいたします。
別件ですが、先日ご紹介頂きました「世界でいちばん簡単なExcel VBAのe本」を購入しました。代入やカウンタ変数の箇所はとてもわかりやすく理解できました。基本文法は何度も繰り返し勉強して理解を深めたいと思います。
No.4
- 回答日時:
2点確認させてください。
1)<売上表>シート の本数は全て1なのですか?
<在庫状況>シート の本数が5の場合、
<売上表>シートのYesを5行残せばよいのですか?
それとも、Yesの行の数量を合計して5になるように残すのですか?
2) <売上表>シート のNoの行は最初に全て削除して
その後、Yesの数を<在庫状況>シート の数量に
あわせて、余分が出ればNoに書き換える
ということですか?
また、
<売上表>シートのYesの数がそもそも、
<在庫状況>シート の数量より少ない場合などは無いのでしょうか?
尚、回答に対する返信は明日以降になると思います。
この回答への補足
1)<売上表>シートの本数は全て1ではございません。
2であったり10であったりいろいろです。
<売上表>シートのYesを5行残してください。
2)<売上表>シートのYesの数が、<在庫状況>シート の数量より少ない場合はございます。
No.3
- 回答日時:
こんばんは。
以下の条件で作ったつもりです。
>お客様ごとに同じ品名で在庫状況シートの本数まで、売上表シートの在庫状況列の「No」の行を削除します。在庫状況シートの本数を超えると売上表シートの在庫状況列の「Yes」の行を削除します。
ご提示の<売上表>シート の例では、
Yesが6行、Noが6行あります。
ご提示の<在庫状況>シートの例では、
本数が5
となっていますので、
まず、Noを5行削除します。
Noが5行削除できたので、コレで終了。
もし、
ご提示の<在庫状況>シートの例で、
本数が7
であれば、
まず、Noを6行削除します。
削除するNoの行が無くなったので、Yesの行を1行削除。
おそらく、質問文に対する私の解釈が間違っているのだと思います。
もう少し、具体的にどういう動きをすれば良いのか補足説明をお願いします。
尚、追加質問の(2)はこれが解決してから考えます。
この回答への補足
説明が足りない部分があり、大変申し訳ございません。
在庫数:5本の場合
<売上表>シートの在庫状況列(E列)のYesを5個残すイメージです。
Noを6行削除します。
Yesが1行多いので、これは<売上表>シートの在庫状況列(E列)を
のYesをNoにします。
※<売上表>シートの在庫状況列(E列)のNoとYesは、どちらかの行を
削除するため、12行あれば6行残ります。
ただし上記のように、<在庫状況>シートの本数列(D列)を<売上表>シートの在庫状況列(E列)Yesの最大数としていますので、YesをNoに変更する作業が必要となります。
よろしくお願いいたします。
No.2
- 回答日時:
こんばんは。
2つのシートのどちらにも、見出し行がある前提で作りました。
Sub test1()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim myLastRow1 As Long
Dim myLastRow2 As Long
Dim myCustomer As Long
Dim myStocks As Long
Dim i As Long
Dim j As Long
Dim k As Long
Application.ScreenUpdating = False
Set Ws1 = Worksheets("在庫状況")
Set Ws2 = Worksheets("売上表")
myLastRow1 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
myLastRow2 = Ws2.Cells(Ws2.Rows.Count, "A").End(xlUp).Row
For i = 2 To myLastRow1
myCustomer = Ws1.Cells(i, "A").Value
myStocks = Ws1.Cells(i, "D").Value
k = 0
With Ws2
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("E1"), Order2:=xlDescending, _
Header:=xlGuess
For j = myLastRow2 To 2 Step -1
If .Cells(j, "A").Value = myCustomer Then
k = .Cells(j, "D").Value + k
If k <= myStocks Then
.Rows(j).Delete
Else
.Cells(j, "D").Value = k - myStocks
Exit For
End If
End If
Next j
End With
Next i
Application.ScreenUpdating = True
Set Ws1 = Nothing
Set Ws2 = Nothing
End Sub
この回答への補足
テストサンプルいつもありがとうございます。勉強になります。
2点確認させてください。
(1)マクロ実行後の結果ですが、在庫状況Yesが6個とNoが1個になっています。在庫状況Yesを5個、Noを1個にしたいのですが可能でしょうか。
(2)同じお客様で複数の異なる製品がございます。その場合も同様に製品ごとに処理したいと思います。在庫表シートと売上表シートのいずれもC列に条件を付ける必要があると思います。こちらもサンプルがありましたらお教えください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) 【関数】【マクロ】データの転記の方法について 2 2023/07/26 15:22
- Visual Basic(VBA) 【ExcelVBA】Powerクエリーでいうピボット解除と同じ処理をVBAで 4 2022/07/06 17:09
- Excel(エクセル) 前の(左隣の)シートを連続参照するように、あとから変更したい 1 2023/02/22 00:51
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- Excel(エクセル) vba シート名の一覧を2列に分けるには 5 2023/04/24 08:56
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの複数シートの保護を...
-
Excelで同じシートのコピーを一...
-
EXCELで1ヶ月分の連続した日付...
-
Excelで金銭出納帳。繰越残高を...
-
別シート参照のセルをシート毎...
-
エクセルVBAでパスの¥マークに...
-
VBAでシートコピー後、シート名...
-
スプレッドシートの関数VLOOKUP...
-
エクセル(VBA)でリストボック...
-
前の(左隣の)シートを連続参...
-
複数のシートにまたがるデータ...
-
エクセルでファイル保存時に複...
-
基本となるシートをコピーした...
-
複数のピボットを同じフィルタ...
-
シートの保護のあとセルの列、...
-
EXCEL:同じセルへどんどん足し...
-
エクセルif関数で、複数のシー...
-
エクセルで前のシートを連続参...
-
全シートを選択し、それぞれ特...
-
別シートの最終行に貼り付けす...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelで同じシートのコピーを一...
-
エクセルの複数シートの保護を...
-
Excelで金銭出納帳。繰越残高を...
-
エクセルでファイルを開いたと...
-
EXCELで1ヶ月分の連続した日付...
-
エクセルVBAでパスの¥マークに...
-
EXCEL:同じセルへどんどん足し...
-
シートの保護のあとセルの列、...
-
別シート参照のセルをシート毎...
-
エクセルで前のシートを連続参...
-
前の(左隣の)シートを連続参...
-
EXCELで同一フォーマットのシー...
-
VBAでシートコピー後、シート名...
-
Excel 連番を入力する方法
-
エクセル 計算式も入っていない...
-
エクセルで前シートを参照して...
-
エクセルでシート名を自動入力...
-
Accessのスプレッドシートエク...
-
複数シートの特定の位置に連番...
-
エクセルのシート名をリスト化...
おすすめ情報