VBA多条件验证宏异常触发问题排查：单条件触发及超出有效数据范围弹窗修复

Let's break down and fix the two key problems in your macro, plus add some improvements for maintainability.

What Was Wrong in the Original Code? #

1. Nested loops causing false triggers: Your code uses two nested For Each loops—so for every cell in column C, it checks every cell in column D. That means if any D cell in the range is zero, it pops up the message immediately, even if that D cell isn't paired with a valid fruit.
2. No stop condition for empty C cells: The macro runs through all 90 rows (5-94) regardless of whether C cells are empty, which violates your requirement to stop validation when C has no input.

Corrected Macro Code #

Sub Check_fruit()
    Dim ws As Worksheet
    Dim fruitList As Variant
    Dim i As Integer
    Dim fruitVal As String
    Dim qtyVal As Variant
    
    ' Set a reference to the Import worksheet (cleaner than repeating Sheets("Import"))
    Set ws = ThisWorkbook.Sheets("Import")
    
    ' Define valid fruits in an array (easy to update later)
    fruitList = Array("apple", "orange", "pear", "grape", "peach", "banana", "strawberry")
    
    ' Loop through each row from 5 to 94
    For i = 5 To 94
        ' Get the fruit value, trimming any accidental leading/trailing spaces
        fruitVal = Trim(ws.Cells(i, "C").Value)
        
        ' Stop validation immediately if C cell is empty
        If fruitVal = "" Then
            Exit For
        End If
        
        ' Check if the fruit is in our valid list
        If IsInArray(fruitVal, fruitList) Then
            qtyVal = ws.Cells(i, "D").Value
            
            ' Validate quantity: must be a positive number (not empty, zero, or non-numeric)
            If IsEmpty(qtyVal) Or qtyVal = 0 Or Not IsNumeric(qtyVal) Then
                MsgBox "You must specify a valid quantity for the fruit in row " & i & ".", vbExclamation
                ws.Cells(i, "D").Activate ' Optional: Jump directly to the problematic cell
                Exit Sub ' Stop the macro until the issue is fixed
            End If
        End If
    Next i
    
    ' If we get here, all valid fruit entries have correct quantities
    MsgBox "All fruit quantity checks passed!", vbInformation
End Sub

' Helper function to check if a value exists in an array
Function IsInArray(valToFind As String, arr As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        If element = valToFind Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

Key Fixes & Improvements #

• Single row loop: We now check column C and D in the same row, so we only validate the quantity that corresponds to each fruit entry.
• Stop on empty C cell: As soon as we hit an empty cell in column C, we exit the loop to stop validation.
• Array for valid fruits: Replaced the long chain of Or conditions with an array, making it much easier to add/remove fruits later.
• Trimmed input: Handles accidental spaces in fruit names (e.g., " apple " will still be recognized as "apple").
• Better quantity validation: Checks for empty cells, zero, and non-numeric values (since quantities should be positive numbers).
• Optional cell activation: Jumps to the problematic D cell to help the user quickly fix the issue.
• Success message: Confirms when all checks pass, so the user knows everything is correct.

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