|
由于报表文件比较大,打开费时,所以先打开报表文件后,再点击按钮。- Sub 读取() '在报表文件打开的前提下运行
- Dim wb As Workbook, sh As Worksheet
- Set d = CreateObject("scripting.dictionary")
- brr = Sheets(2).[a1].CurrentRegion
- For i = 3 To UBound(brr) '不良原因和代码相对应
- d(brr(i, 1)) = brr(i, 2)
- Next
- 'Set wb = Workbooks.Open(ThisWorkbook.Path & "\报表.xls")
- Set wb = Workbooks("报表.xls")
- Set sh = wb.Worksheets(1)
- arr = sh.Range("a1:db" & sh.[b65536].End(3).Row)
- With ThisWorkbook.Sheets(1)
- For i = 3 To UBound(arr)
- If arr(i, 8) <> "" Then 'H列非空
- x = arr(i, 44) '不良分类AR列
- Do While Len(x) > 0
- 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 = .[b65536].End(3).Row + 1
- .Cells(r, "B") = arr(i, 5) '订单号
- .Cells(r, "D") = arr(i, 2) '工作中心(铜牌号)
- If arr(i, 105) > 0 Then .Cells(r, "M") = arr(i, 105) '作业时间1(合计工时DA列)
- If arr(i, 61) > 0 Then .Cells(r, "P") = arr(i, 61) '作业时间2(合计工时BI列)
- If arr(i, 68) > 0 Then .Cells(r, "S") = arr(i, 68) '作业时间3(合计工时BP列)
- .Cells(r, "AD") = arr(i, 106) '确认内文(工时分布)
- .Cells(r, "J") = sl '不良数量
- If d.exists(bl) Then .Cells(r, "K") = d(bl) Else .Cells(r, "K") = bl '不良原因(或代码)
- x = Replace(x, bl & sl, "")
- Loop
- End If
- Next
- End With
- End Sub
复制代码 |
|