Jan 09 2023 08:27 AM
Jan 09 2023 08:27 AM
I thought it would be useful to use the 'filter' function from Excel in an Excel VBA script. I had a table where I needed the results from one column, dependent on the value of the second column, meaning I couldn't use the VBA filter function.
So I figured you should be able to access the FILTER() function via 'worksheetfunction' but I got errors (see below) which made me realise that I don't know what data type the function passes back to VBA or even if it'll work. I tried a number of ways of referring to the columns required, including as elements of a listObject but I think the real problem is either understanding what data type the function is trying to return or whether or not these new 'spill' formulas even work in VBA. In the end I solved the problem with a sledgehammer (i.e. looping through all the rows in the table and building an array from all the matching data) but I'd still like to know if it's possible to make the "worksheetfunction" approach work.
Update: I'm thinking the problem here is with the 'include' parameter, since I just tried using SORT() with the worksheetfunction and that worked.
for each f in worksheetfunction.filter(range("table1[column1]"),range("table1[column2]")="X") 'do stuff next f
Jan 10 2023 07:07 AM
I couldn't get this very Pythonesque syntax to work, type mismatch error
Dim v As Variant With Me.Controls(dropName) .Clear For Each v In [Filter(causes["ListValue"],causes["ListName"]=listName)] .AddItem v Next v End With
I'm using this instead, which works but looks amateur as heck!
Dim LO As ListObject Set LO = Sheets("Pick Lists").ListObjects("Causes") With Me.Controls(dropName) .Clear For r = 1 To LO.ListRows.Count If LO.Range.Cells(r + 1, 1).Value = listName Then .AddItem LO.Range.Cells(r + 1, 2) End If Next r End With
Jan 10 2023 08:02 AM
Private Sub testfilter() Dim v As Variant, str As String str = "Shut Down Reason" For Each v In [filter(causes[listValue],Causes[ListName]=str)] Debug.Print v Next v End Sub
Doesn't seem to work if you have to use a variable as the condition.
Jan 10 2023 08:56 AM
Jan 12 2023 12:50 AM
Private Sub testfilter() Dim v As Variant, StrCondition As String, strEval As String StrCondition = "Shut Down Reason" strEval = "[filter(causes[listValue],Causes[ListName]=""" & StrCondition & """)]" For Each v In Evaluate(strEval) Debug.Print v Next v End Sub
Still getting type mismatch.
Jan 12 2023 01:54 AMSolution