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!

 

[XLfriend777]

Hi rbsam. Try putting the counter = counter + 1 inside the "For each cell" loop. That way every time that loop is executed the counter will increment. Hope this helps.

 

[rbsam]

Hi rbsam. Try putting the counter = counter + 1 inside the "For each cell" loop. That way every time that loop is executed the counter will increment. Hope this helps.

So I've put this directly above the For loop

TextboxName3 = "Cardcounter"ActiveSheet.Shapes(TextboxName3).DrawingObject.Text = counter
counter = 1

And directly under the For Each line I've put

counter = counter + 1

And it doesn't write the 1 inside the textbook, so it doesn't even get as far as counting up. Not sure where I've gone wrong - thanks for your help though!

 

[Fluff]

Try moving it into the loop like

   Dim cell As Variant
   
   For Each cell In FilteredData
      counter = counter + 1
      ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter

       i = i + 1

 

[rbsam]

Try moving it into the loop like

   Dim cell As Variant
   
   For Each cell In FilteredData
      counter = counter + 1
      ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter

       i = i + 1

Do I not need to set the default value to 1?

 

[Fluff]

Nope, it will become 1 the first time through the loop

 

[rbsam]

Nope, it will become 1 the first time through the loop

Ok so I just got round to doing this. I have no idea why but for some reason this inserts a 5 into the counter box and it doesn't change when I trigger the macro each time

EDIT: The only reason I can think there would be a 5 is the filter produces 4 results and this counter is adding 1 to it

 

[Fluff]

The code is probably running so fast that you don't see the counter change.

 

[rbsam]

The code is probably running so fast that you don't see the counter change.

The counter should only go up by 1 increment upon each button click. It's not as if it should cycle through all the numbers in a loop upon 1 button click.

 

[Fluff]

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