I've linked images describing an example of what I'm working with (Input, Array, and Output), and written an example of the code that I'm using below. For the sake of simplicity let's define "Input Sheet" as Sheet1 and "Output Sheet" as Sheet2 within the workbook:
Option Explicit
Sub TransferData()
Dim myArray as Variant 'Define the array to hold the data.
Dim i as integer 'Define a generic loop counter variable.
myArray = Sheet1.Range("A1:F7") 'Pulls all the relevant data into the array.
myArray now looks exactly like the range. (I'd post 3rd link, but I need to earn some more rep first).
I want to transfer the data into my output file so that it looks like this:
Here is how I currently approach the situation:
For i = 1 to ubound(myArray)
Sheet2.Cells(i,1) = myArray(i,1)
Sheet2.Cells(i,3) = myArray(i,2)
Sheet2.Cells(i,6) = myArray(i,3)
Sheet2.Cells(i,7) = myArray(i,4)
Sheet2.Cells(i,8) = myArray(i,5)
Sheet2.Cells(i,9) = myArray(i,6)
Next i
End Sub
My question is this; is there a way to transfer the data from the array to the staggered ranges in a way similar to how I grabbed the array; a range at a time? For example, something along the lines of:
Sheet2.Range("A1:A8") = myArray(1 to 7, 1) 'Note: this is total pseudocode
Sheet2.Range("C1:C8") = myArray(1 to 7, 2) 'Note: this is total pseudocode
etc... etc...
I believe I've managed to teach myself a fair amount of VBA so far, but I definitely don't know everything, and this is something i need help with. I'm trying to minimize the amount of writes开发者_StackOverflow中文版 to the worksheets from VBA; they really take their toll.
Any help is greatly appreciated.
Thank you!
"Copying and pasting" the values might be quite a bit faster. You'll obviously need to generalize this to fit your purposes, but this code works with the sample you provided:
Sub TransferData()
Sheet3.Range("A1:A7") = Sheet1.Range("A1:A7").Value
Sheet3.Range("C1:C7") = Sheet1.Range("B1:B7").Value
Sheet3.Range("F1:I7") = Sheet1.Range("C1:F7").Value
End Sub
Depending on the size of the "other data" to insert, one very simple way of approaching this is to transfer all of your data into the output range, then insert new columns and place the other data as appropriate.
For example:
data = Worksheets("InputSheet").Range("A1:F7").Value
Worksheets("OutputSheet").Range("A1:F7").Value = data
Worksheets("OutputSheet").Columns("B").EntireColumn.Insert ' do this for every column you need, within reason.
You could then drop your "other data" into all of the new columns you had created.
精彩评论