Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 33
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
counting occurrences dpwicz Excel Discussion (Misc queries) 2 October 2nd 08 06:25 PM
Unique occurrences of a value Q Sean Excel Worksheet Functions 7 December 30th 06 02:55 PM
sort 2 or more occurrences akullen Excel Worksheet Functions 2 April 10th 06 03:38 AM
# of occurrences gbeard Excel Worksheet Functions 1 May 5th 05 09:50 PM
Nested IF limit or Open parentheses limit Fred Excel Discussion (Misc queries) 5 December 23rd 04 04:34 PM


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"