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

Question

I am trying to have my macro check two criteria before running: if a fruit has been entered and if a count has been added for each fruit.

I want to make sure a quantity is entered for each fruit when a fruit is entered, but fruit will not be entered all the way down to row 94 (maybe only through row 15, for example). When I run the macro below, the message box appears beyond where any fruit is entered but I want it to stop when there is no fruit 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

You probably want one loop with Offset, instead of two loops. — BigBen
– BigBen, Commented Jun 29, 2021 at 14:03

Tim Williams · Accepted Answer · 2021-06-29 16:22:40Z

1

Only needs one loop as noted by BigBen.

You can read from ColD by offsetting from ColC

Sub Check_fruit()

    Dim cell As Range
    
    For Each cell In Sheets("Import").Range("C5:C94").Cells
        Select Case cell.Value
            Case "orange", "pear", "grape", "peach", "banana", "strawberry"
                If cell.Offset(0, 1) = 0 Then 'read next column over
                    MsgBox "You must specify how many fruit."
                    cell.Offset(0, 1).Select
                    Exit Sub
                End If
        End Select
    Next cell

End Sub

edited Jun 29, 2021 at 16:22

answered Jun 29, 2021 at 15:42

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related