Function CatIf(avbIf As Variant, _
rInp As Range, _
Optional sSep As String = ",", _
Optional bCatEmpty As Boolean = False) As String
' shg 2007
' UDF only
' Catenates the elements of rInp separated by sSep where the corresponding
' element of avbIf is True. Empty cells ignored unless bCatEmpty is True.
Dim iRow As Long
Dim iCol As Long
Dim i As Long
If TypeOf avbIf Is Range Then avbIf = avbIf.Value
If Not IsArray(avbIf) Then avbIf = Array(False, avbIf)
On Error Resume Next
i = UBound(avbIf, 2)
If Err.Number Then
' avbIf is 1D
For iRow = 1 To rInp.Rows.Count
For iCol = 1 To rInp.Columns.Count
i = i + 1
If avbIf(i) Then
If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
End If
End If
Next iCol
Next iRow
Else
' it's 2D
For iRow = 1 To rInp.Rows.Count
For iCol = 1 To rInp.Columns.Count
If avbIf(iRow, iCol) Then
If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
End If
End If
Next iCol
Next iRow
End If
If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function