CODEWITHSUNDEEP
arraysvbaexcel
Eem Jee
Eem Jee

Reputation: 1319

Store range of cells to an Array

I'm working with arrays and I'm sorry but I'm a bit new to it and still confused. I have already this code to store the values in a range in an array and if I run it, it is empty.

attPresent = ws.Range("H4:H" & lastrow)
    ReDim attPresent(1 To UBound(attPresent))
    For k = LBound(attPresent) To UBound(attPresent)
        MsgBox attPresent(k)
    Next

Can someone please tell me where I'm wrong? I've read any other posts and gather some ideas, but still not working.

Upvotes: 0

Views: 3066

Answers (3)

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

    'Try this code example to suit your case
Sub StoreRangeofCellsToAnArray()
        'presumes optionbase=0
        'presume Range("a1:c3") contain number 1 to 9
        Dim MyRange, MyArray(9), n
        n = 0
        For Each c In Range("a1:c3")
        MyArray(n) = c
        n = n + 1
        Next

        'testing: reprint array
        For n = 0 To 8
        Debug.Print MyArray(n)
        Next
    End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

You can go like this

Dim attPresent as Variant
attPresent = ws.Range("H4:H" & lastrow).Value '<-- this will automatically size the array to a 2dimensional array of as many rows as the range ones and one column
For k = LBound(attPresent,1) To UBound(attPresent,1)
    MsgBox attPresent(k,1)
Next

Or

Dim attPresent as Variant
attPresent=Application.Transpose(Range("H4:H" & lastrow).Value) '<-- this will automatically size the array to a 1dimensional array of as many elements as the range
For k = LBound(attPresent) To UBound(attPresent)
    MsgBox attPresent(k)
Next

Upvotes: 3

user6432984
user6432984

Reputation:

Reference MSDN: ReDim Statement (Visual Basic)

When you Redim the array you are erasing all the values. Use ReDim Preserve to resize the last dimension of the Array and still preserve the values of the array.

After the dynamic array has been allocated(the first time Redim is used) you can only resize the last dimension of a array and you cannot change the number of dimensions. In your code you are trying to convert a 2 Dimensional array to a 1 Dimensional array, you can not do that.

You should watch this complete series: Excel VBA Introduction. This is the relevant video: Excel VBA Introduction Part 25 - Arrays

Upvotes: 2

Related Questions