XLorate by davesexcel

Find Loop


Sub FindLoop()
    Dim sh As Worksheet, LstRw As Long, Rng As Range
    Dim FndVal As String, c As Range, FistFnd As String

    FndVal = "Hi"
    Set sh = Sheets("Sheet1")

    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A1:A" & LstRw)
        With Rng
            Set c = .Find(FndVal, LookIn:=xlValues)
            If Not c Is Nothing Then
                FistFnd = c.Address
                Do
                    c.Offset(, 1) = "Found"
                    Set c = .FindNext(c)
                    If c Is Nothing Then
                        GoTo Fin
                    End If
                Loop While c.Address <> FistFnd
            End If
Fin:
        End With
    End With

End Sub

Union Range,davesexcel union range, find item add to range variable, add to range

Using Find for many results

Sub SelectA1()
    Dim FrstRng As Range
    Dim UnionRng As Range
    Dim c As Range
    Set FrstRng = Range("A1:F20")

    For Each c In FrstRng.Cells
        If c = "A1" Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
                MsgBox UnionRng.Address    'remove later

            Else
                Set UnionRng = c
            End If
        End If
    Next c

    UnionRng.Select
End Sub

Union Range,davesexcel union range, find item add to range variable, add to range

Loop through a range of cells, find value,add to range

Double Loop Examples

Using Integers

 

Creating the Range of Cells

 

Using SpecialCells

 

Using AutoFilter in a Loop

This was an example of loop codes, but if you want to know the fastest way to Color those cells.

Use Conditional Formatting

Other Loops

Loop Examples by Davesexcel
Excel VBA Loop Examples


Loop using fixed Number


Loop using Variable

Loop  and Calculate

Loop Get Unique Items

Loop Find Item