Iterate through multiple Excel Filters in VBA

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
ArticalBadboy
Posts: 106
Joined: Tue Feb 14, 2017 1:43 pm

Hello all,
I have a data set of historic horse races, including results of a couple of strategies I'm testing.
To fine tune them, I am experimenting with filtering out criteria. e.g What if I omitted sprint races, all weather courses etc
I am doing this manually and I am about to embark on programming this via VBA.
Before I start, has anyone done this before, or can anyone think of a better way than this? (I thought about Solver or Goal Seek but couldn't see how to apply them to this problem)

I plan to extract the unique items from each column I want to filter and hold them in arrays.
I will then iterate through each column, treating each criteria as a binary option whereby it's either selected or not selected.
Each columns' iteration will be nested inside the previous one, so if there were three columns each with three unique items, the automation would iterate from "all off" (000000000), to "all on" (111111111) and include every variation in between.

Code: Select all

Dim n As Long
For n1 = 1 To Array1
    ActiveSheet.Range("$A$2:$X$344").AutoFilter Field:=1, Criteria1:=Array1(n1, 1)
          For n2= 1 To Array2
              ActiveSheet.Range("$A$2:$X$344").AutoFilter Field:=2, Criteria1:=Array2(n2, 1)
                   'Export P&L figure
          Next
Next
Thanks in advance
ABB
Anbell
Posts: 2004
Joined: Fri Apr 05, 2019 2:31 am

ArticalBadboy wrote:
Thu Aug 25, 2022 10:54 am
Hello all,
I have a data set of historic horse races, including results of a couple of strategies I'm testing.
To fine tune them, I am experimenting with filtering out criteria. e.g What if I omitted sprint races, all weather courses etc
I am doing this manually and I am about to embark on programming this via VBA.
Before I start, has anyone done this before, or can anyone think of a better way than this? (I thought about Solver or Goal Seek but couldn't see how to apply them to this problem)

I plan to extract the unique items from each column I want to filter and hold them in arrays.
I will then iterate through each column, treating each criteria as a binary option whereby it's either selected or not selected.
Each columns' iteration will be nested inside the previous one, so if there were three columns each with three unique items, the automation would iterate from "all off" (000000000), to "all on" (111111111) and include every variation in between.

Code: Select all

Dim n As Long
For n1 = 1 To Array1
    ActiveSheet.Range("$A$2:$X$344").AutoFilter Field:=1, Criteria1:=Array1(n1, 1)
          For n2= 1 To Array2
              ActiveSheet.Range("$A$2:$X$344").AutoFilter Field:=2, Criteria1:=Array2(n2, 1)
                   'Export P&L figure
          Next
Next
Thanks in advance
ABB
Pivot Tables work pretty well
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”