Including a counter within an advanced filter

[rbsam]

I have an advanced filter set up and separate to that subroutine I have another subroutine that loops through the filter results and places the value into a text box. That all works fine but I'm trying to include a counter so for every result that's being displayed the counter goes up, and then when the criteria changes and new results are found it goes back to 1 and counts again from there.

Public Sub GetNextResult()

    FilterData

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Database")


    Dim header As String
    header = "txtbox1"
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row


    Dim DataRange As Range
    Set DataRange = ws.Range("A5", "H" & LastRow)


    Dim FilteredData As Range
    Set FilteredData = DataRange.Resize(ColumnSize:=1).SpecialCells(xlCellTypeVisible)


    If CurrentRow + 1 > FilteredData.Cells.Count Then
        CurrentRow = 1
        
    End If
    
    


    CurrentRow = CurrentRow + 1
    Dim i As Long
    Dim cell As Variant
    
    counter = 1
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter
    counter = counter + 1


    
    For Each cell In FilteredData
        
        i = i + 1
        
        If i = CurrentRow Then
         
            Call ShowAll
            
        
            
            
            TextboxName = "txtbox1"
            ActiveSheet.Shapes(TextboxName).DrawingObject.Text = cell.Offset(0, 2)
            
            TextboxName2 = "txtbox2"
            ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = cell.Offset(0, 3)




                        
            
            If ActiveSheet.Shapes(TextboxName).DrawingObject.Text = header Then
                'MsgBox "header detected"
                'Exit Sub
     
                Call GetNextResult
                
            End If
            
            Call quick_artwork
            
            
            
        Else
            Call ShowAll


        End If
    


    Next cell
    




End Sub
[COLOR=#242729][FONT=Arial]
[/FONT][/COLOR]

So I've put

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; font-stretch: inherit; line-height: inherit; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">counter = 1</code>

before the for loop and then within the for loop I've put

<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; font-stretch: inherit; line-height: inherit; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">counter = counter + 1</code>

and I thought that would work but it doesn't. It doesn't show an error, but the number doesn't change.

I'm sure I have it in the wrong location, but I can't figure out where. If there are 5 results, the counter should only go to 5 and then when the subroutine returns the first result again the counter is back to 1

Any help would be appreciated!

 

[rbsam]

In that case try

    Dim i As Long
    Dim cell As Variant
    Static counter As Long
    
    counter = counter + 1
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter


    
    For Each cell In FilteredData

Okay so this has made the counter start to count upwards. But 2 things are happening:
1. When all of the results that were filtered have been cycled through, the counter should reset back to 1 but it keeps counting up
2. When the criteria for the filter is changed, it should reset back to 1, but instead keeps counting upwards

Thanks for your help though, we're getting closer!

 

[Fluff]

How will the code know when to reset?

 

[rbsam]

How will the code know when to reset?

When it has inserted the first filtered result, that should be linked to 1 so everything after it goes upwards until it gets back to the first result again which triggers the counter to go back to default position of 1

The idea behind doing this is right now I have a macro for filtering, which shows all of the entires, then I have another macro which inserts the data from each row the filter one by one into a textbox. The reason for the counter is to ensure it's easy to see when you're going in loop so you don't keep clicking it and not being aware of the fact you're seeing a result you've already seen before

 

[Fluff]

What is there in your code that says you have gone back to the start?

 

[rbsam]

Currently it’s not there. That’s what I’m trying to figure out. I was thinking of using the match function as that picks out the first result that fits the criteria, right? So that should return the row number, and then I could assign the 1 to that so if that result is displayed then the counter is 1

 

[Fluff]

Yup, that should work.

 

[rbsam]

Yup, that should work.

Alternatively, is there a way to get the number of rows returned by a filter? I could then use a loop for the counter to match the number of increments of results filtered

 

[Fluff]

Something like

   MsgBox Range("H1", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count

 

[rbsam]

Something like

   MsgBox Range("H1", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Count

I won't be back to my computer until tomorrow to test it but will that work? Because the filter works by making the unwanted rows really small so they're not visible, and I worry that doing the end xl up won't know which cells to count

 

[Fluff]

It will work if you are using either advanced filters or Auto filters. If you are simply reducing the row height then it probably won't.