How to view items in array

Question

I'm getting Subscript out of range on Debug.Print myarr(i).

Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim myarr as Variant, i as Long
myarr = Array(ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row))

For i = LBound(myarr) To UBound(myarr)
    Debug.Print myarr(i)
Next i

I have validated the range with (below) which shows A2:A5

Msgbox ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)).Address (false, false)

I have also tried changing myarr to

 = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row) 
 = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Value
 = Array(Temp.Range("A2:A" & Temp.Range("A" & Temp.Rows.Count).End(xlUp).Row).Value)

First one gives Subscript out of range, 2nd two give type mismatch

Fairly new to working with arrays, and I dont know what I'm missing here. Am I loading the array incorrectly or trying to view the items in array incorrectly?

Scott Craner · Accepted Answer · 2018-07-23 20:04:00Z

5

myarr is a two dimesional array with one column.

You will need to add the references to the second dimension.

Debug.Print myarr(i,1)

Also to load an array from a range you simply assign the value:

myarr = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Value

so

Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim myarr as Variant, i as Long
myarr = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Value

For i = LBound(myarr,1) To UBound(myarr,1)
    Debug.Print myarr(i,1)
Next i

edited Jul 23, 2018 at 20:04

answered Jul 23, 2018 at 19:54

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

urdearboy Over a year ago

So loading the array this way was my 3rd attempt. But it wasn't working because I was not referring to the array as two dimensional. Dots: Connected.

gregV · Accepted Answer · 2018-07-24 01:59:00Z

0

to add, you can view your array size and elements in VBA Locals Window (Developer-> Visual basic -> View -> Locals). it really helpful for code compiling and i have it on all the time.

answered Jul 24, 2018 at 1:59

gregV

1,11712 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to view items in array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related