Excel VBA Exit For does not work inside If block

Question

Dim valCheck As Variant

For i = 0 To row1
    For j = 0 To col1
        MsgBox ("started" & i)
        valCheck = Range("A1").Offset(i, j).Value
        MsgBox (valCheck)
        If valCheck = "Details" Then
            MsgBox ("found")
            ActiveCell = Range("A1").Offset(i + 1, j)
            Exit For
            Exit For
        End If
    Next j
Next i

You can't use 2 x Exit For like this, it will only use the first one, and exit the For j loop. what are you trying to achieve ? — Shai Rado
– Shai Rado, Commented Mar 31, 2018 at 6:02
I want to loop through a row1 X col1 matrix until i find desired word="Details" and select the column in which it is present — Spd
– Spd, Commented Mar 31, 2018 at 6:05

Shai Rado · Accepted Answer · 2018-03-31 06:08:24Z

3

You don't need to have 2 For loops and Exit For, you can simply use the Find function instead.

Dim FindRng As Range

Set FindRng = Cells.Find("Details")
If Not FindRng Is Nothing Then ' Find was successful
    FindRng.Select ' <-- not sure what you want to do after you find the cell with "Details" ?
Else ' Find failed
    MsgBox "Unable to find 'details'", vbCritical
End If

answered Mar 31, 2018 at 6:08

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

Comments

user4039065 · Accepted Answer · 2018-03-31 06:02:27Z

2

As soon as the first Exit For executes, it bypasses the second. You need to place the second Exit For outside the inner loop.

Dim valCheck As Variant

For i = 0 To row1
    For j = 0 To col1
        MsgBox ("started" & i)
        valCheck = Range("A1").Offset(i, j).Value
        MsgBox (valCheck)
        If valCheck = "Details" Then
            MsgBox ("found")
            ActiveCell = Range("A1").Offset(i + 1, j)
            Exit For
        End If
    Next j
    If valCheck = "Details" Then Exit For
Next i

answered Mar 31, 2018 at 6:02

user4039065

Collectives™ on Stack Overflow

Excel VBA Exit For does not work inside If block

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related