I just changed my range to a contiguous range and it worked, so that
answers
my question.

Yeah, Countif accepts only one area. "Type mismatch" isn't very helpful but
I guess VB doesn't really know why the function failed.

--
Jim
"Otto Moehrbach" wrote in message
...
| Jim
| Thanks for that. That appears to be just like what I have. The only
| difference that I see is that my range is non-contiguous. My range is:
| Set TheRng = Range("D1,F1,H1,J1,L1,N1")
| Would that cause the "Type Mismatch" error?
| If so I could just loop through the range and count the number of
| Target.Value
| I just changed my range to a contiguous range and it worked, so that
answers
| my question.
| Thanks for your time. Otto
| "Jim Rech" wrote in message
| ...
| This variation on what you're doing works okay for me:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| Dim TheRng As Range
| Set TheRng = Range("A1:A10")
| With Application
| If .CountIf(TheRng, Target.Value) 1 Then
| .EnableEvents = False
| .Undo
| .EnableEvents = True
| End If
| End With
| End Sub
|
|
| --
| Jim
| "Otto Moehrbach" wrote in message
| ...
| Excel XP & Win XP
| I have a range, TheRng, of cells all formatted as text. The user
enters
| numbers into those cells. I want to trap the instance of him entering
a
| number that has already been entered somewhere else in TheRng. I am
| trying to use the CountIf worksheet function in a Worksheet_Change
macro
| as follows:
| If Application.CountIf(TheRng, Target.Value) 1 Then....
| Or
| If Application.CountIf(TheRng, CStr(Target.Value)) 1 Then....
| Or
| If Application.CountIf(TheRng.Address, CStr(Target.Value)) 1 Then....
| Each try has produced a "Type Mismatch" error.
| How can I get this to work? Thanks for your time. Otto
|
|
|
|
|