|
- Sub 自动填写()
- r = [f65536].End(3).Row + 2
- Range("H7:ak" & r) = ""
- arr = Range("e5:ak" & [f65536].End(3).Row + 2)
- Set d = CreateObject("scripting.dictionary")
- For j = 5 To UBound(arr, 2)
- d(arr(2, j)) = j
- Next
- For i = 3 To UBound(arr) - 2
- sl = Val(arr(i, 1)) '数量
- x = arr(i, 2) '工序工时
- Do While Len(x) > 0 '分离工序、工时
- For k = 1 To Len(x)
- If InStr("0123456789", Mid(x, k, 1)) > 0 Then Exit For
- Next
- gx = Left(x, k - 1) '工序
- gs = Val(Mid(x, k)) '工时
- y = gs * sl '工时*数量
- zgs = zgs + y '总工时
- arr(i, 4) = arr(i, 4) + y
- c = d(gx)
- If c > 0 Then arr(i, c) = y: arr(UBound(arr), c) = arr(UBound(arr), c) + y
- x = Replace(x, gx & gs, "")
- Loop
- Next
- arr(UBound(arr) - 1, 4) = zgs
- Range("e5:ak" & [f65536].End(3).Row + 2) = arr
- For j = 5 To UBound(arr, 2)
- x = arr(UBound(arr), j)
- If x > 0 Then xstr = xstr & arr(2, j) & x
- Next
- Cells(r, 4) = xstr
- End Sub
复制代码 |
|