CountIf in VBA
 

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

CountIf in VBA
 

On 26 Maj, 23:43, "Otto Moehrbach"
wrote:
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

CountIf in VBA
 

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

CountIf in VBA
 

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

CountIf in VBA
 

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