Using the for loop to search for the values of i

Question

If I use a for i = 1 to 1000, how do I use the Cell.Find function to search for the value of i in my sheet?

I need to check if any of the numbers from 1 to 1000 are in the sheet (or row...doesn't matter). Preferably using the for loop.

I've done the following:

Dim i As Integer
For i = 1 To 10
    Cells.Find(What:="i", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _, SearchFormat:=False).Activate

Next i

End Sub

Remove the quotes from `"i"` so that it reads `Cells.Find(What:=i, ...` — tigeravatar, Mar 21 '16 at 20:44
to piggy-back on @tigeravatar correct comment, because as it's written it will look for the literal string `[lower case letter I]` on each loop. — Scott Holtzman, Mar 21 '16 at 20:50
You can change `After:=ActiveCell` to `After:=range("A1")` or wherever the first cell is located, then you don't have to worry where the activecell is. — Davesexcel, Mar 21 '16 at 20:52
In fact, you [*should*](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) change `ActiveCell` to `Range("A1")`. I can't recommend understanding how to avoid `.Select` and `.Activate` enough. — BruceWayne, Mar 21 '16 at 21:08

score 0 · Answer 1 · answered Mar 21 '16 at 21:11

If there is just one instance of i then find could work for you.

Sub UsingFind()
    Dim i As Integer, c As Range

    For i = 1 To 1000
        Set c = Cells.Find(what:=i, lookat:=xlWhole)

        If Not c Is Nothing Then
            c.Font.Bold = 1
        Else:    'MsgBox "Not Found"
        End If
    Next i
End Sub

If there is more than one instance of i then something else would have to be used, such as looping through each cell.

For example(x is used instead of i):

Sub MoreThanOne()
    Dim x As Integer, c As Range, rng As Range

    Set rng = Cells.SpecialCells(xlCellTypeConstants, 23)
    For x = 1 To 1000
        For Each c In rng.Cells
            If c = x Then
                c.Font.Bold = 1
            End If
        Next c
    Next x

End Sub

Using the for loop to search for the values of i

1 Answers1