For loop with irregular step or use arrays if possible

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,875
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,687
Office Version
2016
Platform
Windows
Delete
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,687
Office Version
2016
Platform
Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,561
Office Version
365
Platform
Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,687
Office Version
2016
Platform
Windows
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.
 

Forum statistics

Threads
1,084,880
Messages
5,380,425
Members
401,677
Latest member
BobH

Some videos you may like

This Week's Hot Topics

Top