Search code examples
excelvbafilterexcel-formulaxlookup

Converting Excel formula to VBA add-in (FILTER)

I have this formula in a cell, it effectively works a bit like xlookup but actually returns all the matches as a CSV string instead of just the first match

=TEXTJOIN(", ",TRUE,FILTER('[myFile.xlsx]ForLookUp'!$K:$K,'[myFile.xlsx]ForLookUp'!$N:$N=A2))

Where

  • Column K is where the returned values will be pulled from,
  • Column N is where the data range to find matches in lives
  • and A2 is the cell that contains the 'key' I'm trying to match

This works fine but I want to convert it to VBA so I can add it to my Add-In file and reuse

I thought it was going to be relatively simple but I don't think either TEXTJOIN or FILTER exist as-is in VBA? I've been around the web trying to find answers but have not ready found something which works for me.

Ideally for the add-in, both the ranges and the key would be params passed to the routine. Any help would be much appreciated.

Regard

Solution

  • Add this function to your VBA code base

    Function textfilter(dataRange As Range, filter As Variant)
  textfilter = WorksheetFunction.TextJoin(", ", True, WorksheetFunction.filter(dataRange, filter))
End Function

    and call it in the cell like this

    =textfilter('[myFile.xlsx]ForLookUp'!$K:$K,'[myFile.xlsx]ForLookUp'!$N:$N=A2)