Saving Range in an Array and retrieving any Index from Array

[Meesam]

Hi Guys,
I'm really stuck with this code for last 6 hours now. Code is not that completed. Actually I have a range (containing text and numbers mixed) so i'm first saving the range in one variable and then I'm trying to save (or copy) the range to another Array. It works fine to this level. But when I want to retrieve ANY individual item (or index) from Array then I'm unable to do so. Can somebody guide me how to properly copy range to array that I have complete control over my array i.e. I can access any of it's indices for further processing. Here is snippet of code.

Dim strAll() As String
Dim strSNO() As String
Dim lastRow As Integer, i As Integer, newRng As Range, count As Integer

With ThisWorkbook.Sheets("Data")
    lastRow = .Range("A7000").End(xlUp).Row
    ReDim strAll(lastRow)
    Set newRng = .Range("A1:A" & lastRow)
    MsgBox LBound(strAll()) & ":" & UBound(strAll())
End With

For count = LBound(strAll()) To UBound(strAll())
    strAll(count) = newRng.Offset(count, 0).Value
Next

Would appreciate earliest help as I want to close this project tonight !

 

[Norie]

You need to loop through the array and put the individual items in the array, not try to put the entire range (and it's offsets) into the array.

Try this.

Dim strAll() As String
Dim strSNO() As String
Dim lastRow As Integer, i As Integer, newRng As Range, cnt As Integer

    With ThisWorkbook.Sheets("Data")
        lastRow = .Range("A7000").End(xlUp).Row
        ReDim strAll(lastRow)
        Set newRng = .Range("A1:A" & lastRow)
        MsgBox LBound(strAll()) & ":" & UBound(strAll())
    End With

    For cnt = LBound(strAll()) To UBound(strAll())
        strAll(cnt) = newRng.Cells(cnt + 1, 1).Value
    Next

    x = strAll(4)    ' get 5th element from array

    MsgBox "5th element is " & x

 

[Meesam]

That worked perfectly. Actually I was looking at lots of threads online and most of guys were putting ranges directly to arrays for unknown reasons. But I really wanted the full array in control. Thanks a lot.

 

[Norie]

A lot of the code you'll see that puts ranges in arrays does it to speed things up, it's quicker to work with an array than a range, for some things.

Your code could have been written like this.

Dim strAll()
Dim lastRow As Integer, newRng As Range
Dim x

    With ThisWorkbook.Sheets("Data")
        lastRow = .Range("A7000").End(xlUp).Row
        Set newRng = .Range("A1:A" & lastRow)
    End With

        strAll = newRng.Value


    x = strAll(5, 1)   ' get 5th element from array

    MsgBox "5th element is " & x

When you put a range directly in array the resultant array is 2-dimensional.

So to access elements in the array we have to specify both dimensions.

For an array from a range the first dimension is the row and the second dimension is the column.