Announcement

Collapse
No announcement yet.

Message if exists

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Message if exists



    Hi All,

    On my worksheet, Range A2:A50 has data then a group number A, B, or C in column B
    using vba, how can I lookup the range A2:A50 to ensure A is shown a minimum of 10 times, and either B or C is shown a minmum of once.

    A message box to say "Selections Complete" if the criteria is met, or "Selections do not meeet requirements" if not.

    Thanks

  • #2
    Hi jl2509,

    Try this while on the sheet in question:

    Code:
    Option Explicit
    Sub Macro1()
    
        Dim blnCriteriaMet As Boolean
           
        blnCriteriaMet = True
       
        If Evaluate("COUNTIF($B$2:$B$50,""A"")") <= 9 Then
            blnCriteriaMet = False
        ElseIf Evaluate("COUNTIF($B$2:$B$50,""B"")") + Evaluate("COUNTIF($B$2:$B$50,""C"")") = 0 Then
            blnCriteriaMet = False
        End If
       
        If blnCriteriaMet = False Then
            MsgBox "Selections do not meeet requirements.", vbExclamation
        Else
            MsgBox "Selections Complete.", vbInformation
        End If
    
    End Sub
    Regards,

    Robert

    Comment


    • #3
      Many thanks Robert

      Great work

      Comment


      • #4
        Hi Robert

        Just one thing, is there a way to say what is not acheived in the message box

        i.e

        "Group A requirements have not been met, selections must be 10 or more"

        or

        "Group B or Broup C requirements have not been met, selections from either one of these Groups must be more than 1"

        Thanks

        Comment


        • #5
          Hello,

          What about the third case ... where ALL Group A, Group B and Group C do not comply with your rules ...?
          If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

          Comment


          • #6
            Hi Carim

            I figured it out and thats exactly what I added.
            A bit of stumbling but got here.

            Nice to have the follow up from you guys though.

            Thanks

            Comment


            • #7


              Hello,

              Glad you could fix your problem ...
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment

              Working...
              X