ちょっとした部品管理用シート。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
Sub SetStyle() Sheets("集計").Rows("2:1000").Delete Sheets("必要数").Range("F2:F18").Value = "" Sheets("必要数").Range("H2:H18").Value = "" Sheets("必要数").Range("J2:J18").Value = "" Sheets("必要数").Range("L2:L18").Value = "" End Sub Private Sub CommandButton1_Click() yn = MsgBox("集計シートはクリアされます。よろしいですか？", vbYesNo) If yn = vbNo Then Exit Sub Call SetStyle Set w = Sheets("リスト") r = 2 Do While w.Cells(r, 2).Value <> "" modelName = w.Cells(r, 2).Value qty = w.Cells(r, 4).Value Call RowsCopy(modelName, qty) r = r + 1 Loop Call MakeSummary Sheets("集計").Cells(1, 1).CurrentRegion.Borders.LineStyle = True Sheets("必要数").Activate End Sub Sub RowsCopy(modelName, qty) Set w = Sheets("部品") destRow = Sheets("集計").UsedRange.Rows.Count + 1 r = 2 Do While w.Cells(r, 1).Value <> "" If w.Cells(r, 1).Value = modelName Then w.Range("A" & r & ":G" & r & "").Copy Sheets("集計").Range("A" & destRow & ":G" & destRow & "") Sheets("集計").Range("G" & destRow & "").Value = Sheets("集計").Range("G" & destRow & "") * qty Sheets("集計").Range("H" & destRow & "").Value = "=E" & destRow & "*F" & destRow & "*G" & destRow & "" destRow = destRow + 1 End If r = r + 1 Loop End Sub Sub MakeSummary() Set w = Sheets("必要数") lastRow = Sheets("集計").UsedRange.Rows.Count For r = 2 To 18 w.Cells(r, 3).Value = "=SUMIFS(集計!H2:H" & lastRow & ",集計!C2:C" & lastRow & ",A" & r & ",集計!D2:D" & lastRow & ",B" & r & ")" w.Cells(r, 4).Value = "=SUMIFS(集計!G2:G" & lastRow & ",集計!C2:C" & lastRow & ",A" & r & ",集計!D2:D" & lastRow & ",B" & r & ")" Next r End Sub |