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

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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

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



End Sub
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If that's the code, you haven't sized array2 so you can't write to its elements.
 

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Thanks, im pretty new to arrays, how do i set the size for array2?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I just noticed that you are assigning from array2 to array1, rather than the other way round, and that doesn't make sense given that array2 is empty. What are you actually trying to do here?
 

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Im trying to copy a value from array1 to array2., ie Array1(2, 1) = Array2(1, 1)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, that's the opposite. You are assigning the value of array2(1, 1) to array1(2,1) if you write that. So, again, what is the actual end goal, and how big should array2 be?
 

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Im trying to copy the value in cell A2, which is 4 to array1 to array2. The total range is 10 cells. So the size of array2 should be 10.

Hope this helps.,

Thanks,
 

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I get the same subscript out of range error using Array2(1, 1) = Array1(2, 1)
 

sintek

New Member
Joined
Oct 14, 2015
Messages
30
Perhaps ?
VBA Code:
Sub sintek()
Dim Arr1, Arr2
Arr1 = Range("A1:A10").Value
ReDim Arr2(1 To 1)
Arr2(1) = Arr1(2, 1)
End Sub
 

Roonyroox

New Member
Joined
Aug 25, 2020
Messages
22
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi, I've got the code to work, thanks to your suggestions. But im not getting any values in array2.




The code is:

VBA Code:
Sub x1()

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


Range("e1:e1") = Array2

msgbox Array2

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top