Cells.find and .findnext

**royUK** · 08-20-2007, 07:49 AM

The VBA help file example is good.

**Buzzaro** · 08-20-2007, 07:59 AM

Originally Posted by royUK

The VBA help file example is good.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Ive looked at that, but Im very new so theres a couple things that are different than the method ive posted that look different that im not sure about.

Worksheets(1).Range("a1:a500")

I would like to search the entire active worksheet as opposed to a specified range and also I tried doing the select worksheet thing but I dont know which worksheet in the workbook the macro is going to be run on, so i would just like it run on the active worksheet. Ive tried adapting that syntax to use the .find method ive posted above and I cant seem to get it to work.

**mudraker** · 08-20-2007, 08:29 AM

When working with the active worksheet you do not need to use worksheets(1) in the code

With Cells
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

As the above example finds 2 and replaces that with 5 you could use

Cells.Replace What:="2", Replacement:="5", LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.find and .findnext

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions