PDA

View Full Version : Transferring data from an array to a range - strange error



musicgold
03-09-2009, 12:02 PM
Hi,

I am not able to transfer the data in a vba array to a range on the spreadsheet. Please see the following code. Could you please help me solve this problem?

The range A1:A10 contains the following numbers: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100. However, the code writes ‘10’ in all the cells of the range B1:B10.

However if I replace the red line in the code with this statement: Myarray = Array (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Then the code correctly writes 1 through 10 in the range B1:B10.



Sub Sheet_Fill_Array( )

Dim myarray As Variant

Set myarray = Range("A1:A10")

Range("B1:B10").Select

Range("B1:B10") = Application.WorksheetFunction.Transpose(myarray)

End Sub

Thanks,

MG.

GTO
03-09-2009, 12:27 PM
Greetings MG,

The problem is your use of Transpose. See the help topic, but in short, TRanspose is used to transpose or coerce a vertical array to a horizontal array or visa-versa.

You could have done:



Sub ex()
Dim myrange As Range
Set myrange = Range("B1:B10")
myrange = Range("A1:A10").Value
End Sub

'...or simply...

Sub ex_2()
Range("B1:B10") = Range("A1:A10").Value

End Sub

'...or to Transpose:

Sub ex_3()
Range("A1:J1") = Application.WorksheetFunction.Transpose(Range("A1:A10"))
End Sub

Hope that helps,

Mark

musicgold
03-09-2009, 01:19 PM
GTO,

Thanks. Actually the example I gave here is a simplified version of what I am trying to do.

I need to take the data from a range on the spreadsheet, process it and write it into another
range of the spreadsheet. All the spreadsheet ranges here have the same
dimension.

Given this, how can I make this work?

Thanks again,

MG.

mdmackillop
03-09-2009, 01:33 PM
Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Range("A1:A10").Value
Range("B1:B10") = myarray
Range("C1:L1") = Application.WorksheetFunction.Transpose(myarray)
End Sub

musicgold
03-09-2009, 01:53 PM
mdmackillop,

Thanks a lot. It works!

musicgold
03-09-2009, 02:05 PM
mdmackillop,

I am noticing one thing with your code. It doesn't allow me to process individual array elements.
For example, if I try a statement like myarray(3) = 100
I am getting an error.

I need to process the array elements in my code. How can I fix this problem?

Thanks,

MG.

mdmackillop
03-09-2009, 02:37 PM
Deleted.

Bob Phillips
03-09-2009, 02:39 PM
Try



Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Range("A1:A10").Value
Range("B1:B10") = myarray
myarray(3, 1) = 100
Range("C1:L1") = Application.WorksheetFunction.Transpose(myarray)
End Sub

Bob Phillips
03-09-2009, 02:43 PM
Or this



Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Application.Transpose(Range("A1:A10").Value)
Range("B1:B10") = Application.Transpose(myarray)
myarray(3) = 100
Range("C1:L1") = myarray
End Sub