使用VBA将Excel数据从列复制到行

本文介绍了使用VBA将Excel数据从列复制到行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我对VBA有一点经验，我非常感谢任何关于这个问题的帮助。在基本意义上，我需要将表1中的2列数据转换为表2中的数据行。

它目前在Excel中看起来像这样：

我需要它看起来像这样：

我已经编写了代码，将标题转移到表格2，它的工作正常。我只是以正确的格式传输实际值的问题。现在，我的代码正文是

  ws.Range（B3）。复制
 ws2.Range （C2）。PasteSpecial xlPasteValues 
 
 ws.Range（B4）。复制
 ws2.Range（D2）。PasteSpecial xlPasteValues 
 
 ws .Range（B5）。复制
 ws2.Range（E2）。PasteSpecial xlPasteValues 
 
 ws.Range（B6）。复制
 ws2.Range F2）。PasteSpecial xlPasteValues

继续打开。然而，这真的不会奏效，因为我正在研究的实际文档拥有数以万计的数据点。我知道有一种自动化这个过程的方法，但是我尝试过的一切都没有做任何事情或者给出了错误1004.

对此的任何帮助将不胜感激！！

编辑：有数百个小部分数据，每18行长（1行为帧＃，1行为时间，每行1行的16个频道）。我试图让它进入一个步长为18的循环。这是可能吗？我很好的循环，但我从来没有做过循环复制和粘贴单元格值

解决方案

此方法利用循环和数组来传输数据。这不是最有活力的方法，但它完成了工作。所有循环都使用现有的常量，所以如果您的数据集更改，您可以调整常量，它应该运行很好。确保调整工作表名称以匹配您在Excel文档中使用的名称。实际上，这样做是将您的数据加载到数组中并将其转换到另一个工作表上。

如果您的数据集大小变化不大，您将需要包括一些逻辑来调整循环变量和数组大小声明。如果是这样的话，让我知道，我会找出如何做到这一点，并发布一个编辑。

  Sub moveTimeData （）
 
设置source = ThisWorkbook.Sheets（RawData）
设置dest = ThisWorkbook.Sheets（TransposeSheet）
 
 Const dataSetSize = 15 
 
 Const row15Start = 3 
 Const row15End = 18 
 Const row30Start = 21 
 Const row30End = 36 
 
 Const colStart = 2 
 
 Const destColStart = 2 
 Const dest15RowStart = 2 
 Const dest30RowStart = 3 
 
 Dim time15Array（）As Integer 
 Dim time30Array（）As Integer 
 ReDim time15Array（0到dataSetSize）
 ReDim time30Array（0到dataSetSize）
 
 Dim X As Integer 
 Dim Y As Integer 
 Dim c As Integer 
c = 0 
 
对于X = row15Start到row15End 
 time15Array（c）= source.Cells（X，colStart）.Value 
c = c + 1 
下一步X 
 
c = 0 
对于X = row30Start到row30End 
 time30Array（c）= source.Cells（X，colStart）.Value 
 c = c + 1 
下一个X 
 
对于X = 0对于dataSetSize 
 dest.Cells（dest15RowStart，X + destColStart）.Value = time15Array（X）
下一个X 
 
对于X = 0到dataSetSize 
 dest.Cells（dest30RowStart，X + destColStart）.Value = time30Array（X）
下一个X 
 
 End Sub

EDIT->我认为这是你在阅读之后寻找的编辑

  Sub moveTimeData（）
 
设置source = ThisWorkbook.Sheets（RawData ）
设置dest = ThisWorkbook.Sheets（TransposeSheet）
 
 Const numberDataGroups = 4 
 Const dataSetSize = 15 
 Const stepSize = 18 
 
 Const sourceRowStart = 3 
 
 Const sourceColStart = 2 
 
 Const destColStart = 2 
 Const destRowStart = 2 
 
 
 
 Dim X As Integer 
 Dim Y As Integer 
 Dim currentRow As Integer 
 currentRow = destRowStart 
 
 
 
对于X = 0到numberDataGroups 
对于Y = 0到dataSe tSize 
 dest.Cells（currentRow，Y + destColStart）.Value = source.Cells（（X * stepSize）+（Y + sourceRowStart），sourceColStart）
下一个Y 
 currentRow = currentRow + 1 
下一个X 
 
 
 End Sub

