macro doesn't compile after module crashed continued from scary th

[Janis]

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) & " ossible 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

 

[stevebriz]

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?

 

[Janis]

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]

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

 

[Janis]

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,

 

[Janis]

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,

 

[Janis]

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

 

[Janis]

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.

 

[stevebriz]

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

the try again.