Limit occurrences using VBA

Discussion in 'Excel Programming' started by jamasm2010@gmail.com, Apr 14, 2014.

  1. Guest

    Hi,
    I have a list of numbers in column A that are grouped together. What I needto do is count the number of each numeric occurrence and alert the user ifthe 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
     
    , Apr 14, 2014
    #1
    1. Advertisements

  2. isabelle Guest

    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
    >
     
    isabelle, Apr 14, 2014
    #2
    1. Advertisements

  3. Guest

    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!
     
    , Apr 14, 2014
    #3
  4. Guest

    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
     
    , Apr 15, 2014
    #4
  5. isabelle Guest

    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
    >
     
    isabelle, Apr 15, 2014
    #5
  6. Guest

    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
     
    , Apr 15, 2014
    #6
  7. GS Guest

    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
     
    GS, Apr 15, 2014
    #7
  8. isabelle Guest

    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!
    >
     
    isabelle, Apr 15, 2014
    #8
  9. GS Guest

    > 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
     
    GS, Apr 15, 2014
    #9
  10. Guest

    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
     
    , Apr 15, 2014
    #10
  11. GS Guest

    > 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
     
    GS, Apr 15, 2014
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Ben
    Replies:
    1
    Views:
    84
    Sharad
    Dec 9, 2004
  2. ShirleyTR
    Replies:
    1
    Views:
    89
    Bob Phillips
    Jul 22, 2005
  3. dkenebre

    Code occurrences between rows

    dkenebre, Aug 10, 2005, in forum: Excel Programming
    Replies:
    2
    Views:
    98
    dkenebre
    Aug 11, 2005
  4. vmegha
    Replies:
    0
    Views:
    95
    vmegha
    Jan 11, 2006
  5. vmegha
    Replies:
    1
    Views:
    89
    Bob Phillips
    Jan 11, 2006
  6. Replies:
    1
    Views:
    102
    Gazeta
    Nov 7, 2006
  7. Danny Boy

    Using VBA coding to count color Occurrences

    Danny Boy, Apr 22, 2010, in forum: Excel Programming
    Replies:
    4
    Views:
    150
    Danny Boy
    Apr 23, 2010
  8. Aaron
    Replies:
    7
    Views:
    315
    Don Guillett
    Mar 1, 2012
Loading...