|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
如图,将左侧b3:d14重排到g3:j11,排列规则,原始表是同一个检验员的数据在一列,现在想把同一个样品编号的排在一列,刚学VBA不就,编的代码虽然能实现这个功能,但是太繁杂了,请求大家指点下怎么优化,谢谢!
代码如下:
- Sub 重排()
- Dim arr, brr, m%
- arr = [a3:d14]
- ReDim brr(1 To UBound(arr) * UBound(arr, 2), 1 To 1)
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, 1) = 1 Then
- m = m + 1
- brr(m, 1) = arr(i, j)
- End If
- Next
- Next
- [g3].Resize(m, 1) = brr
- m = 0
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, 1) = 2 Then
- m = m + 1
- brr(m, 1) = arr(i, j)
- End If
- Next
- Next
- [h3].Resize(m, 1) = brr
- m = 0
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, 1) = 3 Then
- m = m + 1
- brr(m, 1) = arr(i, j)
- End If
- Next
- Next
- [i3].Resize(m, 1) = brr
- m = 0
- For i = 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If arr(i, 1) = 4 Then
- m = m + 1
- brr(m, 1) = arr(i, j)
- End If
- Next
- Next
- [j3].Resize(m, 1) = brr
- End Sub
复制代码
求助.rar
(13.05 KB, 下载次数: 10)
|
|