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

  3. Guest

    Thanks, Isabelle. It worked like a charm!
     
    , Apr 14, 2014
    #3
  4. Guest

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

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

    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

    Thanks, Garry. I do believe that will work.
    James
     
    , Apr 15, 2014
    #10
  11. GS Guest

    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

Ask a Question

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.