|
今天因为给论坛里关于BOM的写了个代码,回头看这个贴,也补一下
- Private vData As Variant, nRow As Long, nCol As Long
- Private dicItem As Object, dicParent As Object
- Sub BOM统计()
- Dim nVal As Double, vItem As Variant
-
- Application.ScreenUpdating = False
- Set dicItem = CreateObject("Scripting.Dictionary") 'BOM表项目值
- Set dicParent = CreateObject("Scripting.Dictionary") 'BOM表子项对应的父项
- With Sheet1
- nRow = .Cells(.Rows.Count, 2).Row
- vData = .[A2:B2].Resize(nRow - 1, 5).Value
- ReDim Preserve vData(1 To UBound(vData), 1 To 5 + 1) '扩展一列记录子项总列表
- For nRow = 1 To UBound(vData)
- If vData(nRow, 2) <> "" Then
- If UBound(vData, 2) - 6 < vData(nRow, 1) + 1 Then '记录子项值层数不足
- ReDim Preserve vData(1 To UBound(vData), 1 To 6 + vData(nRow, 1) + 1)
- End If
- For nCol = 0 To vData(nRow, 1) '记录各层子项
- If nCol <> vData(nRow, 1) Then
- vData(nRow, 7 + nCol) = vData(nRow - 1, 7 + nCol)
- Else
- vData(nRow, 7 + nCol) = vData(nRow, 2)
- End If
- If vData(nRow, 6) <> "" Then vData(nRow, 6) = vData(nRow, 6) & "|"
- vData(nRow, 6) = vData(nRow, 6) & vData(nRow, 7 + nCol)
- Next
- SetDic
- End If
- Next
-
- For Each vItem In dicItem.Keys
- Do While dicParent.Exists(vItem)
- nVal = dicItem(vItem)("用量") * dicItem(vItem)("单价")
- vItem = dicParent(vItem)
- If Not dicItem.Exists(vItem) Then
- Set dicItem(vItem) = CreateObject("Scripting.Dictionary")
- dicItem(vItem)("用量") = 1
- End If
- dicItem(vItem)("单价") = dicItem(vItem)("单价") + nVal
- Loop
- Next
- For nRow = 1 To UBound(vData)
- vItem = Trim(vData(nRow, 6))
- If vItem <> "" Then
- vData(nRow, 4) = dicItem(vItem)("单价")
- vData(nRow, 5) = (dicItem(vItem)("用量") - 1 * (dicItem(vItem)("用量") = 0)) * dicItem(vItem)("单价")
- End If
- Next
- .[A2:E2].Resize(UBound(vData), 5) = vData
- End With
- Application.ScreenUpdating = True
- End Sub
- Private Function SetDic(Optional ByVal Level As Long = 7, Optional ByVal Parent As Variant = Empty)
- Dim vKey As Variant, vSon As Variant
-
- vKey = vData(nRow, Level) '如果是第7列,则为顶父级
-
- If Parent <> "" Then vSon = Parent & "|"
- vSon = vSon & vKey
- If Parent <> "" Then dicParent(vSon) = Parent
-
- If Level - 7 = vData(nRow, 1) Then
- Set dicItem(vSon) = CreateObject("Scripting.Dictionary")
- dicItem(vSon)("用量") = Val(vData(nRow, 3))
- dicItem(vSon)("单价") = Val(vData(nRow, 4))
- Else
- If dicItem.Exists(vSon) Then If dicItem(vSon).Exists("单价") Then dicItem(vSon).Remove "单价"
- SetDic Level:=Level + 1, Parent:=vSon
- End If
- End Function
复制代码 |
|