PDA

View Full Version : Sleeper: Search string 2



lior03
06-09-2005, 05:20 AM
hello
several internt sites offer in the search function to enter a string similiar to the one you look for.
for instance - msn entertainment
if you enter john gilgood you are directed to john gielgud the famous
british actor.
is it possible in excel.
could it be incorporated in a macro killian wrote two days ago.

Sub finders33()
Dim x As String
Dim rngResult As range
x = InputBox("choose partial value to search", "text finder")
Set rngResult = ActiveSheet.Cells.Find(What:=x, LookIn:=xlFormulas, LookAt:=xlPart, _
searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
ActiveSheet.Cells.Font.ColorIndex = 1
If rngResult Is Nothing Then
MsgBox x & " ?? ???? "
Else
rngResult.Activate
rngResult.Font.ColorIndex = 1
rngResult.EntireRow.Font.ColorIndex = 3
End If
End Sub
thanks

Norie
06-09-2005, 05:39 AM
What do you mean?

Can't you just type john gielgud or whatever into the inputbox?

Aaron Blood
06-09-2005, 06:28 AM
What you describe is called soundex search capability. There's some example code on the J-Walk website that shows how to do soundex searches using VBA.

http://j-walk.com/ss/excel/tips/tip77.htm

Killian
06-09-2005, 06:50 AM
Hi moshe,

I think I get the idea.
You can do a string comparison to get an exact match or whether it's contained in the string.
you can also use the Like operator and write some algorithms to do as many tests as you please. How good the search is depends on how thorough your comparisons are. How fast the search is cepends on how effieciently you code it.

I've attached a userform-based search here. It's basic and inefficient but it should be clear from the code how it works...