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 06:48 AM
@Weaver1967 I must admit that I have no idea!
This, however, seems to work:
Dim v As Variant
For Each v In [FILTER(Table1[column1],Table1[Column2]="x")]
MsgBox v
Next
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 07:50 AM
Jan 10 2023 07:57 AM
Jan 10 2023 07:57 AM
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 11 2023 04:47 AM
Jan 12 2023 12:50 AM
Tried this:
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 AM
SolutionJan 12 2023 01:54 AM
Solution