vba - "Transposing"一个表到多个列

Question

我有一张 table ，看起来像这样:

+---+---+---+---+---+---+
|   | a | b | c | d | e |
+---+---+---+---+---+---+
| f | 1 | 3 | 3 | 2 | 2 |
| g | 3 | 1 | 3 | 2 | 1 |
| h | 3 | 3 | 1 | 3 | 3 |
| i | 2 | 2 | 3 | 1 | 2 |
| j | 2 | 1 | 3 | 2 | 1 |
+---+---+---+---+---+---+

我希望它是这样的:

+---+---+---+
| a | f | 1 |
| a | g | 3 |
| a | h | 3 |
| a | i | 2 |
| a | j | 2 |
| b | f | 3 |
| b | g | 1 |
| b | h | 3 |
| b | i | 2 |
| b | j | 1 |
| c | f | 3 |
| c | g | 3 |
| c | h | 1 |
| c | i | 3 |
| c | j | 3 |
| d | f | 2 |
| d | g | 2 |
| d | h | 3 |
| d | i | 1 |
| d | j | 2 |
| e | f | 2 |
| e | g | 1 |
| e | h | 3 |
| e | i | 2 |
| e | j | 1 |
+---+---+---+

我正在使用这个宏:

   Sub ColumnCopy()

Sheets("test").Cells.Clear

Dim tRow As Long
Dim source As String
Dim target As String

    source = "test1"       'Set your source sheet here
    target = "test"       'Set the Target sheet name

    'tRow = 2                'Define the start row of the target sheet

    'Get Last Row and Column
    lastRow = Sheets(source).Range("A" & Rows.Count).End(xlUp).Row
    lastCol = Sheets(source).Cells(1, Columns.Count).End(xlToLeft).Column

    tRow = 2
    colBase = 2
    Do While colBase < lastCol
        For iRow = 2 To lastRow

            Sheets(target).Cells(tRow, 1) = Sheets(source).Cells(1, colBase)
            Sheets(target).Cells(tRow, 2) = Sheets(source).Cells(iRow, 1)
            Sheets(target).Cells(tRow, 3) = Sheets(source).Cells(iRow, colBase)

            tRow = tRow + 1
        Next iRow
        colBase = colBase + 1
    Loop
End Sub

但我得到的结果缺少“列 e”:

+---+---+---+
| a | f | 1 |
| a | g | 3 |
| a | h | 3 |
| a | i | 2 |
| a | j | 2 |
| b | f | 3 |
| b | g | 1 |
| b | h | 3 |
| b | i | 2 |
| b | j | 1 |
| c | f | 3 |
| c | g | 3 |
| c | h | 1 |
| c | i | 3 |
| c | j | 3 |
| d | f | 2 |
| d | g | 2 |
| d | h | 3 |
| d | i | 1 |
| d | j | 2 |
+---+---+---+

我找不到导致此问题的原因。我真的是 excel 宏的新手。
谢谢您的帮助!

Answer 1

改变这个:Do While colBase < lastCol对此:

Do While colBase <= lastCol