>>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message
| news:(E-Mail Removed)...
| > 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" <(E-Mail Removed)> wrote in message
| > news:e2a1$(E-Mail Removed)...
| >> 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
| >>
| >
| >
|
|
|
Similar Threads
