Copying An Array To A New Array Gives subscript out of range error

[Roonyroox]

Hi, Im trying to copy a value from an array to a new array, ie., Array1(2, 1) = Array2(1, 1)

But this gives a subscript out of range error, the code is pretty basic.

Link to the file

array.xlsm

drive.google.com

Sub x1()
 
     
    Dim Array1() As Variant
    Dim Array2() As Variant
    
    Array1 = Range("a1:a10").Value
    
    
    
    Array1(2, 1) = Array2(1, 1)



End Sub

 

[RoryA]

You've only populated one value in array2, and only sent the output to one cell, so you should see one value (assuming A1 wasn't blank)

 

[sintek]

Why pass to Second Array and then to Range...Why not just directly to Range from First Array

 

[Roonyroox]

Link to the file, just in case array.xlsm

Thanks.,

 

[Roonyroox]

Sintek Range("e1:e1") = Array2 just copies array2 to cell e1

 

[sintek]

As per Post 9...

Sub sintek()
Dim Arr1, Arr2
Arr1 = Range("A1:A10").Value
ReDim Arr2(1 To 1)
Arr2(1) = Arr1(2, 1)
Range("E1") = Arr2(1)
End Sub

In your case...?

Range("e1:e1") = Array2(1, 1)

 

[Roonyroox]

Thanks sintek & rory, works fantastically, much appreciated ...

 

[Roonyroox]

Hi guys one last question.,

Is it possible to make Array2 dynamic, so it autoadjusts the size just like Array1? ie Dim Array2() As Variant. I'm using atm Dim Array2(1, 10) As Variant.

Thanks.,

Sub x1()
 
     
     
     
     
     
     
    Dim Array1() As Variant
    Dim Array2(1, 10) As Variant
    
    Array1 = Range("a1:c10").Value
    
    
    
     Array2(1, 1) = Array1(1, 3)


Range("e1:e1") = Array2(1, 1)


End Sub

 

[RoryA]

Why are you using an array at all? You only populate one element.

 

[sintek]

You mean size of Arr1

Arr1 = Range("A1:A10").Value
ReDim Arr2(1 To UBound(Arr1))

 

[Roonyroox]

Sintek thanks, is this the correct way to use it?

Sub x1()
 
     
     
     
     
     
     
    Dim Array1() As Variant
    Dim Array2(1, 10) As Variant
    
  
    Array1 = Range("a1:c10").Value
    ReDim Array2(1 To UBound(Array1))
    
    
     Array2(1, 3) = Array1(1, 3)


Range("e1:e1") = Array2(1, 3)


End Sub