VBA Programming - How to Display a Message Box when all Values in a Range are the Same

Question

I need to display a message box when all the values in a range on my spreadsheet are zero. Currently I am using the following code:

Dim Cell As Range
For Each Cell In Range("E17:E25")
    If Cell.Value = "0" Then
    MsgBox ("If hardware is required, please  manually populate the corresponding sections.")
    End If
Next

The message is displayed, however it is shown 9 times (for each of the cells in the range). What I need is to check if all the values in the range E17:E25 are zero, and then display only one message box. Any ideas?

Thanks.


Show source
| excel-vba   | vba   | excel   2017-01-05 21:01 4 Answers

Answers to VBA Programming - How to Display a Message Box when all Values in a Range are the Same ( 4 )

  1. 2017-01-05 21:01
    'something like this
     Dim isDataPresent as boolean
     isDataPresent = true
     for each Cell in Range(....)
       if cell.value = "0" then 
           isDataPresent = false
           exit for
       end if
     next
     if not isDataPresent then 
       show message box here
     end if 
    
  2. 2017-01-05 21:01

    You want to know if all the values are 0? You could just do

    If WorksheetFunction.Sum(Range("E17:E25")) = 0 Then MsgBox ("If hardware is required, please manually populate the corresponding sections.")

    No need for loops.

    Edit: If you want to check for any other number, and if all cells are that number, you can do this:

    Sub t()
    Dim rng As Range
    Dim myNum as Long
    myNum = 1
    Set rng = Range("B3:B6")
    If WorksheetFunction.CountIf(rng, myNum) = rng.Count Then MsgBox ("All the same!")
    End Sub
    
  3. 2017-01-05 21:01

    And cause there are infinite ways to skin a cat here is another approach.

    Dim Cell As Range
    Dim ZeroCount As Integer
    Dim CellCount As Integer
    
    ZeroCount = 0
    CellCount = 0
    
    For Each Cell In Range("E17:E25")
        CellCount = CellCount + 1
        If Cell.Value = 0 Then ZeroCount = ZeroCount + 1
    Next Cell
    
    If ZeroCount = CellCount Then MsgBox ("If hardware is required, please  manually populate the corresponding sections.")
    
  4. 2017-01-06 04:01

    To test thate

    1. The range doesn't contain any empty values
    2. All cells are the same

    function

    Function SameRange(rngIn As Range) As Boolean
    If Application.CountA(rngIn) = rngIn.Cells.Count Then SameRange = (Application.CountIf(rngIn, rngIn.Cells(1).Value) = rngIn.Cells.Count)
    End Function
    

    test

    Sub test()
    MsgBox SameRange([d1:d5])
    End Sub
    

Leave a reply to - VBA Programming - How to Display a Message Box when all Values in a Range are the Same

◀ Go back