CODEWITHSUNDEEP
stringvbarange
brookebot
brookebot

Reputation: 25

Store range of strings in array (vba)

The goal is to create an array with the items in the declared range. Simple but I can't get it to work...

'Declaration
Dim Measures As Variant
Dim MeasureRows As Long

'Search and store
Sheets(4).Activate
MeasureRows = Range("A" & Rows.Count).End(xlUp).row
Measures = Range(Cells(2, 1), Cells(MeasureRows, 1)).Value2

MeasureRows populates fine but when I go to check Measures, it is empty. e.g.

?MeasureRows
 780 
?Measures(MeasureRows) 'Returns "Subscript out of range"    
?Measures(1) 'Returns "Subscript out of range"

I'm very green when in comes to VBA so please excuse the basic question. I tried searching the web for answers but I couldn't get anything to work. I imagine I just don't know the right jargon for the search terms.

Upvotes: 1

Views: 181

Answers (1)

QHarr
QHarr

Reputation: 84475

It is 2 dimensional when read from sheet

Option Explicit
Public Sub test()
    Dim Measures As Variant
    Dim MeasureRows As Long

    With Worksheets("Sheet4")
        MeasureRows = .Cells(.Rows.Count, "A").End(xlUp).Row
        Measures = .Range(.Cells(2, 1), .Cells(MeasureRows, 1)).Value2
    End With

    Dim i As Long
    For i = LBound(Measures, 1) To UBound(Measures, 1)
        Debug.Print Measures(i, 1)
    Next
    Stop

End Sub

Upvotes: 2

Related Questions