Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif help | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |