CODEWITHSUNDEEP
vbaexcel
PFost
PFost

Reputation: 61

Check that all values in a range are identical

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.

Upvotes: 3

Views: 4738

Answers (4)

brettdj
brettdj

Reputation: 55682

To test that:

  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

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 3

Captain Grumpy
Captain Grumpy

Reputation: 520

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.")

Upvotes: 2

dgorti
dgorti

Reputation: 1240

'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

Upvotes: -1

Related Questions