Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates