Converting Excel formula to VBA add-in (FILTER)-CodePudding

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

CodePudding user response：

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)