有一张EXCEL工作表,含有18行(1~18),51列(A~AY),现在要求将原来的每一列数据依次排成一长列(共18*51行,每行一个数据)
原始数据在SHEET1中,转换后的数据在SHEET2的B列。
Dim bq1, bq2 As String
Dim xb1, xb2 As String
Dim i, j, x, a, b As Integer
bq1 = "Sheet1"
bq2 = "Sheet2"
x = 0
'
a = 18
b = 51
For j = 1 To b
For i = 1 To a
x = x + 1
xb2 = "B" + CStr(x)
If j <= 26 Then
xb1 = Chr(64 + j) + CStr(i)
Else
xb1 = "A" + Chr(64 + j - 26) + CStr(i)
End If
Worksheets(bq2).Range(xb2) = Worksheets(bq1).Range(xb1)
Next i
Next j
样例如图所示