TEXTJOIN Alternative

[mightymo77]

Hey guys. I was reading through some posts and everyone here seems very helpful. I have this formula below, however it's not working on my work machine which uses Excel 2016. Can anyone create an alternative for me that will function in Excel 2016? I would appreciate any help!

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"BLACK,BLUE")),$F$1:$T$1,""))

 

[JEC]

Ahh you had to leave Dim before ar, sorry for confusing

 

[Fluff]

The code has to be in the workbook that you want to use it in.

 

[mightymo77]

Ahh you had to leave Dim before ar, sorry for confusing

Can you post the whole code please.

 

[JEC]

Function jec(searchRng As Range, mergeRng As Range) As String
 Dim ar As Variant, i As Long, c00 As String
 Application.Volatile
 
 ar = searchRng
 For i = 1 To UBound(ar, 2)
    If ar(1, i) = "WARNING" Or ar(1, i) = "FAIL" Then c00 = c00 & ", " & mergeRng(1, i)
 Next
 jec = Mid(c00, 3)
End Function

=jec(F2:T2;$F$1:$T$1)

 

[mightymo77]

The code has to be in the workbook that you want to use it in.

This is what I am doing:

1 - Opening my csv

2 - Press ALT + F11 for a VBA window > Expand VBAProject, right-click Modules folder, insert, Module > Paste JEC's code > close that little window, close the XLSB window (where the module folder is on the left) > This brings me back to the regular csv file

3 - Paste JEC's second code into X2. Getting the error.

 

[Fluff]

See post#22.
Also csv files do not retain formulae when you save & close them.

 

[mightymo77]

See post#22.
Also csv files do not retain formulae when you save & close them.

I'm not saving and closing the csv file. Please post #25 and follow what I am doing closely. If I am doing something wrong let me know.

 

[Fluff]

I'm not saving and closing the csv file.

Then what's the point of putting a formula in it?

 

[mightymo77]

Please see the attached image. After pasting the code in the module, I am close window 2, then 1, which puts me back in window 3 (which is my csv file). If I press ALT + F11 again, the code is still in the module. So, is my process wrong?

 

[Fluff]

Maybe you didn't read post#22.
Also if you put the formula into the csv file & then save it as a csv file, you will loose the formula. Is that what you want?