mardi 29 juin 2021

VBA If/And formula with multiple criteria. Code is triggering when only meeting one of two criteria

I am trying to have my macro check certain criteria before running. In this example, I want to make sure a quantity is entered for each fruit. Sometimes fruit will not be entered all the way down to cell C94... it might stop at C15. When I run the macro the message box will appear beyond where any fruit is entered.

Sub Check_fruit()

Dim x As String
Dim y As Integer
Dim rSearch As Range
Dim rSearch1 As Range
Dim cell As Range, cell1 As Range
Dim matchRow As Integer

Set rSearch = Sheets("Import").Range("C05:C94")
Set rSearch1 = Sheets("Import").Range("D05:D94")

For Each cell In rSearch
    x = cell.value
    For Each cell1 In rSearch1
    y = cell1.value

         
                If y = 0 And (x = "apple" _
                Or x = "orange" _
                Or x = "pear" _
                Or x = "grape" _
                Or x = "peach" _
                Or x = "banana" _
                Or x = "strawberry") Then
                    MsgBox "You must specify how many fruit."
                    Exit Sub
                    End If
            Next cell1
        Next cell
    

End Sub

Aucun commentaire:

Enregistrer un commentaire