+ Reply to Thread
Results 1 to 7 of 7

"No more instances found"

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    "No more instances found"

    In a macro I have this search procedure (bottom up and right to left)

    Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    Silly, but I don't know the proper code saying "if there are no more occurrences, then...". None of those in the HELP section (Not Found, NoMatch, Is Not) seem to apply.

    Thanks for your patience
    ACA

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,595
    2 ways
    1)
    On Error Resume Next
    Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    If Err <> 0 Then
        MsgBox "Not found"
    End If
    On Error GoTo 0
    2) use object variable
    Dim r As Range
    Set r = Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If r Is Nothing then
        MsgBox "Not found"
    End If

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Your code appears to be looking for a cell, but no serch string to find. Try this
    Dim cl     As Range
        Dim lCnt   As Long
        Dim sFind  As String
        Dim sClAddress As String
        With Worksheets(1).UsedRange
    
            sFind = "Find me"    'change this
            Set cl = .Find(sFind, LookIn:=xlValues)
            If Not cl Is Nothing Then
                sClAddress = cl.Address
                lCnt = lCnt + 1
                Do
                    cl.Interior.ColorIndex = 3
                    Set cl = .FindNext(cl)
                Loop While Not cl Is Nothing And cl.Address <> sClAddress
            End If
        End With
        Select Case lCnt
            Case 0: MsgBox "No instances of " & sFind & " found"
            Case Is > 0: MsgBox lCnt & " instances of " & sFind & " found & highlighted"
        End Select
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241
    Thank so very much to you both, jindon and royUK, for the immediate help.
    aca

  5. #5
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241
    Your code, works O.K., but finding one or all occurrences at once, and then stopping, is not what I need;
    sorry I didn’t express myself well.

    First, in each case I enter, through an input box, the chain I'm looking for (which I call ‘c’)
    Dim c As Variant
        c = InputBox(Chr(13) & " Escribe parte d lo q buscas", , , 1000, 4000)
    
       Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    But then I need for the macro to do just what a general search function does (only upwards) i.e.
    Go up, PAUSE at each occurrence and, if that cell is the one I need, let me exit the search and have the cell copied etc.;
    if it is not, let me click somewhere else and GO ON TO THE NEXT occurrence and pause again. And so on.
    But when there are no more, let me kow also (message box “No more to be found”).

    Thanks for your help, and sorry I first mistakenly posted this as a new thread.
    ACA

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,595
    Sub test()
    Dim c, r As Range, ff As String
    c = InputBox(Chr(13) & " Escribe parte d lo q buscas", , , 1000, 4000)
    Set r = Cells.Find(c,,xlValues, xlPart, xlByColumns, xlPrevious)
    If Not r Is Nothing Then
        ff = r.Address
        Do
            r.Activate
            If vbYes = MsgBox("Is this the one?", vbYesNo) Then exit Do
            Set r = Cells.FindPrevious(r)
        Loop Until ff = r.Address
    End If
    [End Sub

  7. #7
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241
    Thank you, jindon: works great! It's what I needed
    Bless you.
    ACA

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1