Array not populating from For Loop VBA

Question

I'm trying to create an array of cell entries (e.g. A6,B6, etc) that populates in a for loop.

However the array

    MyArray

is always empty and I can't figure out why its not being populated within the for loop. Below is (the appropriate part of-the code is doing other stuff to) my code:

    Sub ListSheets()

    ' Defining all variables (objects) used within the code, establishing their
    'classes                                   

   Dim i As Integer
   Dim array_size As Integer
   Dim MyArray() As String

   array_size = 26
   ReDim MyArray(array_size) As String

   For intLoop = 1 To 26
   MyArray(intLoop, 1) = Chr$(64 + intLoop) + "6"
   Next

   Set CopyFrom = MyArray
   Sheets("vba_deposit").Range("A1").Resize(CopyFrom.Rows.Count).Value = CopyFrom.Value

   End Sub

Any ideas?

Many thanks in advance.

Hi simoco, I've removed the "1" as advised but I've still getting an empty MyArray and thus "Object Required" compile error. To confirm is that the only line that needs correcting? Thanks for your help. — vbastrangledpython
– vbastrangledpython, Commented Mar 31, 2014 at 10:13
I changed my opinion, it's better to use MyArray(intLoop, 1) but with ReDim MyArray(1 To array_size, 1 To 1). See my answer below — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 31, 2014 at 10:21
@simoco - No need to use MyArray(intLoop, 1) or making it a 2-D array. You simply need to start your loop from 0 rather than 1. — Pankaj Jaju
– Pankaj Jaju, Commented Mar 31, 2014 at 10:23
Ahh ... now i see ... what was the expected output. My bad. :P — Pankaj Jaju
– Pankaj Jaju, Commented Mar 31, 2014 at 10:30

Dmitry Pavliv · Accepted Answer · 2014-03-31 10:31:57Z

2

Try this one:

Sub ListSheets()
   Dim i As Integer
   Dim array_size As Integer
   Dim MyArray() As String

   array_size = 26
   ReDim MyArray(1 To array_size, 1 To 1)

   For intLoop = 1 To 26
        MyArray(intLoop, 1) = Chr$(64 + intLoop) + "6"
   Next

   Sheets("vba_deposit").Range("A1").Resize(UBound(MyArray)).Value = MyArray    
End Sub

Your first idea to use MyArray(intLoop, 1) was good - because in that case there is no need to use Transpose (which not always working since it has limitation on number of elements in array) here: Range(...).Value = MyArray. However I've made little changes in your code:

redim array as 2D: ReDim MyArray(1 To array_size, 1 To 1)
use direct Range(...).Value = MyArray

edited Mar 31, 2014 at 10:31

answered Mar 31, 2014 at 10:20

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phil D · Accepted Answer · 2014-03-31 10:17:51Z

1

Sub ListSheets()

' Defining all variables (objects) used within the code, establishing their
'classes

 Dim i As Integer
 Dim array_size As Integer
 Dim MyArray() As String

 array_size = 26
 ReDim MyArray(array_size) As String

 For intloop = 1 To 26
 MyArray(intloop) = Chr$(64 + intloop) + "6"
 Sheets(1).Range("A1").Offset(intloop - 1).Value = MyArray(intloop)
 Next
 'An array is not an object, you can't use SET with them.
 'Your array is 1-dimensional, MyArray(1,1) won't work as that's 2-dimensional, just
 'MyArray(1) = "whatever1"       MyArray(2) = "whatever2" etc. etc.
 End Sub

answered Mar 31, 2014 at 10:17

Phil D

361 bronze badge

Collectives™ on Stack Overflow

Array not populating from For Loop VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related