Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft