VBA: Loop until Find function fails

[liquid81]

Hi,

Beginner question here.

Say I have a VBA script that finds the value 100 in a worksheet and replaces is with 1. I want it to run until there is no cell with value 100 in the worksheet anymore.

How can I do this? Do I use a Loop function?

Thanks in advance.

 

[onlyadrafter]

Hello,

try

Sub Macro1()
    Cells.Replace What:="100", Replacement:="1", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

don't really see the need for a loop.

 

[xld]

Lifted straight from help, untetested

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

 

[liquid81]

Hello,

try

Sub Macro1()
    Cells.Replace What:="100", Replacement:="1", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

don't really see the need for a loop.

Thanks for the quick reply. You're right, the scenario I gave doesn't need one.

But let's say I have a whole script (not just replacing 100 with 1 but with lots of other changes to the sheet) that starts with finding the value 100. What I want to do is to run this script over and over until it cannot find a cell with value 100 anymore.

Thanks!

 

[onlyadrafter]

Hello,

how about

Sub Macro2()
   Cells.SpecialCells(xlCellTypeConstants, 23).Select
   For Each CELL In Selection
    Select Case CELL
        Case 100
            CELL.Value = 1
        Case 1
            CELL.Value = 10
    End Select
    Next CELL
End Sub

add further

Case
CELL.value =

before the End Select