[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
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
..........................
..........................
I SOLVED this question about the slow searches described below.. I replaced LookIn:=xlValues with xlFormulas...
But I must admit I have no clue as to why using xlFormulas can search 5,000 Rows in about one second and LookIn:=xlValues takes about 8 seconds......
I am still wishing for an solution to the issue I have with the Application.inputbox in my original post
.................................................................................................... .................................................................................................... ....
I went ahead and made a Userform searchbox with ShowModal set to False so I can select a cell while the Form is displayed, as I could with an application.inputbox
And for testing purposes I'm using a more simplified Find Method Routine in the userform, but this is searching very slow if each match is thousands of rows apart.
In contrast to the Aplication.inputbox routine I first posted here, which will search 15,000 rows lightning fast
This userform routine can take upwards of 8 seconds if the next match is 3,000 rows or more down the sheet.
If I change SearchOrder:=xlByRows to xlByColumns that speeds it up a lot, but I think that may not suit me needs. I'll have to post back after more testing on that.
Any ideas?
Thanks in advance
Code:
Sub OpenUserform1()
UserForm1.TextBox1.Value = ActiveCell.Value
UserForm1.Show
End Sub
Code:
Private Sub CommandButton1_Click()
Application.EnableEvents = False
Dim c As Range
If Len(Me.TextBox1.Value) = 0 Then
MsgBox "No Criteria Entered"
End If
Set c = Cells.Find(What:=Me.TextBox1.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False)
If Not c Is Nothing Then
c.Activate
Else
MsgBox "No Matches found"
End If
Application.EnableEvents = True
End Sub
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
hi sqladoman,
this is in reply to your first entry:
i don't have excel 2007, but i noticed one thing:
when calling "inputbox" your default parameter may be a value OR a range (at least that is what is stated in the online vba 2003 help). i would believe excel looks at your "M1" *value* and interprets this as a range. that's why the cell "M1" seems to be selected all the time. i'm guessing this is whats foiling your plan. can't verify this for 2007 though...
Code:
Find_Next:
stringToFind = Application.InputBox(Input_Instructions, _
"[" & StartAdrss & "]<-Search began at this address.", _
MyDefaultSearchValue <- this may also be a range , 5, 10, , , 2)
as in response to your second post, the only difference i can see in your search routine, is your reference value which is sited in the userform. note sure about this, but maybe that's causing the lag.
hope this helps
cheers,
ray
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
Yeah I think get where you are coming from, but the Type 2 inputbox I am using is supposed to be for strings not ranges, the range type is type 8.
- The help file in this matter I feel should give a lot more information about inputbox type parameters,(string, range, number types), uses and limitations..
* By my view having a string type parameter for the inputbox should offer more versatility than it does.
Also as an added observation, if "M85" is the search criteria entered in the inputbox, when it finds cell address M85, the routine is not actually selecting the M85 cell, as it will if a match is found in any other cell...Instead the previous cell where a match was found stays selected and the routine just hangs there with the little dotted lines surrounding Cell M85.. -- When I say "hangs", I mean nothing happens when I press the ok button to try to find the next match, as it normally would. --
-- If I manually select any cell (including the M85 Cell, then the M85 cells value, or other selected cell value becomes the new search criteria as its supposed) and I can at that point either push the ok button and find a match to the value contained in cell M85, or I can type "M85" into the inputbox and and push the ok button to search again, or I can enter a new criteria. ----- In other words, if the criteria is "M85", and a cell before cell M85 contains the string "M85" that cell will be selected and I can continue by pressing the ok button to look for another match and it will work fine until cell M85 is reached.
It may very well be that what I want just cannot be done. I am coming to accept that
and I believe the userform I created will work well now that I solved the issue on how to make it search quickly. --- At this point I'm more curious than anything.
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
I know your beyond this, but just for the heck :D of it I tried something:
Only for illustration purposes, just implement the following 2 modifications:
Code:
Find_Next:
stringToFind = Application.InputBox(Input_Instructions, _
"[" & StartAdrss & "]<-Search began at this address.", "'" & MyDefaultSearchValue, Type:=2)
If Left(stringToFind, 1) = "'" Then stringToFind = Right(stringToFind, Len(stringToFind) - 1)
stringToFind = UCase(stringToFind)
I'm just adding an ' in front of the default value, which makes it impossible for Excel to interpret this as a range.
In the next step I remove it again (only if it exists) ensuring i'm searching for the correct string. And it seems to work!
This at least proves that Type:=2 does not hinder Excel in assuming MyDefaultSearchValue as a range in some cases.
oh well :eek2:
cheers,
ray
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
Thanks Ray for your idea and your time.
I have to laugh though, as I think we both found near the same solution at the same time.. * Using your same concept,
except yours is far simpler if it will work.
I was going to use ' but decided to use asterisk instead *
Either or I think is fine. --- I will test yours soon. It may be awhile as this thing tired me out, hee-hee. :afrog:
BELOW is my new routine that works great.
Edit: Not as great as i thought. See post #'s: 7 8 and 9 for reasons why.
Or just see Post#9 for my latest version of the routine that so far has tested to be virtually flawless.
My modification amounts to a few lines of code near the end of the routine where I prefix my default criteria with an asterisk, right before GoTo Find_Next
The additional commands are pretty much self explanatory if you take a look.
Code:
Sub Find_Inpute()
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."
Dim myResult As String 'prefix criteria with asterisks to avoid finding cell address's.
MyDefaultSearchValue = "*" & stringToFind 'doimg this retains current criteria for find next
myResult = Replace(MyDefaultSearchValue, "**", "*")" 'trims extra asterisks if more than one
MyDefaultSearchValue = myResult
GoTo Find_Next
End With
End Sub
Re: Application.Inpubox is finding strings & addresses. I only want it to find string
Hey Ray
Your the man of the hour. , at least in my book, hee-hee
Either prefix works and your code is only one line plus the slight modification of one.
My code additions that do the same thing, are more bulky, and a couple more lines of code than yours.
Cheers to you man !! :) :check: :thumb: Nice Job
Edit:
The ' Character, is what I first thought of trying before I opted to use the asterisk *
--> I still think it's hilarious that we both thought of prefixing with ' at nearly the same moment.(both work identically well), but the ' is preferable as it's less noticable.
By the way I rated your post. I didn't really see any choice of different rating level, or I would have given you a near top score.
I did though add a nice compliment in the rating box about your solution.
Edit #2: Ray - I noticed that adding the ' where you did is FAR BETTER than what i did.
Ray, with your code the criteria is prefixed for the very first search :thumb:, which I have come to discovered is in some cases, is absolutely necessary..
-My coding only prefixes for 2nd search and beyond, which could potentially screw up the initial search.
Anyone trying my routine please use Rays coding, rather than the way I have it in my posted solution.
see Post#9 for my latest version of the routine that so far has tested to be virtually flawless.
Re: [RESOLVED] Application.Inpubox is finding strings & addresses. I only want it to
Glad I could help :)
Just learning in the process. Had some fun too :D
cheers,
ray
Application.Inpubox Find Method Search & Find Next. Now avoids finding cell addresss
Here's the Final version of the routine whith my issue solved. - Thanks to the great help on this by User: RaytracerFFM :thumb:
Thanks again Ray ! :):thumb:
Code:
Sub Find_Input()
'routine best for finding criteria in cells formatted as general or text.
'Will search number formatted cells, if not formatted to use comma's, or decimal places.
'Finds date if entered as appears in FormulaBar(ie:m/dd/yyyy),not necessarily as appears in cell.
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." _
& vbNewLine & "" _
& vbNewLine & "You may select any cell **with the inputbox still displayed**" _
& vbNewLine & "and that cell selection contents will become the new search criteria."
StartAdrss = ActiveCell.Address
MyDefaultSearchValue = Trim(UCase(ActiveCell.Value)) 'Initial Search Value
Find_Next:
'prefix's the variable MyDefaultSearchValue with an apostrophe to prevent finding cell address's.
stringToFind = Application.InputBox(Input_Instructions, _
"Inputbox was opened at cell address[" & StartAdrss & "]", "'" & MyDefaultSearchValue, Type:=2)
'This next command removes extra apostrophe/s if there is more than one accumulated
If Left(stringToFind, 1) = "'" Then stringToFind = Right(stringToFind, Len(stringToFind) - 1)
'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 initial cellvalue,this message willnot display."
MyDefaultSearchValue = stringToFind 'retains criteria value needed for a Find Next Search
GoTo Find_Next
End With
End Sub
