检查范围内的所有值是否相同

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?

显示该消息,但显示9次(对于该范围中的每个单元格)。我需要检查E17:E25范围内的所有值是否为零,然后只显示一个消息框。有任何想法吗?

Thanks.

4 个解决方案

#1

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

你想知道所有的值是否都是0?你可以这样做

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

如果WorksheetFunction.Sum(Range(“E17:E25”))= 0那么MsgBox(“如果需要硬件,请手动填充相应的部分。”)

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

#2

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

#3

To test that:

测试:

The range doesn't contain any empty values

范围不包含任何空值

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

#4

-1

'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

#1