TEXTJOIN with IF Statement VBA

[Jean Lewis]

Hello,

I have Excel 2016 so I don't have the TEXTJOIN function automatically. Thanks to this article, I have been able to recreate it (function is named my_text_join) through VBA:

Text Join Formula for all Excel Versions

TEXTJOIN Function was introduce with the release of Excel 2016. This is very helpful function to join the multiple text into one. The best thing is that it allows us to select an entire range of cell references to be joined. So it is very time saving formula. But the problem is this formula is...

www.pk-anexcelexpert.com

and it works great if I am only doing the my_text_join function (=my_text_join(";",TRUE,RANGE); however, I need to do something a little extra, which is to join a a specific row of cells (in this case, header cells) only if different/specific cells are not empty. Below is an example of the outcome for this formula - as you can see, it joined the header text 1, 2 and 4 since only the cells below that header had text.

I have been able to do this successfully in EXCEL 2019 with this formula =TEXTJOIN(";",TRUE,IF(A5:D5<>"",A$1:D$1,"")) to only join header cells when the reference cell is populated, but I can't get it to work in the Excel 2016 version where I don't have access to TEXTJOIN and have to use the VBA workaround =My_Text_Join(";",TRUE,IF(A2:D5<>"",A$1:D$1,"")).

I'm not sure if the fix is an alteration to the VBA code or to the formula. Below is the VBA code I used, provided by Advanced Excel and VBA tutorials. Any help would be appreciated as I'm currently sending files back and forth between my work laptop and personal laptop since my work laptop doesn't have Excel 2019/365).

Option Explicit

Function My_Text_Join(delimiter As String, ignore_empty As Boolean, text_range As Range) As String

Application.Volatile
Dim c As Range
Dim n As Long
n = 0
For Each c In text_range
If ignore_empty = True Then
If VBA.IsEmpty(c.Value) = False Then
If n = 0 Then
My_Text_Join = c.Value
Else
My_Text_Join = My_Text_Join & delimiter & c.Value
End If
n = n + 1
End If

Else

If n = 0 Then
My_Text_Join = c.Value
Else
My_Text_Join = My_Text_Join & delimiter & c.Value
End If
n = n + 1

End If
Next

End Function


Thank you!

 

[Sulprobil]

Hello,

I suggest to use my UDF Textjoin:

Confusion Of Join Method

I'm creating an app for user to select data but they don't have to select all the data. For example, they can choose to include some columns, but not the others. One thing that is known is the data selected will have the same rows(so if the user selects A1:A5 and C2:C5, then that's the user...

www.mrexcel.com

 

[Jean Lewis]

@Sulprobil Thank you for your reply. I pasted that VBA into a new module but I don't really understand how to use it the way I need. I attached a minisheet showing what I am trying to do with the original VBA I was using. Again, this formula works with the standard TEXTJOIN function in Excel 2019, but not the one I created with VBA.

Book1.xlsx
	A	B	C	D	E	F	G
1	Header 1	Header 2	Header 3	Header 4	Header 5	TEXTJOIN with IF Statement	Correct Value
2	Yes		Yes	Yes		#NAME?	Header 1; Header 3; Header 4
3		Yes		Yes	Yes	#NAME?	Header 2; Header 4; Header 5
4	Yes	Yes	Yes			#NAME?	Header 1; Header 2; Header 3
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=My_Text_Join(";",TRUE,IF(A2:E2<>"",A$1:E$1,""))