Transferring data from an array to a range - strange error

[musicgold]

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.

 

[VoG]

Try

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

 

[musicgold]

VoG,

Thanks a lot. It works !

 

[musicgold]

Hi VOG,

I am running into a problem when I try to transfer a two dimensional array into a two column range. Please see the following code.

I am able to transfer some contents of Vma into a two-column range (first two rows out of 500 rows), for all other rows Excel inserts #N/A into the cells. I tried different permutations and combinations, to no avail. What could be the problem?

Dim Buycol ()
Dim Sellcol ()
Dim Vma ()
...
TotalRows = .Range("Master").Count           ‘ roughly 500 rows

ReDim Buycol(TotalRows)
ReDim Sellcol(TotalRows)
ReDim Vma(TotalRows, 1)

For i = TotalRows To 0 Step -1

    Vma(i, 0) = "=Average(RC[-2]:R[3]C[-2])" 
    Vma(i, 1) = "=Average(RC[-2]:R[3]C[-2])" 

    Buycol(i) = "=Average(RC[-2]:R[3]C[-2])"
    Sellcol(i) = "=Average(RC[-2]:R[3]C[-2])"

    Next
            
   .Range("sellcolumn") = Application.WorksheetFunction.Transpose(Sellcol) 
         'Working      
   .Range("ratios") = Application.WorksheetFunction.Transpose(Vma)   ‘ NOT WORKING
   .Range("buycolumn") = Application.WorksheetFunction.Transpose(Buycol) 'Working

Thanks,

MG.