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

[Devitt]

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?

 

[Norman Jones]

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