simple様
誠に恐縮ではございますが、結果が以下のようになりました。
親CD 合計
111 16
222 21
本来は以下の結果にしたいのです。
親CD 合計
111 16
222 6
どの箇所を確認すればよいのかご教示をお願いできないでしょうか。
Sub test()
Dim k As Long
Dim dic1 As Object
Dim dic2 As Object
Dim total As Long
Dim parent As Long
Dim child As Long
Dim s1 As String
Dim s2 As String
Dim dicTotal As Object
'切断シートから所要時間を取得
Set dic1 = CreateObject("Scripting.Dictionary")
With Worksheets("切断")
For k = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
dic1(.Cells(k, "A").Value) = .Cells(k, "B").Value
Next
End With
'溶接シートから所要時間を取得
Set dic2 = CreateObject("Scripting.Dictionary")
With Worksheets("溶接")
For k = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
dic2(.Cells(k, "A").Value) = .Cells(k, "B").Value
Next
End With
'構成表シートをもとに合計時間を集計
Set dicTotal = CreateObject("Scripting.Dictionary")
With Worksheets("構成表")
For k = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
parent = .Cells(k, 1) '親CD
child = .Cells(k, 2) '子CD
s1 = .Cells(k, 3) '切断有無
s2 = .Cells(k, 4) '溶接有無
If s1 = "有" Then total = dic1(child)
If s2 = "有" Then total = total + dic2(child)
dicTotal(parent) = dicTotal(parent) + total
Next
'合計をシートに書き出す
.Range("F1:G1") = Array("親CD", "合計")
.Range("F2").Resize(dicTotal.Count, 1) _
= Application.Transpose(dicTotal.keys)
.Range("G2").Resize(dicTotal.Count, 1) _
= Application.Transpose(dicTotal.items)
End With
End Sub
大変申し訳ございませんがよろしくお願い致します。
お手数をお掛けします。
