macsplit function
 

I would like to add the cell contents delimited by ","
v(i) = (individual program #'s) to row 27 so that we can fiind the
duplicates individually instead of seeing which cells has dupes, I could see
which program # is a dupe. I can't seem to be able to set the cell row
reference to see where I have the with and end with to break out the ?
thanks,

Sub CkforDupes()

Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim v
Dim rowcounter As Integer
Set rng = Range("D1", Range("D65536").End(xlUp))
rowcounter = 1

For Each cell In rng
v = MACSplit(cell.Text, ",")

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) & " :possible dups"


cell.Interior.ColorIndex = 3
End If
Debug.Print "v(i): ("; i; ")"; v(i)
Next
rowcounter = rowcounter + 1
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

macsplit function
 

please see next question, Excel 2003, split function thanks,

"Janis" wrote:

I would like to add the cell contents delimited by ","
v(i) = (individual program #'s) to row 27 so that we can fiind the
duplicates individually instead of seeing which cells has dupes, I could see
which program # is a dupe. I can't seem to be able to set the cell row
reference to see where I have the with and end with to break out the ?
thanks,

Sub CkforDupes()

Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim v
Dim rowcounter As Integer
Set rng = Range("D1", Range("D65536").End(xlUp))
rowcounter = 1

For Each cell In rng
v = MACSplit(cell.Text, ",")

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) & " :possible dups"


cell.Interior.ColorIndex = 3
End If
Debug.Print "v(i): ("; i; ")"; v(i)
Next
rowcounter = rowcounter + 1
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