在循环中使用Excel转置动态值

Question

我刚开始超越VBA，我需要帮助来弄清楚如何将值从一个工作表转换到另一个工作表。

下面是关于我的数据是如何和我需要如何转换数据的图片：

转座子前

特征的计数类CHAR 1 CHAR 2 CHAR 3E 211E 112CHAR 4<代码>E 213CHARE 114CHAR 5E 215E 116CHAR 6E 217\x

3 x-吸收体-型化合物-尺寸

|SIZE_A |CONNECTION_A |SIZE_B |CONNECTION_B材料

转座子后

类CHAR\x{e76f}

吸收器型

吸收器尺寸

吸波材料

适配器型

适配器|SIZE_A

适配器|CONNECTION_A

适配器|SIZE_B

适配器|CONNECTION_B

适配器材料

我有1000多行数据需要转换。

对不起，这是我能做的关于桌子的最好的事情了。

Answer 1

我认为这个函数将根据你问题中的样本数据来工作。这假设了一些事情("Count“是原始数据中的第一列，没有空白的"Char”字段，等等)，所以如果需要的话，我可以帮助更具体地调整它以适应您的确切场景。如果所有内容都在一个工作簿中，并且不需要/希望将工作簿和工作表对象设置为变量，那么我们也可以删除几行。

Public Sub Transpose()

Dim Row1 As Long
Dim Column1 As Long
Dim Row2 As Long
Dim CurrentWB As Workbook
Dim CurrentWS As Worksheet
Dim TransposeWB As Workbook
Dim TransposeWS As Worksheet
Dim CurrentClass As String

'Set workbook(s) and worksheets as variables just to make it easier to reference them
'Replace necessary workbooks and worksheets with what holds your actual data
Set CurrentWB = ActiveWorkbook
Set CurrentWS = CurrentWB.Worksheets("Sheet1")
Set TransposeWB = ActiveWorkbook
Set TransposeWS = CurrentWB.Worksheets("Sheet2")

'Set the starting row for the worksheet in which the transposed data will go
Row2 = 1

'Adjust as needed for the row in which your raw data starts and ends
For Row1 = 1 To 1000

    'Store the current class
    CurrentClass = CurrentWS.Cells(Row1, 2)

    'Assuming first "Char" column is 3
    Column1 = 3

    While CurrentWS.Cells(Row1, Column1) <> ""

        TransposeWS.Cells(Row2, 1) = CurrentClass
        TransposeWS.Cells(Row2, 2) = CurrentWS.Cells(Row1, Column1)

        'Move to the next "Char" column in untransposed sheet
        Column1 = Column1 + 1

        'Move to the next row in transposed sheet
        Row2 = Row2 + 1
    Wend

Next Row1

结束子对象