Limit VBA Loops

  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"><html><head><meta http-equiv="Content-Language" content="en-us"><title>Ways To Restrict Loops in Excel VBA</title><meta name="description" content="Ways To Restrict Loops in Excel VBA"><meta name="keywords" content="Ways To Restrict Loops in Excel VBA"><link rel="STYLESHEET" href="http://www.ozgrid.com/css/ozgrid.css" type="text/css"><script type="text/javascript" src="http://www.ozgrid.com/SideNavJS/stmenu.js"></script>
    </head><body><div align="center"></div><h1>Restricting Excel VBA Loops</h1><h2>EXCEL VBA: Restricting Loops</h2><p class="j">I would like to show you 2 ways to restrict <a href="http://www.ozgrid.com/VBA/VBALoops.htm">looping</a> by using the <a href="http://www.ozgrid.com/Excel/count-if.htm">COUNTIF Function</a> with the <a href="http://www.ozgrid.com/VBA/find-method.htm">Find Method</a>. The 1st code uses a <b>whole</b> cell match, while the 2nd uses a <b>part</b> cell match.</p><p class="j">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, <b>After:=rFound.</b> 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.</p>
    <pre>
    <b>Sub RestrictLoop1WholeCellMatch()</b>
    Dim rFound As Range
    Dim lLoop As Long


    With Range(&quot;A:A&quot;)
    <font color="#008000">'Set our range variable to the 1st cell in Column A</font>
    Set rFound = .Cells(1, 1)
    <font color="#008000">
    'Use COUNTIF to restrict our looping</font>
    For lLoop = 1 To WorksheetFunction.CountIf(.Cells, &quot;Dave&quot;)
    <font color="#008000">'Use the Find Method and set each parameter to suit whole cell match</font>
    Set rFound = .Find(What:=&quot;Dave&quot;, After:=rFound, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    With rFound
    <font color="#008000">'Your .With code here</font>
    End With
    Next lLoop
    End With
    <b>End Sub</b>
    </pre>
    <pre><b>Sub RestrictLoop2PartCellMatch()</b>
    Dim rFound As Range
    Dim lLoop As Long


    With Range(&quot;A:A&quot;)
    <font color="#008000">'Set our range variable to the 1st cell in Column A</font>
    Set rFound = .Cells(1, 1)

    <font color="#008000">'Use COUNTIF to restrict our looping</font>
    For lLoop = 1 To WorksheetFunction.CountIf(.Cells, &quot;*Dave*&quot;,)
    <font color="#008000">'Use the Find Method and set each parameter to suit part cell match</font>
    Set rFound = .Find(What:=&quot;Dave&quot;, After:=rFound, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    With rFound
    <font color="#008000">'Your .With code here</font>
    End With
    Next lLoop
    End With
    <b>End Sub</b></pre></body></html>