Excel VBA：计算BOM*Planning

前面已经使用VBA对所有生产线的排产进行了汇总，以及从Oracle中分解BOM直至采购层。

接下来，我们实现将成品的Planning，转换为物料的Planning：

基础资料：

1，BOM的格式：

2，汇总后生产计划的格式：

3，我们希望得到的结果：

 如何实现（代码和提示）：

Sub BOMxPlan()

'计划中的SKU是solo，因为是数组汇总好的
'在BOM中SKU也是唯一的

Dim d, d1
Dim i, j, k
Dim m, n
Dim arr, brr, crr(), drr(), err()
Dim s As String
Dim ki, ii



Set d = CreateObject("Scripting.Dictionary")

arr = Sheets("Plan").UsedRange
'得到一个arr(1,1)=A1第一个单元格的二维数组
'Debug.Print arr(1, 1), arr(1, 2)，所以这里说一下二维数组，就是一个Array（行，列）组成的数据矩阵
n = 0
'使用字典最基本应用，唯一值的应用把Plan的SKU作为key放入字典
For i = 2 To UBound(arr)
    s = CStr(Trim(arr(i, 1)))
    
    If d(s) = "" Then
    'd(设备号)初始都是"",同时第一次的时候记得给d(s)赋值，这样第二次就不会到这个IF判断中了。
    n = n + 1
    d(s) = n
    End If
Next

Sheet3.Rows.Delete
Sheet3.Columns.Delete
Sheet3.[a:a].NumberFormatLocal = "@"
Sheet3.[a1] = Sheet1.[a1]
Sheet3.[a2].Resize(d.Count, 1) = Application.Transpose(d.keys)
Sheet3.[b2].Resize(d.Count, 1) = Application.Transpose(d.items)

'如何使数组粘贴后，数值前的000保留下来，这里我们也简单说一下
'首先，我们说明一下s=Cstr("00001"),在数组/字典中肯定还是00001，但是粘贴到表格中，等于1，而不是00001
'我们在Resize之前需要做一个动作：sheet3.[a:b]="@",表示A列B列都是文本,注意表示列的时候是[a:a]

'-----如何读取字典的数据------
'ki = d.keys
'ii = d.items
'Debug.Print d.keys(2), d.items(2)，这样是错误的，需要使用一维数组Ki（1),ii(1)


'我们对计划的SKU是否有BOM做一个判断
'把BOM的SKU放入数组中
n = 0
Set d1 = CreateObject("scripting.dictionary")
brr = Sheets("BOM").UsedRange
For i = 2 To UBound(brr)
    s = Trim(brr(i, 1))
    If d1(s) = "" Then
    n = n + 1
    d1(s) = n
    End If
Next

n = 0
For i = 2 To UBound(arr)
'读取Plan中的每个数据，使用d1.exists(key)来判断是否在数组中存在
    If Not d1.exists(Trim(arr(i, 1))) Then
    n = n + 1
    ReDim Preserve crr(1 To 2, 1 To n)
    'Error:类型不匹配，需要在crr后加一个()
    
    crr(1, n) = arr(i, 1)
    crr(2, n) = i
    Debug.Print crr(1, n), crr(2, n)
    End If
Next

'Debug.Print UBound(crr), UBound(crr, 2)
'Error:注意如果数组中为NULL时，空数组，打印，会提示下标越界

s = ""

If n > 0 Then
For i = 1 To UBound(crr, 2)
s = s & crr(1, i) & "--" & crr(2, i) & "行" & vbLf
Next
MsgBox s & vbLf & "上述SKU不在BOM表中，请联系技术部提供BOM", vbYesNo, "检查Plan中的SKU是否有BOM"
Exit Sub
End If


'接下来，我们使用数组将Plan和BOM按日期做一个乘法（Daily Usage per SKU）
'我们将Plan从F列开始至结束
'error记录:使用d.exsits(key)替代brr(i,1)<>arr(m,1)
'error记录:把整个日期对应下的数量*BOM后，就可以exit for
'error记录:想要代表Plan的某个SKU在哪一行，可以使用d(brr(i,1))+1,因为d(s)是1开始，实例行就加一
'error记录:损耗率是不要直接使用，需要100%+损耗率
'error记录:
Debug.Print UBound(arr), UBound(arr, 2)
Debug.Print arr(1, 1), brr(1, 1)
n = 0
For i = 2 To UBound(brr)
        For m = 2 To UBound(arr)
            If Not d.exists(brr(i, 1)) Then
                n = n + 1
                For j = 1 To UBound(arr, 2) - 6
                ReDim Preserve drr(1 To UBound(arr, 2) - 6, 1 To n)
                drr(j, n) = 0
                Next
                Exit For
            Else
                n = n + 1
                For j = 1 To UBound(arr, 2) - 6
                ReDim Preserve drr(1 To UBound(arr, 2) - 6, 1 To n)
                drr(j, n) = arr(d(brr(i, 1)) + 1, j + 6) * brr(i, 3) * (1 + brr(i, 4))
                'Debug.Print d(brr(i, 1))
                Next
                Exit For
            End If
        Next
Next


Sheets("BOM").[g2].Resize(n, UBound(drr)) = Application.Transpose(drr)

'error：一维数组的Redim preserve，不要忘记1 to n
'error:如果需要将日期放入Excel中，建议先把日期放入数组，不然就要写A1：BA1（这非动态，不提倡）
n = 0
For i = 7 To UBound(arr, 2)
If Len(arr(1, i)) Then
    n = n + 1
    ReDim Preserve err(1 To n)
    err(n) = arr(1, i)
End If
Next

Sheets("BOM").[g1].Resize(1, n) = err