CODEWITHSUNDEEP
vbaexcelloopsexit-code
Mike Mann
Mike Mann

Reputation: 546

Loop stops after one condition is met even if there are multiple

The code below will "exit for" after it meets its condition once even if there are more in the range that meet the condition. How do I correct this?

Private Sub cmdAdd_Click()
    On Error GoTo Whoa
    Dim LastRow As Long, i As Long
    LastRow = ActiveSheet.Range(Me.txtTaskCol.Value & Rows.Count).End(xlUp).Row
    'Copy input values to sheet
    For i = 1 To LastRow
        If UCase(CStr(ActiveSheet.Range(Me.txtTaskCol.Value & i).Value)) = UCase(CStr(Me.txtTask.Value)) Then
            ActiveSheet.Range(Me.txtUnitCol.Value & i).Value = Me.txtQuantity.Value
            Exit For
        Else
            If i = LastRow Then MsgBox "Task Not Found!"
        End If
    Next i
    'Clear input controls
    Me.txtTask.Value = ""
    Me.txtQuantity.Value = ""
    Exit Sub

Whoa:
        Select Case Err.Number
            Case 1004
                MsgBox "Check for Valid Column Letters!"
        End Select

End Sub

Upvotes: 0

Views: 659

Answers (2)

JJB
JJB

Reputation: 53

Echoing K.Davis comment, not sure why you would want to exit?

I made some edits to your code. Error handling is not included, but the code should complete the loop and tell you if nothing is found.

Private Sub cmdAdd_Click()
    On Error GoTo Whoa
    Dim LastRow As Long, i As Long
    LastRow = ActiveSheet.Range(Me.txtTaskCol.Value & Rows.Count).End(xlUp).Row

'Set a counter so you can message Task Not Found
    Dim matchCounter as Integer
    matchCounter = 0

'Copy input values to sheet
    For i = 1 To LastRow
        If UCase(CStr(ActiveSheet.Range(Me.txtTaskCol.Value & i).Value)) = _
        UCase(CStr(Me.txtTask.Value)) Then
            ActiveSheet.Range(Me.txtUnitCol.Value & i).Value = Me.txtQuantity.Value
            matchCounter = matchCounter + 1
        End If
    Next i

If matchCounter = 0 then MsgBox "Nothing Found"

'Clear input controls
    Me.txtTask.Value = ""
    Me.txtQuantity.Value = ""
    Exit Sub

End Sub

Hope it's helpful.

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Just use a boolean flag that will set the flag to True if your If...Then statement is true one time:

If you are wanting to continue your For...Next statement after you meet the criteria, then you don't want to exit for.

Private Sub cmdAdd_Click()
    On Error GoTo Whoa
    Dim LastRow As Long, i As Long, tskFlg As Boolean
    LastRow = ActiveSheet.Range(Me.txtTaskCol.Value & Rows.Count).End(xlUp).Row
    'Copy input values to sheet
    For i = 1 To LastRow
        If UCase(CStr(ActiveSheet.Range(Me.txtTaskCol.Value & i).Value)) = UCase(CStr(Me.txtTask.Value)) Then
            ActiveSheet.Range(Me.txtUnitCol.Value & i).Value = Me.txtQuantity.Value
            tskFlg = True
        End If
    Next i
    If tskFlg = False Then MsgBox "Task Not Found!"
    'Clear input controls
    Me.txtTask.Value = ""
    Me.txtQuantity.Value = ""
    Exit Sub

Whoa:
        Select Case Err.Number
            Case 1004
                MsgBox "Check for Valid Column Letters!"
        End Select

End Sub

Upvotes: 0

Related Questions