PDA

View Full Version : [SOLVED] find a string



lior03
06-07-2005, 02:38 AM
hello
i would like to add to the following macro a msgbox to indicate that the string searched was not found . i keep getting a message about error 91.
any suggestions?
please help


Sub finders4()
On Error Resume Next
Dim x As String
Cells.Select
x = InputBox("choose value to search", "hadara")
Selection.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
End Sub

Killian
06-07-2005, 02:56 AM
Hi and welcome to VBAX :hi:

The Find method returns a range so you can test to see if it's nothing and act accordingly.
Also, the Find method applies to a range so I've amended your example to search all the cells on the activesheet without selecting them, which is preferable


Sub finders4()
Dim x As String
Dim rngResult As Range
x = InputBox("choose value to search", "hadara")
Set rngResult = ActiveSheet.Cells.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If rngResult Is Nothing Then
MsgBox x & " not found"
Else
rngResult.Activate
MsgBox rngResult.Address
End If
End Sub

MWE
06-07-2005, 10:46 AM
the above approach will work, but will only find the first instance of the search string. Finding multiple instances is not very hard. A fairly simple way is to repeat the search process starting After the most recent hit until the search wraps around and encounters the first instance. Tweaking Killian's code:


Sub finders4()
Dim x As String
Dim rngResult As Range
Dim strBuffer As String
Dim xlCell As Range
Dim xlCell0 As Range
x = InputBox("choose value to search", "hadara")
' find first instance
Set rngResult = ActiveSheet.Cells.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If rngResult Is Nothing Then
MsgBox x & " not found"
Exit Sub
End If
' rngResult.Activate
' MsgBox rngResult.Address
strBuffer = strBuffer & rngResult.Address & " "
Set xlCell = rngResult
Set xlCell0 = xlCell
' find additional instances
GetNext:
Set rngResult = ActiveSheet.Cells.Find(What:=x, After:=xlCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If rngResult Is Nothing Or rngResult.Address = xlCell0.Address Then
MsgBox x & " found in the following cells: " & strBuffer
Exit Sub
Else
strBuffer = strBuffer & rngResult.Address & " "
Set xlCell = rngResult
GoTo GetNext
End If
End Sub

This code is far from optimal, but provides a sense for how to find multiple instances.

mvidas
06-07-2005, 10:52 AM
Just to chime in a little, I have a function that I use frequently that returns a range object with any/all instances of the search term in the specified range. Occasionally I will adjust the LookIn and LookAt arguments depending on what I'm searching for.


Function FoundRange(ByVal vRG As Range, ByVal vVal) As Range
Dim FND As Range, FND1 As Range
Set FND = vRG.Find(vVal, LookIn:=xlValues, LookAt:=xlWhole)
If Not FND Is Nothing Then
Set FoundRange = FND
Set FND1 = FND
Set FND = vRG.FindNext(FND)
Do Until FND.Address = FND1.Address
Set FoundRange = Union(FoundRange, FND)
Set FND = vRG.FindNext(FND)
Loop
End If
End Function

Matt

MWE
06-07-2005, 11:38 AM
Matt: nice solution. I never knew that there was a FindNext :dunno-- sure makes for better and more compact code. Well, we learn something new every day