|
森林木007 发表于 2013-4-21 17:00
老师我是初学VBA,能不能帮注释一下代码? - Sub 转置数据()
- '源数组,结果数组
- Dim arr, result()
- Dim i&, j&
- '数组记录行数
- Dim lRow&
- '工序序号1,工序序号2,工序类型
- Dim bKind1 As Byte, bKind2 As Byte, strKind As String * 2
- Dim t#
- t = Timer
- '读入源数据
- arr = Worksheets("订单排期").Range("a1").CurrentRegion
- '判断是否有数据读入
- If Not IsArray(arr) Then Exit Sub
- '重新定义结果数组大小:(25个工序)*(行数-1)
- ReDim result(1 To (UBound(arr, 2) - 5) * (UBound(arr) - 1), 1 To 5)
- '源数数据,行循环
- For i = LBound(arr) + 1 To UBound(arr)
- bKind1 = 0
- '源数组,列循环
- For j = LBound(arr) + 5 To UBound(arr, 2)
- '每一列,都是新一行的开始
- lRow = lRow + 1
- result(lRow, 1) = arr(i, 3) '订单批号
- result(lRow, 2) = arr(i, 4) '订单数量
- If strKind <> Left(arr(1, j), 2) Then '判断是否是新的工序
- strKind = Left(arr(1, j), 2) '把新工序类型存入字符串中,
- bKind1 = bKind1 + 1 '工序大序号
- bKind2 = 1 '同一工序内的序号
- Else
- bKind2 = bKind2 + 1 '小序号加1
- End If
- '大序号 & 产品序号 & 小序号
- result(lRow, 3) = bKind1 & arr(i, 2) & Format(bKind2, "00")
- result(lRow, 4) = arr(1, j) '工序名称
- result(lRow, 5) = arr(i, j) '工序单价
- Next
- Next
- '关闭刷屏
- Application.ScreenUpdating = False
- With Worksheets("单价编号")
- '写入结果数组到单元格中
- .Range("a2").Resize(UBound(result), UBound(result, 2)) = result
- '写入标题行
- .Range("a1").Resize(, 5) = Array("订单批号", "订单数量", "工序代码", "工序名称", "工序单价")
- With .Range("a1").CurrentRegion
- '自动调整列宽
- .EntireColumn.AutoFit
- '实线边框
- .Borders.LineStyle = xlContinuous
- '水平居中
- .HorizontalAlignment = xlCenter
- End With
- 'A列最后一行数据所在行号
- i = .Cells(Rows.Count, 1).End(xlUp).Row
- '字体大小为10号
- .Range("a2:e" & i).Font.Size = 10
- 'D列字体加粗
- .Range("d2:d" & i).Font.Bold = True
- 'AB列字体颜色
- .Range("a2:b" & i).Font.ColorIndex = 53
- 'DE列字体颜色
- .Range("d2:e" & i).Font.ColorIndex = 9
- End With
- '打开刷屏
- Application.ScreenUpdating = True
- '显示对话框
- MsgBox "转置完成"
- End Sub
复制代码 |
评分
-
查看全部评分
|