I have an Advanced Filter set up in my spreadsheet, and rather than showing all of the filtered results at once, I have it set up so it cycles through them one by one. But I have noticed that the first time I click the button that executes the script, it returns the header value first and then cycles through the rest. It doesn't seem to return the header again. So the only time it does it is if you close Excel and reopen the spreadsheet, then execute, it returns the header.
It is made up of 3 subs:
FilterData = Actually does the filtering of the results, this is where I expect I need to put the error message if it doesn't find a match
ShowAll = This resets the filtered results so it shows everything
GetNextResult = My spreadsheet doesn't actually show the filtered results, it replaces 2 text boxes with the values from the filter result and changes on every execution
In case it's relevant:
The data headings are on A5 to I5
So the data begins on A6 to I6
The criteria selection headings are J2 to L2
and the actual selection is J3 to L3
Any ideas? Thanks!
It is made up of 3 subs:
FilterData = Actually does the filtering of the results, this is where I expect I need to put the error message if it doesn't find a match
ShowAll = This resets the filtered results so it shows everything
GetNextResult = My spreadsheet doesn't actually show the filtered results, it replaces 2 text boxes with the values from the filter result and changes on every execution
Code:
Public Sub FilterData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim CriteriaRange As Range
Set CriteriaRange = ws.Range("J2", "L3")
Dim DataRange As Range
Set DataRange = ws.Range("A5", "I" & LastRow)
DataRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=CriteriaRange, Unique:=False
End Sub
Code:
Public Sub ShowAll()
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub
Code:
Public Sub GetNextResult()
FilterData
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim DataRange As Range
Set DataRange = ws.Range("A5", "I" & 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
For Each Cell In FilteredData
i = i + 1
If i = CurrentRow Then
Call ShowAll
TextboxName = "Box1"
ActiveSheet.Shapes(TextboxName).DrawingObject.Text = Cell.Offset(0, 2)
TextboxName2 = "Box2"
ActiveSheet.Shapes(TextboxName2).DrawingObject.Text = Cell.Offset(0, 3)
Call quick_artwork
End If
Next Cell
In case it's relevant:
The data headings are on A5 to I5
So the data begins on A6 to I6
The criteria selection headings are J2 to L2
and the actual selection is J3 to L3
Any ideas? Thanks!