使用VBA将Excel数据从列复制到行 [英] Copy Excel data from columns to rows with VBA
问题描述
它目前在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数据从列复制到行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
