Conditional IF function

[Fwiz]

Hi all,

I have a little problem with my code where I've added VB newline which works perfect but it has a new line break on the before each record in a cell, here is what i have done in the code:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
strResult = strResult & vbNewLine & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function

how do i stop the VB newline from adding a new type from the start of each concatenated record? - I've tried the trim function, clean function removes the line break.

any ideas?

 

[Yongle]

Please provide an example to illustrate
1. what the function is actually returning
2. what you want the function to return

 

[jasonb75]

There are a few ways you could do what you need to. If you have an up to date version of excel then the easiest way would be to use this array formula instead of vba

=TEXTJOIN(CHAR(10)&",",1,IF(CriteriaRange=Condition,ConcatenateRange,""))

Otherwise, this change to your code should fix it.

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler

If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        strResult = strResult & IIf(strResult <> "", vbNewLine, "") & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If strResult <> "" Then
    strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function