For Each-Next Constructs

For Each-Next constructs


Basic construct of for-each

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
For Each element In collection

[instructions]
[Exit For]
[instructions]

Next [element]
[/sourcecode]

Here is a simple For Each-Next construct that changes all text within a selection to uppercase.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub MakeUpperCase() ‘Changes all text to uppercase within selection
Dim Cell As Range
For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next Cell
End Sub

Sub SelectNegative() ‘Selects first negative cell in row 1
Dim Cell As Range
For Each Cell In Range (“1:1”)
If Cell.Value < 0 Then
Cell.Select
Exit For ‘Exits here if negative
End If
Next Cell
End Sub
[/sourcecode]