Excel VBA技术求助：G列指定范围单元格非0时MsgBox无法触发

Hey there! I see the problem with your current code—let's get it sorted so it properly checks the entire G20:G100 range.

Why Your Current Code Fails #

When you use Sheets("Sheet1").Range("G20:G100").Value <> 0, VBA treats the range's value as a 2D array (since it covers multiple cells). Comparing an array directly to a single value like 0 only checks the first cell in the range (G20), which is why it only works for that one cell.

Solution 1: Loop Through Each Cell (Simple & Clear) #

If you want to check every cell in the range and trigger the message when any non-zero value is found, this straightforward loop will do the trick:

Private Sub Worksheet_Calculate()
    Dim cell As Range
    
    ' Loop through every cell in the target range
    For Each cell In Me.Range("G20:G100")
        ' Skip empty cells if needed (remove this line if empty cells should count as non-zero)
        If cell.Value <> "" And cell.Value <> 0 Then
            MsgBox "Not equal to 0", vbOKOnly
            Exit Sub ' Stop checking after the first non-zero (remove this if you want a message for each non-zero)
        End If
    Next cell
End Sub

Note: I used Me instead of Sheets("Sheet1") because this code lives in the worksheet's module—Me refers to the current sheet, making it more flexible if you ever rename the sheet.

Solution 2: Use CountIf (Faster for Large Ranges) #

If you just need to know if any cell in the range is non-zero (without checking each one individually), the CountIf worksheet function is much more efficient:

Private Sub Worksheet_Calculate()
    Dim nonZeroCells As Long
    
    ' Count how many cells in the range are not equal to 0
    nonZeroCells = WorksheetFunction.CountIf(Me.Range("G20:G100"), "<>0")
    
    ' If there's at least one non-zero cell, show the message
    If nonZeroCells > 0 Then
        MsgBox "Not equal to 0", vbOKOnly
    End If
End Sub

This method avoids looping entirely, which is better if your range is large or you want faster execution.

Quick Tip #

If you want to exclude empty cells from the check (since an empty cell's value is technically 0 in some contexts), adjust the CountIf criteria to "<>" & 0 & "<>"""—though the default "<>"0 will treat empty cells as 0, matching your original intent.

内容的提问来源于stack exchange，提问作者ADRSAL