|
- Sub grf()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1:ca" & Sheet1.[e65536].End(3).Row)
- brr = Sheets("不良代码").[a1].CurrentRegion
- For i = 3 To UBound(brr) '不良原因和代码相对应
- d(brr(i, 1)) = brr(i, 2)
- Next
- ReDim crr(1 To 1000, 1 To 30)
- For i = 4 To UBound(arr)
- x = Trim(arr(i, 29)) '不良分类AC列
- Do While Len(x) > 0 'AC列不为空
- For k = 1 To Len(x)
- If IsNumeric(Mid(x, k, 1)) Then Exit For
- Next
- bl = Left(x, k - 1): sl = Val(Mid(x, k)) '不良及数量
- r = r + 1: n = n + 1
- crr(r, 2) = arr(i, 5) '订单号
- crr(r, 10) = sl '不良数量
- crr(r, 11) = IIf(d.exists(bl), d(bl), bl) '不良原因(或代码)
- If n = 1 Then crr(r, 16) = arr(i, 49) '作业时间P列值=原表AW列值 (只填 第一行)
- If n = 1 And arr(i, 4) <> "是" Then crr(r, 19) = arr(i, 17) '作业时间S列值=原表Q列值 (只填 第一行)
- x = Trim(Replace(x, bl & sl, ""))
- Loop
- If Len(arr(i, 23)) > 0 Then 'W列不为空
- r = r + 1: n = n + 1
- crr(r, 2) = arr(i, 5) '订单号
- crr(r, 30) = arr(i, 79) & "报废," & arr(i, 23) '确认内文
- End If
- If n = 0 And arr(i, 4) <> "是" Then 'W列为空,AC列为空,D列不等于“是”
- r = r + 1
- crr(r, 2) = arr(i, 5) '订单号
- crr(r, 19) = arr(i, 17) '作业时间S列值=原表Q列值
- End If
- n = 0
- Next
- With Sheets(2)
- maxr = .[b65536].End(3).Row + 1
- .Cells(maxr, 1).Resize(r, UBound(crr, 2)) = crr
- End With
- End Sub
复制代码 |
|