我需要用Excel的VBA将数据进行 行-列 转置,同学给了我样例和代码:
样例的原始数据是这样的:
同学给的代码如下:
Sub zhuanzhi()
Dim i, j, a, m, n
m = 2
For i = 2 To Sheet1.[a1000000].End(3).Row
a = Sheet1.Cells(i, 4).Value
n = 5
If a = 1 Then
Sheet3.Cells(m, 1) = Sheet1.Cells(i, 1).Value
Sheet3.Cells(m, 2) = Sheet1.Cells(i, 2).Value
Sheet3.Cells(m, 3) = Sheet1.Cells(i, 3).Value
Sheet3.Cells(m, 4) = Sheet1.Cells(i, 4).Value
Sheet3.Cells(m, 5) = Sheet1.Cells(i, 5).Value
m = m + 1
ElseIf a > 1 Then
For j = 1 To a
Sheet3.Cells(m, 1) = Sheet1.Cells(i, 1).Value
Sheet3.Cells(m, 2) = Sheet1.Cells(i, 2).Value
Sheet3.Cells(m, 3) = Sheet1.Cells(i, 3).Value
Sheet3.Cells(m, 4) = Sheet1.Cells(i, 4).Value
Sheet3.Cells(m, 5) = Sheet1.Cells(i, n).Value
m = m + 1
n = n + 1
Next j
End If
Next i
End Sub
通过这段代码,可以转换成如下形式:
可以看到,数据从原来的A-B C D在一行,变成了A-B, A-C, A-D分别在一列,我的数据大概是这样
当我直接套用公式,发现报错代码13,或者1004。改来改去怎么都不对。因此想请教大家,这段代码的含义是什么,以及我应该如何修改?谢谢,辛苦了!