Results 1 to 5 of 5

VBA: Loop until Find function fails

This is a discussion on VBA: Loop until Find function fails within the Excel Questions forums, part of the Question Forums category; Hi, Beginner question here. Say I have a VBA script that finds the value 100 in a worksheet and replaces ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default VBA: Loop until Find function fails

    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.

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    4,644

    Default Re: VBA: Loop until Find function fails

    Hello,

    try

    Code:
    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.
    -------------------------
    Hope this is helpful.
    -------------------------
    only a drafter,
    but broadening my Excel knowledge.

  3. #3
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: VBA: Loop until Find function fails

    Lifted straight from help, untetested

    Code:
    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

  4. #4
    New Member
    Join Date
    Sep 2008
    Posts
    5

    Default Re: VBA: Loop until Find function fails

    Quote Originally Posted by onlyadrafter View Post
    Hello,

    try

    Code:
    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!

  5. #5
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    4,644

    Default Re: VBA: Loop until Find function fails

    Hello,

    how about

    Code:
    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

    Code:
    Case
    CELL.value =
    before the End Select
    -------------------------
    Hope this is helpful.
    -------------------------
    only a drafter,
    but broadening my Excel knowledge.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com