Sorry but I tried this macro in another module and it still doesn't compile.
I think something that was saved before got changed due to a file that wasn't
recovered properly. So can someone find out what little bitty thing is
missing? I know it works. I remember I had to change one thing to get it to
work and I don't know if that is the old version now.

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

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

I added this code to a module and it compiled find.

what line does the compile error come up on? and does it give a
message?

I just figured out what I think happened. Yesterday I created a user form.
I deleted the click word and a macro on the user form was exactly the same as
in my project. At least that is what I think happened. On that form if you
click the buttons there is no code window only a blank on all the buttons.
Obviously corrupted. Ideleted the form. It still doesn't compile. It says
invalid procedure call. It stops on rng as RAnge, the second line.

thanks,

"stevebriz" wrote:

I added this code to a module and it compiled find.

what line does the compile error come up on? and does it give a
message?

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?

I did declare it elsewhere. Why does it affect another macro? It isn't a
global variable meaning I didn't define it in the declarations.
thanks,

"stevebriz" wrote:

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?

Steve:
help it still doesn't work. I searched for all the "rng" variables in the
module and changed all those variables in other macros in that modules to
something different. Do I have to do it for all the macros in all the
modules in the project? Can you tell me why they don't hold for only one
macro?
thanks,

"stevebriz" wrote:

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?

This is vary scary. The userform I just deleted reappeared. And what is
more the new replacement userform is now gone?

"stevebriz" wrote:

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?

Excel is also giving me the error message that auto-recover has been
disabled. I think something is wrong with this module in my personal
workbook and I surely need to know how to fix it.

"Janis" wrote:

I did declare it elsewhere. Why does it affect another macro? It isn't a
global variable meaning I didn't define it in the declarations.
thanks,

"stevebriz" wrote:

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?

It Think at this point I would save my workbook and then do a detect
and repair on office

the try again.


Janis wrote:
Excel is also giving me the error message that auto-recover has been
disabled. I think something is wrong with this module in my personal
workbook and I surely need to know how to fix it.

"Janis" wrote:

I did declare it elsewhere. Why does it affect another macro? It isn't a
global variable meaning I didn't define it in the declarations.
thanks,

"stevebriz" wrote:

do a Search for all the project and see if you have declared Rng
elsewhere that could affect it?