|
如何将A1:B21区域中的数据转换成E1:L15区域的数据
相同数据在一起放置5行,超过5行的数据增加列数放置,效果见附件.请高手相助.谢谢!
本帖最后由 hrpotter 于 2012-5-27 09:46 编辑
蓝天一片云 发表于 2012-5-26 22:26
谢谢您的回复!
如果能将最后的单元格赋值改成数组赋值,效果就更好了。
原理是一样的,把单元格区域替换成数组就行了
- Sub test()
- Dim ar, br, cr, dr(1 To 1000, 1 To 240), d
- Dim i As Integer, j As Integer, k As Integer
- Range("e:iv").Clear
- Set d = CreateObject("scripting.dictionary")
- ar = Range("a1:b1")
- br = Range("a2:b" & Range("a65536").End(xlUp).Row)
- For i = 1 To UBound(br)
- d(br(i, 1)) = ""
- Next
- cr = d.keys
- For i = 0 To UBound(cr)
- k = 0
- For j = 1 To UBound(br)
- If br(j, 1) = cr(i) Then
- If (k Mod 4) = 0 Then
- dr(5 * i + 1, (k \ 4) * 3 + 1) = ar(1, 1)
- dr(5 * i + 1, (k \ 4) * 3 + 2) = ar(1, 2)
- End If
- dr(5 * i + 2 + (k Mod 4), (k \ 4) * 3 + 1) = br(j, 1)
- dr(5 * i + 2 + (k Mod 4), (k \ 4) * 3 + 2) = br(j, 2)
- k = k + 1
- End If
- Next
- Next
- Range("e1").Resize(1000, 240) = dr
- End Sub
复制代码
|
|