For loop with irregular step or use arrays if possible

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hi again,

I got something I think is a big problem for me but I don't know what you think:

I have a loop like

Code:
For i  = 1 To 100
 .........
Next

The loop is pointing to numbers in column B. However, all the numbers may not be present. For example, 2 or 54 etc, may not be available in the list. And in this case, the vlookup I am using will return that N/A .

If that alone, I would have a way to clear that. But I am also printing with the same loop as well.

Still I can set a condition to skip the print when that error shows up.

But the issue is, from that 1 To 100 used for the loop counter, there may be only two numbers found in the column B.

And in this case, running all the 100 counters seem to me as a waste of time and memory.

So it occurred to me that if I am able to load that column B to an array and then use the array index for the loop counter, then I can be able to take care of all the concerns I have raised above.

I hope someone has the cure for this.

Thanks
 
You miss my point. I'm saying that you should use your current loop. Just add code that tests for the VLookup, if it doesn't exist then don't do anything, just go to the next loop.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi mike,

So I was playing around with cooler ways to test for the availability of the items in column B.

The one I chose to use is:

Code:
If Application.CountIf(Range,Item)= 1 Then
       Do something
End If

And its working fine.

I appreciate the clue given.

Thanks to you @offthelip too
 
Upvote 0
You shouldn't have to do any loop from 1 to 100 to find a few values if you don't want to


So let say I have the numbers 34, 5, 27 in the column B
If they are in consecutive cells starting from, say B2 then you could use something like this to just grab the relevant values & just loop through those values.

Rich (BB code):
Sub getvaluesintoarray1()
  Dim a As Variant, itm As Variant
  
  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  For Each itm In a
    'Do something
  Next itm
End Sub

If the values may be scattered anywhere in, say, B2:B100 (& the rest of the cells are blank) then you can use something like this to just grab those values and loop through them.

Rich (BB code):
Sub getvaluesintoarray2()
  Dim a As Variant, itm As Variant
  
  a = Filter(Application.Transpose(Evaluate("if(len(B2:B100),B2:B100,""%"")")), "%", False)
  For Each itm In a
    'Do something
  Next itm
End Sub
 
Upvote 0
You shouldn't have to do any loop from 1 to 100 to find a few values if you don't want to


If they are in consecutive cells starting from, say B2 then you could use something like this to just grab the relevant values & just loop through those values.

Rich (BB code):
Sub getvaluesintoarray1()
  Dim a As Variant, itm As Variant
  
  a = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  For Each itm In a
    'Do something
  Next itm
End Sub

If the values may be scattered anywhere in, say, B2:B100 (& the rest of the cells are blank) then you can use something like this to just grab those values and loop through them.

Rich (BB code):
Sub getvaluesintoarray2()
  Dim a As Variant, itm As Variant
  
  a = Filter(Application.Transpose(Evaluate("if(len(B2:B100),B2:B100,""%"")")), "%", False)
  For Each itm In a
    'Do something
  Next itm
End Sub

Your suggestions are very great points for me to look at.

I just realised how useful the For Each Loop can be for what I am doing.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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