Store range of strings in array (vba)

Question

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.

QHarr · Accepted Answer · 2018-07-30 14:20:04Z

2

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

answered Jul 30, 2018 at 14:20

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

3 Comments

QHarr Over a year ago

You may also need MeasureRows -1

brookebot Over a year ago

Great. Thank you. I see you have it s/t it prints every item of Measures after it has been populated. Is there a way to see my array in a place similar to the Matlab Workspace or the R Environment?

QHarr Over a year ago

You can inspect it in the locals window when it stops at the STOP command. You want to delete that command at a later point.

Collectives™ on Stack Overflow

Store range of strings in array (vba)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related