<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Restricting Excel VBA Loops

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

EXCEL VBA: Restricting Loops

I would like to show you 2 ways to restrictlooping by using theCOUNTIF Function with theFind 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 RangeDim 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 WithEnd Sub

Sub RestrictLoop2PartCellMatch()Dim rFound As RangeDim 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 WithEnd 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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