Forum Discussion
Solved
Using new 'spill range' formulas in Excel VBA
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, me...
- I think you need to get rid of the square brackets in the string, the Evaluate function effectively replaces them
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
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 WithI'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- Get rid of the quotes, simply write it as if it were a regular Excel formula
- PS how would you achieve a result if the table and/or field names were dynamic?
