There is a compile error on the split line where I need to loop through the
product numbers in each B cell. I got this macro from someone on this list
and I do not understand the for/if statement?

What I am trying to do is check all product numbers ( commas as delimiters).
I need to be absolutely sure there are no duplicate product numbers in any
of the other cells. The macro must check ea product number against all other
product numbers in all of the other cells rows one by one. That is why there
are two loops.

Private Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = R1C2.Count.End(xlUp)
Set rng = Range("B1:RowCount")
For Each cell In rng
v = Split(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
Next
Next
End Sub
Thanks for helping me with the split

Did you said you are on a MAC. The MAC uses VBA 5 and Split was introduced
in VBA 6. You also have some other odd stuff. Try this:

Public Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("B1:B" & rowCount)
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 dup"
cell.Interior.ColorIndex = 3
Exit For
End If
Next
Next
End Sub




Public 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
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



--
Regards,
Tom Ogilvy


"Janis" wrote in message
...
There is a compile error on the split line where I need to loop through
the
product numbers in each B cell. I got this macro from someone on this
list
and I do not understand the for/if statement?

What I am trying to do is check all product numbers ( commas as
delimiters).
I need to be absolutely sure there are no duplicate product numbers in any
of the other cells. The macro must check ea product number against all
other
product numbers in all of the other cells rows one by one. That is why
there
are two loops.

Private Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = R1C2.Count.End(xlUp)
Set rng = Range("B1:RowCount")
For Each cell In rng
v = Split(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
Next
Next
End Sub
Thanks for helping me with the split

Hi Tom:
I am on a Mac and thanks for pointing that out. It would not compile on the
vSplit. Wow it is so much more complicated on a Mac I'm thinking about
switching to a PC. tnx,
Janis

"Tom Ogilvy" wrote:

Did you said you are on a MAC. The MAC uses VBA 5 and Split was introduced
in VBA 6. You also have some other odd stuff. Try this:

Public Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("B1:B" & rowCount)
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 dup"
cell.Interior.ColorIndex = 3
Exit For
End If
Next
Next
End Sub




Public 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
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



--
Regards,
Tom Ogilvy


"Janis" wrote in message
...
There is a compile error on the split line where I need to loop through
the
product numbers in each B cell. I got this macro from someone on this
list
and I do not understand the for/if statement?

What I am trying to do is check all product numbers ( commas as
delimiters).
I need to be absolutely sure there are no duplicate product numbers in any
of the other cells. The macro must check ea product number against all
other
product numbers in all of the other cells rows one by one. That is why
there
are two loops.

Private Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = R1C2.Count.End(xlUp)
Set rng = Range("B1:RowCount")
For Each cell In rng
v = Split(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
Next
Next
End Sub
Thanks for helping me with the split

I will try it. This project involves some other apps as well as applescript.
it would be good to have it all on the same platform however I haven't
figured out your function yet.

"Tom Ogilvy" wrote:

Did you said you are on a MAC. The MAC uses VBA 5 and Split was introduced
in VBA 6. You also have some other odd stuff. Try this:

Public Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("B1:B" & rowCount)
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 dup"
cell.Interior.ColorIndex = 3
Exit For
End If
Next
Next
End Sub




Public 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
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



--
Regards,
Tom Ogilvy


"Janis" wrote in message
...
There is a compile error on the split line where I need to loop through
the
product numbers in each B cell. I got this macro from someone on this
list
and I do not understand the for/if statement?

What I am trying to do is check all product numbers ( commas as
delimiters).
I need to be absolutely sure there are no duplicate product numbers in any
of the other cells. The macro must check ea product number against all
other
product numbers in all of the other cells rows one by one. That is why
there
are two loops.

Private Sub deleteDups()
Dim rng As Range, cell As Range
Dim i As Long, c1 As Long
Dim rowCount As Long
rowCount = R1C2.Count.End(xlUp)
Set rng = Range("B1:RowCount")
For Each cell In rng
v = Split(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
Next
Next
End Sub
Thanks for helping me with the split