How To Pass If Condition To Excel VBA Text Join?

user_6672597 0
Viewed 316

How does one pass an if condition in Textjoin into the VBA equivalent? ie. my working formula in Excel currently is:

=TEXTJOIN("",TRUE,IF(M1:M100=1,N1:N100,""))

This is to join rows in column N if the flag is set in column M.

What I am trying to get is to replicate this in VBA, something like:

abc = WorksheetFunction.TextJoin(" ", True, IF(Range(M1:M100)=1,Range(N1:N100),"")

But can't seem to work out how to pass the criteria to the worksheet function. Any ideas?

I guess the question also relates to WorksheetFunction.Filter, on how you can pass a filter condition to it, something like:

 WorksheetFunction.Filter(Range("M:M"), Range("N:N") & "=1", False)

I say this as if I can work out the filter, I can probably just pass that range to the text join directly.

Thanks!

textjoin worksheet-function vba excel
1 Answers

TEXTJOIN With an If Conditioned Array in VBA

enter image description here

  • The image shows that your formula isn't quite correct. By introducing the If statement, the second argument has become irrelevant. You have chosen True to skip blanks but they won't be skipped. You can cover for it with another (nested) If statement.
  • The three examples in the following procedure show how you can use a VBA version of your idea. The first two are mostly based on the Evaluate method, while the third uses the function below.
  • I don't have Office 365, so you could expect another answer using those new functions it contains.
Option Explicit

Sub Test()
    With ActiveSheet
        
        ' 1.) Evaluate
        Debug.Print .Evaluate("=TEXTJOIN("""",FALSE," _
            & "IF(M1:M100=1,IF(N1:N100="""","""",N1:N100),""""))")
        
        ' 2.) Worksheet Function + Evaluate
        Debug.Print WorksheetFunction.TextJoin("", False, _
            .Evaluate("IF(M1:M100=1,IF(N1:N100="""","""",N1:N100),"""")"))
        
        ' 3.) Function (UDF)
        Debug.Print TextJoinIf(.Range("M1:N100"), 2, 1, 1, "")
    
    End With
End Sub
  • You can use the following function in VBA or if you have Office prior to 2019, also as a UDF. Skipping blanks is assumed.
Function TextJoinIf( _
    ByVal rg As Range, _
    ByVal ConcatColumn As Long, _
    ByVal FlagColumn As Long, _
    ByVal Flag As Variant, _
    Optional ByVal Delimiter As String = " ") _
As String
    Const ProcName As String = "TextJoinIf"
    On Error GoTo ClearError
    
    Dim Data As Variant: Data = rg.Value
    Dim sr As Long
    Dim rString As String
    For sr = 1 To rg.Rows.Count
        If Data(sr, FlagColumn) = Flag Then
            If Len(Data(sr, ConcatColumn)) > 0 Then
                rString = rString & Data(sr, ConcatColumn) & Delimiter
            End If
        End If
    Next sr
    If Len(rString) > 0 Then
        TextJoinIf = Left(rString, Len(rString) - Len(Delimiter))
    End If

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function
user_98140690
Related