檢查整行是否具有值 0 (Check if an entire row has value 0)

問題描述

檢查整行是否具有值 0 (Check if an entire row has value 0)

我有一個 Excel 工作表“Sheet1”，其中 A1 中的周數。我在 A 列中有另一張帶有周數的工作表“Sheet2”。B 到 Z 列包含與週數對應的數字。

現在我想用 VBA 檢查整行，B 到 Z 列是否在“ Sheet2" 包含值 0（零）

我該怎麼做？

我有這個代碼來選擇對應於 Sheet 1 上的 weeknumber 的特定行：

Dim WeekNr As Long
WeekNr = Sheets("Sheet1").Range("A1")
Sheets("Sheet2").Cells(WeekNr + 2, 2)

但我不知道如何檢查每個單元格是否包含從 B 到 Z 的零。

參考解法

方法 1:

You have two different queries so I am confused. Addressing both...

i want to check with VBA if the entire row, column B to Z on "Sheet2" contains the value 0 (zero)

Non VBA Solution

=COUNTIF(B1:Z1,0)>0

VBA Solution

Dim ws As Worksheet
Set ws = Sheets("Sheet2")

If Application.WorksheetFunction.CountIf(ws.Range("B1:Z1"), 0) > 0 Then
    MsgBox "Found"
Else
    MsgBox "not Found"
End If

But i don't know how to check if every cell contains a zero from B to Z.

Non VBA Solution

=IF(SUM(B1:Z1)>0,"Not Every Cell has 0","Cell has 0 or blank values")

VBA Solution

Dim ws As Worksheet
Set ws = Sheets("Sheet2")

If Application.WorksheetFunction.Sum(ws.Range("B1:Z1")) > 0 Then
    MsgBox "Not Found"
Else
    MsgBox "Found"
End If

NOTE: If you only want to check for 0 and not for blank cells then you can use this as well

Non VBA Solution

=COUNTIF(B1:Z1,0)=25

VBA Solution

Dim ws As Worksheet
Set ws = Sheets("Sheet2")

If Application.WorksheetFunction.CountIf(ws.Range("B1:Z1"), 0) = 25 Then
    MsgBox "All cells have 0"
Else
    MsgBox "All cells do not have 0"
End If

方法 2:

Try this to check if value is 0 or not (it will get values from active sheet, you can use any sheet with sheet name according to your requirement)

Sub checkzeros()
    Dim weekRange As Range
    Set weekRange = Range("B1:Z1")
    For Each cell In weekRange
        Debug.Print cell.Value
        If cell.Value = 0 Then
            ' do what ever yu want if value is 0
        Else
            ' do what ever yu want if value is not 0
        End If
    Next cell
End Sub

(by Rick A.、Siddharth Rout、Bhanu Pratap)

參考文件

1. Check if an entire row has value 0 (CC BY‑SA 2.5/3.0/4.0)