Hi Harald

didn't think of testing it with nothing in it either :)

so final(?!?!) function

Public Function CONCAT_IF(ConcCheck As Range, _
ConcCrit As Variant, _
Optional ConcRange As Range, _
Optional DelimitWith As String) As String
'written by JulieD and Harald Staff
'ConcCheck - range to check for the criteria
'ConcRange - range to concatenation
'ConcCrit - the criteria
'DelimitWith - the delimination character(s)

Dim Cel As Range
Dim i As Long, j As Long
Dim checkarray() As String
Dim rangearray() As String

If ConcRange Is Nothing Then Set ConcRange = ConcCheck

i = ConcCheck.Count
j = ConcRange.Count

If i < j Then
Exit Function
End If

ReDim checkarray(i - 1)
ReDim rangearray(i - 1)

i = 0
For Each Cel In ConcCheck
checkarray(i) = Cel.Text
i = i + 1
Next
i = 0
For Each Cel In ConcRange
rangearray(i) = Cel.Text
i = i + 1
Next

For i = 0 To j - 1
If checkarray(i) = ConcCrit Then CONCAT_IF = _
CONCAT_IF & rangearray(i) & DelimitWith
Next
If CONCAT_IF < "" Then _
CONCAT_IF = Left$(CONCAT_IF, _
Len(CONCAT_IF) - Len(DelimitWith))
End Function

-------
now all i need to do is update my website and put it on there - unless you
have a better home for it?

Cheers
JulieD


"Harald Staff" wrote in message
...
Hi again Julie

What I never remember to set up in test scenarios is "nothing". Some day
I'll learn. This is not good:
Application.WorksheetFunction.CountA(ConcCheck)
-clear a cell and the function returns nothing. Let us instead do

i = ConcCheck.Count
j = ConcRange.Count

Best wishes Harald

---snip---