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]

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.

I'm using an advanced filter so should be okay then. I'll test it tomorrow and let you know how it went - thanks for your help

 

[Fluff]

You're welcome

 

[rbsam]

So I'm trying to get the value of the first filtered result row (column c's value) so I can use this as part of an if statement to define it as being number 1 in the counter

When I use this
debug.print FilteredData.Cells(2, 3).Value

It returns the first row column c value when all the data is showing rather than the actual filtered data, I have no idea why because FilteredData is supposed to have already filtered the results. Maybe this is the issue I mentioned earlier about filters not removing results but just resizing the rows until they're not visible?

 

[Fluff]

Your code makes no real sense to me, partly because I cannot see your data, partly because you are calling other procedures and partly because I have no idea what you are trying to do.

Can you please explain what you are trying to do.

 

[rbsam]

Your code makes no real sense to me, partly because I cannot see your data, partly because you are calling other procedures and partly because I have no idea what you are trying to do.

Can you please explain what you are trying to do.

So I'm trying to assign a variable to the first result row (of column C preferably). The reason for doing this is so I can put an if statement in with the counter, if the textbox contains the value of the first result C & row then counter =1. This ensures the counter always resets back to 1 when a new filter criteria is set.

EDIT:

Just to explain a little about what these 2 subroutines are doing.

One is filtering the data based on a criteria, normal filtering.

The other (GetNextResult) is inserting values from the filtered rows and putting them into textboxes, and each time you click the macro button it will insert a different result row into the textbox. The purpose of having a counter is so the user doesn't loop round and insert the same results over and over without realising.

 

[Fluff]

I still don't understand.
Please remember, that I cannot see your scree, or your data. And have no idea what you are trying to do with the code you posted in your op.

 

[rbsam]

I still don't understand.
Please remember, that I cannot see your scree, or your data. And have no idea what you are trying to do with the code you posted in your op.

Imagine you have multiple rows of data. The first 2 columns of these rows have titles in them. You have an ordinary filter where you can select 2 titles and defines the criteria for the filter, so it only shows rows that contain the 2 titles you selected. That's the first filter.

The next subroutine, GetNextResult, utilises the filter, but rather than the user having to read the results line by line, it inserts certain values from the results row (for example the value of column C rows) into a textbox. So every time you click this button, a new value from the filtered results is put into the textbox.

The problem with this is, lets say there are only 3 matching results, and you click the button 4 times, the 4th time you are given the first result again, as it loops round. It may not be immediately obvious to the user that they are seeing the first result again. This is why I want a counter.

Does this make it any clearer? Trying my best

 

[Fluff]

What happens if somebody looks at the first two (of 3) filtered rows & then changes the filter?

 

[rbsam]

Then once they've clicked the button, the variable that looks for the first result row will tell the counter to be 1 as that is the default position

 

[Fluff]

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