split function for Excel 2003

[Janis]

I got this split function which all works well. Thanks. I need write the
v(nth) iterations to another column so I can see the individual dupes it that
is possible. Right now it very nicely colors the whole cell where a dupe is
found but I would love to have the individual values stored somewhere instead
of flashing by in the message box. Thanks for any ideas,

Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim v

Set rng = Range("D1", Range("D65536").End(xlUp))


For Each cell In rng
v = MACSplit(cell.Text, ",")
Debug.Print "v:"; v
For i = LBound(v) To UBound(v)
c1 = Application.CountIf(rng, "*" & v(i) & "*")
If c1 > 1 Then ' it should be 1 to match itself
MsgBox v(i) & " ossible dups"

Debug.Print "v(i): ("; i; ")"; v(i)
'c1.Font.ColorIndex = 50
End If
Next
Next

End Sub
Function MACSplit(s As String, s3 As String)
Dim v As Variant, sChr As String
Dim S1 As String, s2 As String
Dim cnt As Long
Dim i
ReDim v(0 To 0)
S1 = Trim(s)
s2 = ""
If InStr(1, S1, s3, vbTextCompare) = 0 Then
v(0) = S1
MACSplit = v
Exit Function
End If
cnt = -1
For i = 1 To Len(S1)
sChr = Mid(S1, i, 1)
If sChr = s3 Then
cnt = cnt + 1
ReDim Preserve v(0 To cnt)
v(UBound(v)) = s2
s2 = ""

Else
s2 = s2 & sChr
End If
Next
If s2 <> "" And s2 <> s3 Then
cnt = cnt + 1
ReDim Preserve v(0 To cnt)
v(UBound(v)) = s2
End If
MACSplit = v
End Function

 

[Tom Ogilvy]

Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim v, rw as Long

Set rng = Range("D1", Range("D65536").End(xlUp))


For Each cell In rng
v = MACSplit(cell.Text, ",")
'Debug.Print "v:"; v
For i = LBound(v) To UBound(v)
c1 = Application.CountIf(rng, "*" & v(i) & "*")
If c1 > 1 Then ' it should be 1 to match itself
MsgBox v(i) & " ossible dups"
cells(rw,"F").Value = v(i)
rw = rw + 1
' Debug.Print "v(i): ("; i; ")"; v(i)
'c1.Font.ColorIndex = 50
End If
Next
Next

End Sub

 

[Janis]

tom, you are the best!
I thank you. I have to run this all tomorrow and it isn't a test and it is
mission critical.
I just want to write down the output somewhere to check it by hand the first
time to make sure there are absolutely no dupes and I spent all day trying to
understand this routine. I basically understand it but it is too complicated
to modify.
Thanks again.