Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
Hi,
I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to: If Application.Countif(ColumnA:A, rowname) 2 then MsgBox "Problem on line" & rowname & "." End if Thanks, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
hi,
Private Sub Worksheet_Change(ByVal Target As Range) If Application.CountIf(Range("A:A"), Target.Value) 2 Then MsgBox "Problem on line " & Target.Row & "." End If End Sub isabelle Le 2014-04-14 16:01, a écrit : Hi, I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to: If Application.Countif(ColumnA:A, rowname) 2 then MsgBox "Problem on line" & rowname & "." End if Thanks, James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
On Monday, April 14, 2014 3:01:03 PM UTC-5, wrote:
Hi, I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to: If Application.Countif(ColumnA:A, rowname) 2 then MsgBox "Problem on line" & rowname & "." End if Thanks, James Thanks, Isabelle. It worked like a charm! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
On Monday, April 14, 2014 3:01:03 PM UTC-5, wrote:
Hi, I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to: If Application.Countif(ColumnA:A, rowname) 2 then MsgBox "Problem on line" & rowname & "." End if Thanks, James I've been told that using a change event is not the preferred method of doing this. Is there a way to run it another way, such as by a command button? Thanks, James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
it is possible but it involves doing a click on the button to confirm each entry,
but you could also limited the event to a range of cells order to avoid a recursive method you can add: Application.EnableEvents = False resets to "True" after Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Set isect = Application.Intersect(Range("A1:A1000"), Target) If Not isect Is Nothing Then If Application.CountIf(Range("A:A"), Target.Value) 2 Then MsgBox "Problem on line " & Target.Row & "." End If End If Application.EnableEvents = True End Sub isabelle Le 2014-04-14 19:20, a écrit : I've been told that using a change event is not the preferred method of doing this. Is there a way to run it another way, such as by a command button? Thanks, James |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
On Monday, April 14, 2014 3:01:03 PM UTC-5, wrote:
Hi, I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to: If Application.Countif(ColumnA:A, rowname) 2 then MsgBox "Problem on line" & rowname & "." End if Thanks, James Thank you, Isabelle. I really appreciate your help. I'm not sure that will make everyone happy, but it really close. I may have to use a loop and counter, and run it from a button. James |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
Another way..., use Conditional formatting to 'flag' offending cells so
no VBA is required! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
Garry, this is a brilliant idea,
isabelle Le 2014-04-14 20:49, GS a écrit : Another way..., use Conditional formatting to 'flag' offending cells so no VBA is required! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
Garry, this is a brilliant idea,
Not sure about that assessment, but it's how I often hint erroneous input OR next required input for progressive data entry. The latter shows where the next input is required after current input. isabelle Le 2014-04-14 20:49, GS a écrit : Nice to see you back here! I've missed your contributions... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
On Monday, April 14, 2014 7:49:56 PM UTC-5, GS wrote:
Another way..., use Conditional formatting to 'flag' offending cells so no VBA is required! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Thanks, Garry. I do believe that will work. James |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limit occurrences using VBA
On Monday, April 14, 2014 7:49:56 PM UTC-5, GS wrote:
Another way..., use Conditional formatting to 'flag' offending cells so no VBA is required! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Thanks, Garry. I do believe that will work. James You're welcome! I appreciate the feedback... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting occurrences | Excel Discussion (Misc queries) | |||
Unique occurrences of a value Q | Excel Worksheet Functions | |||
sort 2 or more occurrences | Excel Worksheet Functions | |||
# of occurrences | Excel Worksheet Functions | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) |