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
ABB