Doh! Missed that bit

Option Explicit

Function CountList(rng As Range, Optional delimiter As String = "+")
Dim arg
If rng.Count 1 Then
CountList = CVErr(xlErrRef)
Else
arg = Split(rng.Formula, delimiter)
CountList = UBound(arg) - LBound(arg) + 1
End If
End Function

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If delimiter = vbNullChar Then
delimiter = Chr(7)
Text = Replace(Text, vbNullChar, delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, delimiter, """,""") &
"""}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function


'-----------------------------------------------------------------
Function Replace(expression As String, _
find_string As String, _
replacement As String)
'-----------------------------------------------------------------
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String


sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replace = sTemp

End Function
#End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Max" wrote in message
...
Thanks, Bob but urrgh ... got hit again, same error,
but this time at this line in "Function Split":

Text = Replace(Text, vbNullChar, Delimiter)
"Replace" was highlighted

Am I ever going to see the "light" <g?
Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
That's because you are still in the dark ages Max, still using XL97
<vbg.

Try this version

Function CountList(rng As Range, Optional Delimiter As String = "+")
Dim arg
If rng.Count 1 Then
CountList = CVErr(xlErrRef)
Else
arg = Split(rng.Formula, Delimiter)
CountList = UBound(arg) - LBound(arg) + 1
End If
End Function

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""")
&
"""}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function
#End If