Looping through an Array

[Roderick_E]

I'm trying to use an array instead of a range so I can loop through it more efficiently. I thought I understood the various documentation I researched but can't get it to work. Here's my set up/code:
Please help explain why I'm not getting a result. Thanks

Dim Arr() As Variant
lastrow = 0 'reset
lastrow = plwbk.Sheets(1).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Arr = plwbk.Sheets(1).Range("B1:B" & lastrow)
For p = LBound(Arr) To UBound(Arr)
Debug.Print Arr(p)
next p

DEBUG isn't returning anything

 

[Fluff]

When you load an array in that manner it's a 2d array rather than a 1d array. Try

Debug.Print Arr(p, 1)

Or depending on data

Arr = Application.Transpose(Range("B1:B" & Lastrow))
For p = LBound(Arr) To UBound(Arr)
Debug.Print Arr(p)
Next p

 

[Roderick_E]

When you load an array in that manner it's a 2d array rather than a 1d array. Try

Debug.Print Arr(p, 1)

Or depending on data

Arr = Application.Transpose(Range("B1:B" & Lastrow))
For p = LBound(Arr) To UBound(Arr)
Debug.Print Arr(p)
Next p

Upon more reading, your first answer fixes my situation but I still have some other issues I'll report back in a second. Thanks

 

[Rick Rothstein]

I'm trying to use an array instead of a range so I can loop through it more efficiently. I thought I understood the various documentation I researched but can't get it to work. Here's my set up/code:
Please help explain why I'm not getting a result. Thanks

Dim Arr() As Variant
lastrow = 0 'reset
lastrow = plwbk.Sheets(1).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Arr = plwbk.Sheets(1).Range("B1:B" & lastrow)
For p = [B][COLOR="#FF0000"]LBound(Arr)[/COLOR][/B] To UBound(Arr)
Debug.Print Arr(p)
next p

DEBUG isn't returning anything

Fluff has answered your direct question, but I just wanted to point out for interest sake that the lower bound for each dimension for an array created by assigning a range to a Variant variable is always 1 (of course, if you like, you can do it the way you show, but I just thought I would mention that to you).

 

[Roderick_E]

Fluff has answered your direct question, but I just wanted to point out for interest sake that the lower bound for each dimension for an array created by assigning a range to a Variant variable is always 1 (of course, if you like, you can do it the way you show, but I just thought I would mention that to you).

Thanks for the add, I knew array actually start with 0, if that's what you're trying to tell me. The problem I'm suffering now is I haven't gained any speed loops through 26K rows or making an array then looping. Hmm

 

[Rick Rothstein]

Thanks for the add, I knew array actually start with 0, if that's what you're trying to tell me.

No, what I am trying to tell you is arrays form by assigning ranges to Variant variable always start at 1 (contrary to the lower bound for normal VB arrays which is 0 unless the Option Base was set to 1).

 

[Norie]

Roderick_E

What are you actually doing with the array?

 

[Roderick_E]

Roderick_E

What are you actually doing with the array?

I was looping through it and copying data to a spreadsheet if it matches a criteria.

 

[Norie]

Have you considered using an array for the results?

i.e. loop through the input array and, based on the criteria, populate another array and then use the second array to put the values in the sheet