|
-
Jun 9th, 2010, 03:11 AM
#1
Thread Starter
Addicted Member
[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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|