The following macro was working with a split function written for Excel 2003
yesterday. This morning it doesn't compile and stops on the first variable
definition.

I don't know what happened except I noticed when I saved it yesterday there
was a second module called version(1). I guess somehow version one of the
module was different than the original. I proceeded to use the 2nd version
and keep the first copy as a backup allthough I don't know how I created the
2nd. This morning version 1 module is gone.

How do I get rid of the compile error since I know this macro works the
problem must be somewhere else in the module? I tried it with option eplicit
and without. It is very weird because I know this macro works. By the way
how do I save my Personal Workbook?

thanks,


Sub ckForDupes()

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

Set Rng = Range("B1", Range("B65536").End(xlUp))


For Each cell In Rng
v = MACSplit(cell.Text, ",")
For i = LBound(v) To UBound(v)
c1 = Application.CountIf(Rng1, "*" & 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

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

Better don't use 'cell' as a variable name since it is a reserved keyword in
VBA.



"Janis" schreef in bericht
...
The following macro was working with a split function written for Excel
2003
yesterday. This morning it doesn't compile and stops on the first
variable
definition.

I don't know what happened except I noticed when I saved it yesterday
there
was a second module called version(1). I guess somehow version one of the
module was different than the original. I proceeded to use the 2nd
version
and keep the first copy as a backup allthough I don't know how I created
the
2nd. This morning version 1 module is gone.

How do I get rid of the compile error since I know this macro works the
problem must be somewhere else in the module? I tried it with option
eplicit
and without. It is very weird because I know this macro works. By the
way
how do I save my Personal Workbook?

thanks,


Sub ckForDupes()

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

Set Rng = Range("B1", Range("B65536").End(xlUp))


For Each cell In Rng
v = MACSplit(cell.Text, ",")
For i = LBound(v) To UBound(v)
c1 = Application.CountIf(Rng1, "*" & 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

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

"cell" is not a reserved word, but the plural "Cells" is.

NickHK

"moon" wrote in message
. ..
Better don't use 'cell' as a variable name since it is a reserved keyword
in
VBA.



"Janis" schreef in bericht
...
The following macro was working with a split function written for Excel
2003
yesterday. This morning it doesn't compile and stops on the first
variable
definition.

I don't know what happened except I noticed when I saved it yesterday
there
was a second module called version(1). I guess somehow version one of
the
module was different than the original. I proceeded to use the 2nd
version
and keep the first copy as a backup allthough I don't know how I created
the
2nd. This morning version 1 module is gone.

How do I get rid of the compile error since I know this macro works the
problem must be somewhere else in the module? I tried it with option
eplicit
and without. It is very weird because I know this macro works. By the
way
how do I save my Personal Workbook?

thanks,


Sub ckForDupes()

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

Set Rng = Range("B1", Range("B65536").End(xlUp))


For Each cell In Rng
v = MACSplit(cell.Text, ",")
For i = LBound(v) To UBound(v)
c1 = Application.CountIf(Rng1, "*" & 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

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