现在这个工作的关键是知道数据转储后你处理的数据组有多少。您需要包含用于检测的逻辑或调整名为numberDataGroups的常量来反映您拥有的组数。注意：我使用类似的技术来遍历数据，其数据以Row Major格式存储。

I have a little experience with VBA, and I would really appreciate any help with this issue. In a basic sense, I need to convert 2 columns of data in sheet 1 to rows of data in sheet 2.

It currently looks like this in Excel:

And I need it to look like this:

I've already written the code to transfer the headings over to sheet 2, and it works fine. I'm just having issues with transferring the actual values in the correct format. Right now, the body of my code is

ws.Range("B3").Copy
ws2.Range("C2").PasteSpecial xlPasteValues

ws.Range("B4").Copy
ws2.Range("D2").PasteSpecial xlPasteValues

ws.Range("B5").Copy
ws2.Range("E2").PasteSpecial xlPasteValues

ws.Range("B6").Copy
ws2.Range("F2").PasteSpecial xlPasteValues

continued on and on. However, this really won't work, as the actual document I'm working on has tens of thousands of data points. I know there's a way to automate this process, but everything I've tried has either done nothing or given an error 1004.

Any help with this would be greatly appreciated!!

Edit: There are hundreds of little sections of data, each 18 rows long (1 row for the frame #, 1 row for the time, and 1 row for each of the 16 channels). I'm trying to get it into a loop with a step size of 18. Is that possible? I'm fine with loops, but I've never done a loop with copying and pasting cell values

解决方案

This method leverages loops and arrays to transfer the data. It isn't the most dynamic method but it gets the job done. All the loops use existing constants, so if your data set changes you can adjust the constants and it should run just fine. Make sure to adjust the worksheet names to match the names you are using in your excel document. In effect, what this is doing is loading your data into an array and transposing it onto another worksheet.

If your data set sizes change quite a bit, you will want to include some logic to adjust the loop variables and array size declarations. If this is the case, let me know and I'll figure out how to do that and post an edit.

Sub moveTimeData()

Set source = ThisWorkbook.Sheets("RawData")
Set dest = ThisWorkbook.Sheets("TransposeSheet")

Const dataSetSize = 15

Const row15Start = 3
Const row15End = 18
Const row30Start = 21
Const row30End = 36

Const colStart = 2

Const destColStart = 2
Const dest15RowStart = 2
Const dest30RowStart = 3

Dim time15Array() As Integer
Dim time30Array() As Integer
ReDim time15Array(0 To dataSetSize)
ReDim time30Array(0 To dataSetSize)

Dim X As Integer
Dim Y As Integer
Dim c As Integer
c = 0

For X = row15Start To row15End
    time15Array(c) = source.Cells(X, colStart).Value
    c = c + 1
Next X

c = 0
For X = row30Start To row30End
    time30Array(c) = source.Cells(X, colStart).Value
    c = c + 1
Next X

For X = 0 To dataSetSize
    dest.Cells(dest15RowStart, X + destColStart).Value = time15Array(X)
Next X

For X = 0 To dataSetSize
    dest.Cells(dest30RowStart, X + destColStart).Value = time30Array(X)
Next X

End Sub

EDIT-> I think this is what you are looking for after reading your edits

Sub moveTimeData()

Set source = ThisWorkbook.Sheets("RawData")
Set dest = ThisWorkbook.Sheets("TransposeSheet")

Const numberDataGroups = 4
Const dataSetSize = 15
Const stepSize = 18

Const sourceRowStart = 3

Const sourceColStart = 2

Const destColStart = 2
Const destRowStart = 2



Dim X As Integer
Dim Y As Integer
Dim currentRow As Integer
currentRow = destRowStart



For X = 0 To numberDataGroups
    For Y = 0 To dataSetSize
        dest.Cells(currentRow, Y + destColStart).Value = source.Cells((X * stepSize) + (Y    + sourceRowStart), sourceColStart)
    Next Y
    currentRow = currentRow + 1
Next X


End Sub

Now the key to this working is knowing how many groups of data you are dealing with after the data dump. You either need to include logic for detecting that or adjust the constant called numberDataGroups to reflect how many groups you have. Note: I leveraged a similar technique for traversing arrays that have their data stored in Row Major format.

这篇关于使用VBA将Excel数据从列复制到行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！