Results 1 to 9 of 9

Thread: [RESOLVED] Application.Inpubox is finding strings & addresses. I only want it to find strings.

Threaded View

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2005
    Posts
    222

    Resolved [RESOLVED] Application.Inpubox is finding strings & addresses. I only want it to find strings.

    In Excel 2007

    If I enter for example "M1" into my search box it will find cells that contain the criteria "M1", but also it will go to Cell M1. And the worst of it is that once at cell M1, the search is stuck there. Where as if a string is found containing "M1", pressing the ok button allows the find next code to find the next match. How might I avoid having cell addressees found? I really need to use the application type inputbox because it allows me to both select a cell as a new search criteria with the inputbox open, plus I am able to access the sheet scrolling.

    I have the input box set at type 2 (string type), and I have experimented with declaring my stringToFind variable as a string, but still get the same issue.

    Thanks in advance for any help.
    Code:
    Sub Find_Input()
     
    Dim StartAdrss As String, Found As Range, Input_Instructions As String
    Dim MyDefaultSearchValue As String
    Dim stringToFind 'not declared as str, or var, so that Boolean = FALSE can close the inputbox.
    Input_Instructions = "Search for matches to the selected cell, or criteria you enter."
    StartAdrss = ActiveCell.Address
    MyDefaultSearchValue = ActiveCell.Value 'Initial Search Value
    
    Find_Next:
    stringToFind = Application.InputBox(Input_Instructions, _
    "[" & StartAdrss & "]<-Search began at this address.", MyDefaultSearchValue, 5, 10, , , 2)
    
    stringToFind = UCase(stringToFind)
    
    'stringToFind not declared as str or var, so Boolean = FALSE will close inputbox.
    If stringToFind = False Then
        '**USER CANCELED**
      Exit Sub
    End If
             
        If stringToFind = "" Then
           MsgBox "No Search value has been entered"
        End If
    On Error Resume Next 'Error handling so input box remains open if no matches are found.
    Set Found = Cells.Find(What:=stringToFind, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
            , MatchCase:=False)
    If Found Is Nothing Then
            MsgBox "No Matches found"
    End If
    
    With Found
            .Activate
    On Error GoTo 0 'clears Error Resume Next
    If StartAdrss = ActiveCell.Address Then MsgBox "The begin address has been reached" _
            & vbNewLine & "" _
            & vbNewLine & " * If criteria changed after the initial cell value, msg will not display."
            
        MyDefaultSearchValue = stringToFind'enables edited criteria to be retained for find next
        GoTo Find_Next
    End With
    
    End Sub
    Last edited by SQLADOman; Jun 9th, 2010 at 04:11 AM. Reason: Added blue highlighted comments on why I did not Dim the stringToFind variable

Posting Permissions

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



Click Here to Expand Forum to Full Width