Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching for a word(s) within a list | Excel Discussion (Misc queries) | |||
highlight the word when excel finds text you are searching for | Excel Worksheet Functions | |||
Is Excel or Word better for holding and searching data | Excel Discussion (Misc queries) | |||
searching a cell for a contained text word | Charts and Charting in Excel | |||
Vlookup for part of a word | Excel Worksheet Functions |