Exiting a For loop

Sometimes, it is necessary to exit a For loop prematurely. For example, if the purpose of the code is to check for a particular value in a For range, and that value has been found, then the code should not have to check all other values in that range. However, this will happen because a For loop will by default continue through the range. Fortunately, there is a way to circumvent it. You can jump out of the For. Each loop when the item has been found by using the Exit For statement. The next example illustrates how it works.

Listing 7.4 uses the weeklySales sheet from the SALESMAN workbook. This procedure checks each cell in the rep_name range to see if a particular name exists, and will output the message "found at" followed by the cell address. It works by declaring a variable called thisName that will enable the user to enter a name via an InputBox. A Boolean variable isFound is use to inform the user whether or not the rep name is found. It does this by initially assuming that it is not found. When the For.Next loop begins on Line 8, if the value of myCell = thisName, then the rep name has been found and the variable isFound is set to True. The MsgBox follows with the appropriate

Listing 7.4

1 Sub checkRepName()

2 Dim thisName As Variant

3 Dim myCell As Object

4 Dim isFound As Boolean

5 Worksheets("Weeklysales").Select

6 isFound = False 'assume rep name is not found yet

7 thisName = InputBox(prompt:="enter name")

8 For Each myCell In Range("rep_name")

9 If myCell = thisName Then 'we found it

10 myCell.Interior.ColorIndex = 4

11 isFound = True

12 MsgBox "found at " & myCell.Address

13 Exit For 'jump out of the loop

14 End If

15 Next

16 If Not(isFound) Then

17 MsgBox "The rep name was not found"

18 End If

19 End Sub message. Notice the Exit For statement on line 13 - if the repName is found then the procedure jumps out of the loop. This means that the MsgBox on line 17 will not follow since isFound is True. If the rep name is not found, then isFound is False so that the MsgBox on Line 17 will then be executed.

E Microsoft Excel - SALESMAN

File Edit View Insert Format Tools Data Window Help 13 H ® * Times New Roman t io t |~b~| I

Microsoft Excel

End of MnnüiS« Monthly Bonus enter name S ¡ii". To Date: Month No:

Bojufs líjate: Month No:

Cancel |

te to the Mon" Dtal by the Boj ales column ft

Rep Name

Andy Bintäi

'Mis&a

Fete'

Rhiaimon

Lijieth

TOTALS

Sales To Date

489. .333 336 453 330

Sales To Date

Weekly Sales

Monllily Total

Monllily Total

Monthly Ï

Figure 7.5 Input screenshot for Listing 7.4

Figure 7.6 Output screenshot for Listing 7.4
0 0

Responses

  • seppo
    How to exit for loop in access vba?
    8 years ago

Post a comment