Sending an Array to a Range without a Do Loop

Anonymous · Sep 15th 2003

In a macro I can have a line like
x=range("A1:A65000")
that quickly puts all of the values in the range into the variant x.

Now suppose I have a variant y with 65000 values that I want to put to range("A1:A65000"). How can I do that without a do loop? range("A1:A65000") =y fails.

Richie(UK) · Sep 15th 2003

Hi Derk,

How about something like this:

Code

Sub Array_Test()
    Dim y As Variant
        
    With Sheet1
        .Range(.Cells(1, 1), .Cells(1, 1).Resize(UBound(y), 1)).Value = y
    End With
    
End Sub

HTH

Anonymous · Sep 15th 2003

Thanks Richie, but it doesn't work. Only the first value in the variant array is copied to all of the range cells, rather than y(1) to the first cell, y(2) to the second and so on. A do loop does what I want, but I'm trying to save execution time.

Anonymous · Sep 15th 2003

With some experimentation, I've found the solution. The variant needs to be two dimensional for it to work correctly when passing values back. Try the following macro (after you have some values in A1:A5). All but the last works correctly. In my application I was trying the equivalant of the last rather than the third column.

My apologies Richie, I should have given you some more context.

Code

Option Base 1


Sub AHa()
    Dim x, r(5, 1), s(5), z, y, i As Integer
    x = Range("A1:A5")
    Range("B1:B5") = x
    For i = 1 To 5
        r(i, 1) = x(i, 1)
        s(i) = x(i, 1)
    Next i
    y = r
    Range("C1:C5") = y
    z = s
    For i = 1 To 5
        Cells(i, 4) = z(i)
    Next i
    z = s
    Range("E1:E5") = z
End Sub

Display More

XL-Dennis · Sep 15th 2003

Derk,

You can use the Application.Transpose(vaData) for reading the data back.

It´s limit to 5436 cells upto XL 2000 while in 2002 this limitation does not exit.

Make a search here on the board where I have used the above approach.

Kind regards,
Dennis

Richie(UK) · Sep 16th 2003

Hi Derk,

It's me that should apologise - my example was rushed because I was just about to go to the pub! :biggrin:

Anyway, this is what I should have written:

Code

Sub Array_Test()
    Dim y As Variant
    Dim l As Long
    
    ReDim y(1 To 500, 1 To 1)
    For l = 1 To 500
        y(l, 1) = Int(Rnd() * 1000)
    Next l
    'populate array with random numbers
    
    With Sheet1
        .Range(.Cells(1, 1), .Cells(1, 1).Resize(UBound(y), 1)).Value = y
    End With
    'pass array values to appropriately sized range
    
End Sub

Display More

and your conclusion is correct, the array does need to be 2D to reflect the fact that it is being transferred to a range.

Sending an Array to a Range without a Do Loop

Participate now!

Share