Searching using part of a word rather than whole word in vba
 

Ok i couldn't think how to word that in to a question very well (it
early and i need a smoke)..
Say i column 1 has several names, i wanna find the name of someon
called.. erm i dunno.. 'Dave'.. my search feature i have present mean
i have to type 'Dave' to find 'Dave', but what about 'David'? I jus
wanna beable to type 'Dav' and find all the 'daves' and 'davids'.
tryed recording a macro of myself doing the good ol' excel searc
(ctrl+f). Now that worked a charm.. except when it doesn't fin
anything and i get the error "Object variable or with block variabl
not set"
Could anyone help meh?

"My current search 'feature'" Wrote:
Private Sub CommandButton1_Click()
Dim search as String
search = txtsearch.Text
Columns("A:A").Select
Selection.fIND(What:=search, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False, SearchFormat:=False).Activate
End Su

--
Devit
-----------------------------------------------------------------------
Devitt's Profile: http://www.excelforum.com/member.php...fo&userid=1442
View this thread: http://www.excelforum.com/showthread.php?threadid=26840

Searching using part of a word rather than whole word in vba
 

Hi Devitt,

Try something like:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim foundCell As Range
Dim SrchRng As Range
Dim foundCellAdd As String
Dim sStr As String

sStr = txtsearch.Text
Set sh = Me.Parent.Worksheets("sheet1")
Set SrchRng = sh.Range("A:A")

With SrchRng
Set foundCell = .Find(What:=sStr, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If foundCell Is Nothing Then
'String not found on the sheet so inform user
MsgBox """" & sStr & """" & " not found in " & _
SrchRng.Address & " on " & sh.Name
Else
'String found so take some suitable action, e.g.
foundCellAdd = foundCell.Address
'Return the address of the found string
MsgBox foundCellAdd
'If you must, select the found string
sh.Activate
foundCell.Select
End If
End With

End Sub

---
Regards,
Norman



"Devitt" wrote in message
...

Ok i couldn't think how to word that in to a question very well (its
early and i need a smoke)..
Say i column 1 has several names, i wanna find the name of someone
called.. erm i dunno.. 'Dave'.. my search feature i have present means
i have to type 'Dave' to find 'Dave', but what about 'David'? I just
wanna beable to type 'Dav' and find all the 'daves' and 'davids'. I
tryed recording a macro of myself doing the good ol' excel search
(ctrl+f). Now that worked a charm.. except when it doesn't find
anything and i get the error "Object variable or with block variable
not set"
Could anyone help meh?

"My current search 'feature'" Wrote:
Private Sub CommandButton1_Click()
Dim search as String
search = txtsearch.Text
Columns("A:A").Select
Selection.fIND(What:=search, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
End Sub


--
Devitt
------------------------------------------------------------------------
Devitt's Profile:
http://www.excelforum.com/member.php...o&userid=14423
View this thread: http://www.excelforum.com/showthread...hreadid=268403