L
L. Howard
I want to "gather" the values of many (9 or 10 say) scattered cells in one worksheet and copy them to another workbook to a specific row.
All my searches seem to refer to ranges of cells that are all nicely in a column or a row.
I see stuff like this but I cannot find an example where you throw all the scattered cells values in an array and then tell it you want it to go to Workbook XX, Sheet1, Range("B2:K2") and paste the values there.
Dim MyArr(1,0) = 1
Dim MyArr(1,1) = 2
Dim MyArr(1,2) = 3
This first example does a nice job of putting 1 to 10 into A1 to A10.
Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub
This one I thought would show the values of the cell in myarray one by one in a Msgbox but it errors out.
'/ Wrong number of arguments
Sub From_sheet_make_array()
Dim myarray As Variant
myarray = Range("B2", "G2", "B11", "K16", "F17").Value
'Looping structure to look at array.
For i = 1 To UBound(myarray)
MsgBox myarray(i, 1)
Next
End Sub
Is an array approach the best way to gather all my scattered value and transport then to another workbook?
Thanks.
Howard
All my searches seem to refer to ranges of cells that are all nicely in a column or a row.
I see stuff like this but I cannot find an example where you throw all the scattered cells values in an array and then tell it you want it to go to Workbook XX, Sheet1, Range("B2:K2") and paste the values there.
Dim MyArr(1,0) = 1
Dim MyArr(1,1) = 2
Dim MyArr(1,2) = 3
This first example does a nice job of putting 1 to 10 into A1 to A10.
Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub
This one I thought would show the values of the cell in myarray one by one in a Msgbox but it errors out.
'/ Wrong number of arguments
Sub From_sheet_make_array()
Dim myarray As Variant
myarray = Range("B2", "G2", "B11", "K16", "F17").Value
'Looping structure to look at array.
For i = 1 To UBound(myarray)
MsgBox myarray(i, 1)
Next
End Sub
Is an array approach the best way to gather all my scattered value and transport then to another workbook?
Thanks.
Howard