Looping through an Array

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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

Code:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
When you load an array in that manner it's a 2d array rather than a 1d array. Try
Code:
Debug.Print Arr(p, 1)
Or depending on data
Code:
Arr = Application.Transpose(Range("B1:B" & Lastrow))
For p = LBound(Arr) To UBound(Arr)
Debug.Print Arr(p)
Next p
 
Last edited:
Upvote 0
When you load an array in that manner it's a 2d array rather than a 1d array. Try
Code:
Debug.Print Arr(p, 1)
Or depending on data
Code:
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
 
Upvote 0
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

Code:
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).
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
Roderick_E

What are you actually doing with the array?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top