For loop with irregular step or use arrays if possible

[kelly mort]

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

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

 

[mikerickson]

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.

 

[kelly mort]

Delete

 

[kelly mort]

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:

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

 

[Peter_SSs]

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.

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.

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

 

[kelly mort]

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.

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.

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.