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]

But how will it know that the filter has been reset, rather than just clicking the button to look at the next filtered row?

If you set the criteria then the first time you initiate GetNextResult it will insert the first result into the textbox. The code will see that the value of the first result is in the textbox and tell the counter to be 1, any result after this will be +1 until it see's the value in the textbox is back to the variable which triggers the counter to be 1 again

 

[Fluff]

I'm sorry, but I simply don't understand what you are attempting to do.
I find it very difficult to reverse engineer code, especially when it is calling other routines, including itself.
Whilst you project is very familiar to you, it isn't to me.

 

[rbsam]

I'm sorry, but I simply don't understand what you are attempting to do.
I find it very difficult to reverse engineer code, especially when it is calling other routines, including itself.
Whilst you project is very familiar to you, it isn't to me.

To simplify it, I'm trying to assign a variable to the first row of filtered rows. Do you know the best way to go about this?

 

[Fluff]

Like

x= Range("C2:C" & Rows.Count).SpecialCells(xlVisible)(1).Value

 

[rbsam]

Like

x= Range("C2:C" & Rows.Count).SpecialCells(xlVisible)(1).Value

Public Sub GetNextResult()


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


    Dim header As String
    header = "[COLOR=#333333]txtbox1[/COLOR]"
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   


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


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


    
    First_Row_Filtered = Range("C6:C" & Rows.Count).SpecialCells(xlVisible)(1).Value
 


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




    CurrentRow = CurrentRow + 1




    Dim i As Long
    Dim cell As Variant
    Static counter As Long
    


    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = counter
    
    




    
    For Each cell In FilteredData


        i = i + 1
        If i = CurrentRow Then
            Call ShowAll
            
            
            TextboxName = "[COLOR=#333333]txtbox1[/COLOR]"
            ActiveSheet.Shapes(TextboxName).DrawingObject.Text = cell.Offset(0, 2)
            
            TextboxName2 = "[COLOR=#333333]txtbox2[/COLOR]"
            ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = cell.Offset(0, 3)
            
            TextboxName3 = "[COLOR=#333333]txtbox3[/COLOR]"
            ActiveSheet.Shapes(TextboxName3).DrawingObject.Text = cell.Offset(0, 4)
              
            
            If ActiveSheet.Shapes(TextboxName).DrawingObject.Text = header Then


                Call GetNextResult
            End If
            
            Call quick_artwork
            
            
        Else
            Call ShowAll


        End If
    


    Next cell


    
    If ActiveSheet.Shapes("[COLOR=#333333]txtbox1[/COLOR]").DrawingObject.Text = First_Row_Filtered Then
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = 1
    Else
    'ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = 7
    counter = counter + 1
    End If
   


End Sub

So this is my updated code. Your last reply worked and returns the value of the first result in column C which is great! Thank you. So now, every time the textbox contains the first result the counter defaults back to 1. The problem I have now in making it count upwards from here. For row 2, the counter should show 2, row 3 the counter should show 3 etc. Right now it seems to be counting all over the place. I'm thinking possibly a loop, but I always find it difficult writing them - any ideas?

EDIT: Just so it's easier to see, First_Row_Filtered is the new variable that is using your code to find the first result row value

 

[Fluff]

From what I can see the counter should increment by +1 every time you run the code.

 

[rbsam]

From what I can see the counter should increment by +1 every time you run the code.

Ah I had a reply over at StackOverflow and someone pointed out that the counter variable isn't resetting inside the if statement.

    If ActiveSheet.Shapes("txtbox1").DrawingObject.Text = First_Row_Filtered Then
    ActiveSheet.Shapes("Cardcounter").TextFrame.Characters.Text = 1
    counter = 2
    Else
    counter = counter + 1
    End If

So this works now. There are 2 issues remaining:

1. When the counter should reset, it actually increases by 1 more for half a second and THEN resets to 1. I'm wondering if there's a way to make it happen instantly?

2. This isn't really related to the counter, but for some reason the GetNextResult button will always insert the 2nd result instead of the 1st when you first click the button. So if you changed the criteria, the first result that would be inserted into the textbox is the 2nd row of the filtered data, not the first. Any idea why?

 

[Fluff]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all relevant links.

 

[rbsam]

This is the question I posted on SO

https://stackoverflow.com/questions/57496932/excel-vba-using-a-counter-to-count-filter-results

EDIT: Ok so problem 1 was resolved by adding application.screenupdating = false at the top of the subroutine and setting it to true at the bottom of the subroutine

 

[Fluff]

Thanks for the link. In future please remember to make it clear you have crossposted & supply links