Transferring data from an array to a range - strange error

musicgold

Board Regular
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.

Rich (BB code):
``````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.

Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try

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

VoG,

Thanks a lot. It works !

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?

Rich (BB code):
``````Dim Buycol ()
Dim Sellcol ()
Dim Vma ()
...
TotalRows = .Range("Master").Count           ‘ roughly 500 rows

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])"

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

Thanks,

MG.

Replies
3
Views
433
Replies
7
Views
359
Replies
3
Views
3K
Replies
3
Views
587
Replies
7
Views
957

1,196,359
Messages
6,014,798
Members
441,847
Latest member
hw407

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back