---

Restricting Excel VBA Loops

EXCEL VBA: Restricting Loops

I would like to show you 2 ways to restrict looping by using the  COUNTIF function with the find method. The 1st code uses a whole cell match, while the 2nd uses a part cell match.

The key thing to note in both codes is our use of the range variable rFound in the Find Method parameter for After: That is, After:=rFound. By using this we can move down the Column and find all matches. If we didn't use this, we always find the 1st match over and over again.

Sub RestrictLoop1WholeCellMatch()
Dim rFound As Range
Dim lLoop As Long

    With Range("A:A")
        'Set our range variable to the 1st cell in Column A
        Set rFound = .Cells(1, 1)
        
        'Use COUNTIF to restrict our looping
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "Dave")
            'Use the Find Method and set each parameter to suit whole cell match
            Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                
                    With rFound
                        'Your .With code here
                    End With
        Next lLoop
    End With
End Sub

Sub RestrictLoop2PartCellMatch()
Dim rFound As Range
Dim lLoop As Long

    With Range("A:A")
        'Set our range variable to the 1st cell in Column A
        Set rFound = .Cells(1, 1)
        
        'Use COUNTIF to restrict our looping
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "*Dave*",)
            'Use the Find Method and set each parameter to suit part cell match
            Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                
                    With rFound
                        'Your .With code here
                    End With
        Next lLoop
    End With
End Sub

 

See also:

Do While Loop, Repeat a Block of VBA Macro Code

Used to Loop Through a Collection or Array

For Loop

For Loop Step

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•