|
- Option Explicit
- Sub Test()
- Dim shData As Worksheet, shResult As Worksheet
- Dim lngMax As Long, arrData As Variant, arrDateTitle As Variant
- Dim lngCur As Long, arrResult As Variant
- Dim rgType As Range, lngRows As Long
- Dim lngRowID As Long, lngColID As Long
- Dim strType As String, strID As String, strMemo As String
- Dim lngProNum As Long, strUnCause As String
- Dim lngNum As Long, strDate As String, lngCountNum As Long
-
- Set shData = Sheets("旧表数据")
- Set shResult = Sheets("新表")
- arrDateTitle = shData.Range("G2").Resize(1, 31)
-
- lngMax = shData.Range("E" & Rows.Count).End(xlUp).Row
- ReDim arrResult(1 To lngMax * 31, 1 To 7)
- lngCur = 0
-
- Set rgType = shData.Range("A3")
- Do Until rgType.Row > lngMax
- If rgType.MergeCells Then Set rgType = rgType.MergeArea
- lngRows = rgType.Rows.Count
-
- If lngRows > 3 Then
- strType = rgType(1).Value '物料种类
- strID = rgType.Offset(0, 1)(1).Value '物料编码
- strMemo = rgType.Offset(0, 2)(1).Value '物料描述
-
- arrData = rgType.Offset(0, 4).Resize(lngRows, 33)
-
- For lngColID = 3 To 31
- strDate = arrDateTitle(1, lngColID) '日期
- lngProNum = Val(arrData(2, lngColID)) '生产数量
- lngCountNum = Val(arrData(3, lngColID)) '不合格数量
- If lngProNum > 0 Then '如果生产数量>0
- If lngCountNum > 0 Then '如果不合格总数量>0
- For lngRowID = 4 To lngRows
- strUnCause = arrData(lngRowID, 1) '不合格原因
- lngNum = arrData(lngRowID, lngColID) '不合格数量
- If lngNum > 0 Then '如果具体不合格项的数量>0
- lngCur = lngCur + 1
- arrResult(lngCur, 1) = strType
- arrResult(lngCur, 2) = strID
- arrResult(lngCur, 3) = strMemo
- arrResult(lngCur, 4) = lngProNum
- arrResult(lngCur, 5) = strUnCause
- arrResult(lngCur, 6) = lngNum
- arrResult(lngCur, 7) = strDate
- End If
- Next
- Else '如果没有不合格,增加一条生产数量的记录
- lngCur = lngCur + 1
- arrResult(lngCur, 1) = strType
- arrResult(lngCur, 2) = strID
- arrResult(lngCur, 3) = strMemo
- arrResult(lngCur, 4) = lngProNum
- arrResult(lngCur, 7) = strDate
- End If
- End If
- Next
- End If
-
- Set rgType = rgType.Offset(1, 0)
- Loop
-
- shResult.Range("A2:G" & Rows.Count - 1).ClearContents
- shResult.Range("A2").Resize(lngCur, 7) = arrResult
-
- MsgBox "OK"
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|