CountIf in VBA

[Otto Moehrbach]

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

 

[Per Jessen]

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

Hi Otto

As it's a worksheetfunction you are trying to use in VBA, it should
look like this:

If Application.WorksheetFunction.CountIf(TheRng, Target.Value)) > 1
then..

Regards,
Per

 

[Jim Rech]

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

 

[Otto Moehrbach]

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]

I just changed my range to a contiguous range and it worked, so thatmy 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
| 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
| | > 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
| > | >> 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
| >>
| >
| >
|
|