Find nth .FindNext item

[STEVEMILLS04]

I currently use .Find to find the first instance of a value then use .FindNext to find the second instance. It works great because I use it to insert a page break after the second instance.

Now I need it to find the 10th instance of a value. If there are less than 10 the pagebreak will be at the end anyway, if there are more than 10 it should add it after the every 10th. Below is my code for every other (I am sure it can be much improved but I am no expert and it works!) but I don't know the best way to accomplish my goal for every 10th item.

Any suggestions? Also, does .Find and .FindNext only work on unhidden columns?

Sub insertPageBreaks()
    Dim oRange As Range, allRange As Range
    Dim ws As Worksheet, SearchString As String, firstFind As String, secondFind As String
          
    Set ws = ActiveSheet
    Set oRange = ws.Range("A:A")
      
    SearchString = "Total"
    
    Application.ScreenUpdating = False
    
    Columns("A").Hidden = False
    
    lastrow = ActiveSheet.UsedRange.Rows.Count
    lastRange = Range("A" & lastrow)
    
    With ws
       Set aCell = .Range("A:A").Find(What:="Total", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart)
    End With
    
    
            
    If Not aCell Is Nothing Then
        'ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.ResetAllPageBreaks
        

        firstFind = aCell.Address
        If Range("rgnVisibility").Value = "Visible" Then
            Do
            
                If Not aCell Is Nothing Then
                    Set bCell = ws.Range("A:A").FindNext(After:=aCell)
                    
                    If bCell.Address = firstFind Then
                        Exit Do
                    End If
                
                    secondFind = bCell.Address
    
                    With ws
                        .HPageBreaks.Add .Range("propStart")
                        .HPageBreaks.Add .Range(bCell.Address).Offset(2, 0)
                    End With
    
                    Set aCell = ws.Range("A:A").FindNext(After:=bCell)
                    
                    If aCell.Address = firstFind Then
                        Exit Do
                    End If
                Else
                    Exit Do
                End If
            Loop
        End If
    End If
    
    Columns("A").Hidden = True
    
    Application.ScreenUpdating = True
End Sub

 

[tonyyy]

STEVEMILLS04,

To find the 10th item, you can count the number of iterations. For example...

Dim kount As Long
Do
    kount = kount + 1
    If kount = 10 Then
        MsgBox "Kount is " & kount
        Exit Do
    End If
Loop

And does .Find and .FindNext only work on unhidden columns? That's what my quick testing indicates. To get around this you can sandwich the .Find statement between .Hidden statements...

Columns(3).Hidden = False
Set r = Range("A:E").Find(What:="Sunday", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart)
Columns(3).Hidden = True

Cheers,

tonyyy

 

[STEVEMILLS04]

Thanks Tonyyy... I will give it a shot and report back.