Okay, here is a completely rewritten Soundex function that ignores
non-alphabetic letters completely...

Public Function Soundex(ByVal S As String) As String
Dim X As Long
Const CodeTab = " 123 12 22455 12623 1 2 2"
' abcdefghijklnmopqrstuvwxyz
If Len(S) = 0 Then Exit Function
S = UCase(S)
Soundex = Left(S, 1)
For X = 2 To Len(S)
If Mid(S, X, 1) Like "[A-Z]" Then
Soundex = Soundex & Mid(CodeTab, Asc(Mid(S, X, 1)) - 64, 1)
End If
Next
Soundex = Replace(Soundex, " ", "")
For X = 1 To 6
Do While InStr(Soundex, CStr(X) & CStr(X)) 0
Soundex = Replace(Soundex, CStr(X) & CStr(X), CStr(X))
Loop
Next
Soundex = Left(Soundex & "0000", 4)
End Function

I don't want you to get the wrong idea about the accuracy of Soundex
functions in general... they are somewhat crude. Usually they are
implemented to give the user a choice of exact, or somewhat near, matches to
a string he/she types in. You may have seen versions of it implemented in
dictionaries where you type in, for example, fotograf and it returns several
possible words it 'thinks' you might have meant with the idea you will scan
the list and select the actual word (photograph) you meant. As for you
question about "A woman's love" and "A WOMANS LOVE", the function will
return the same code value, so you would conclude they are similar. However,
don't get too comfortable with the matches it returns the same code for "A
man is alive" too. I would say the main strength of the function is when it
is used on single words rather than multi-worded phrases or sentences.

--
Rick (MVP - Excel)


"KJ MAN" wrote in message
...
Thanks


"Rick Rothstein" wrote:

I am going to sleep soon, so I'll give you a more complete answer later
on
today. I would note that the case of the text is immaterial to the
Soundex
function; however, I got values I didn't expect from your examples, so I
looked closer at the code. It looks like it works on single words only,
and
then only if they don't contain certain characters (like an apostrophe).
This is an artificial set of restrictions which should not require too
much
effort to remove. As I said, I'll look at this again after I wake up.

--
Rick (MVP - Excel)


"KJ MAN" wrote in message
...
Will soundex return a value of
A woman's love

if the search criteria is
A WOMANS LOVE
?
Notice the apostraphe and case differences.



"Rick Rothstein" wrote:

The Soundex function does not return a percentage match value, it just
encodes the normal sound certain letters make and then issues an
encoded
value for the letters it sees... that is why you put the found word
and
the
search word both into the function... to see if the sound of their
letters
is the same. The exact match has to be handled separately because an
exact
match and an almost match (no matter how close to the original word)
all
get
the same encoded Soundex evaluation and you wanted exact matches to be
found
first.

--
Rick (MVP - Excel)


"KJ MAN" wrote in message
...
In the interest of consolidating code, would it not be best to
write some code that will return data based of the value of the
soundex
match.
for instance, a 100% match first, 90% next 10 % last and so on (not
that
10%
actually needs to be returned).

Would that not be better than having two seperate functions
performing
the
search?

"Rick Rothstein" wrote:

I guess I should clarify a little... since you want exact matches
first,
do
the If..Then test as Mike showed it and if nothing is found, then
do
the
test using the Soundex function as shown.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in
message
...
You can do what you want using a Soundex function. Change this
line
from
Mike's code...

If c.Value = myvar Then

to this...

If Soundex(c.Value) = Soundex(myvar) Then

then add a Module to your project (Insert/Module from the VB
editor's
menu
bar) and copy/paste this function into its code window...

' Computes the "Soundex" value of a string.
' This version produces exactly the same results as
' the Soundex function of Microsoft SQL Server 2000.
' Author: Christian d'Heureuse,
' Code webpage:
http://www.source-code.biz/snippets/vbasic/4.htm
'
Public Function Soundex(ByVal S As String) As String
Const CodeTab = " 123 12 22455 12623 1 2 2"
' abcdefghijklnmopqrstuvwxyz
If Len(S) = 0 Then Soundex = "0000": Exit Function
Dim c As Integer
c = Asc(Mid$(S, 1, 1))
If c = 65 And c <= 90 Or c = 97 And c <= 122 Then
' nop
ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c =
248
Then
' nop
Else
Soundex = "0000"
Exit Function
End If
Dim ss As String, PrevCode As String
ss = UCase(Chr(c))
PrevCode = "?"
Dim p As Integer: p = 2
Do While Len(ss) < 4 And p <= Len(S)
c = Asc(Mid(S, p))
If c = 65 And c <= 90 Then
' nop
ElseIf c = 97 And c <= 122 Then
c = c - 32
ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c
=
248
Then
c = 0
Else
Exit Do
End If
Dim Code As String: Code = "?"
If c < 0 Then
Code = Mid$(CodeTab, c - 64, 1)
If Code < " " And Code < PrevCode Then ss = ss & Code
End If
PrevCode = Code
p = p + 1
Loop
If Len(ss) < 4 Then ss = ss & String$(4 - Len(ss), "0")
Soundex = ss
End Function

--
Rick (MVP - Excel)


"KJ MAN" wrote in message
...
That worked, ok, One more thing I need.
I actually need the search to return values based of of near
matches
but
not
necessarily exact matches.
If someone types Michel, and the app will return values such as:
Michel, Michael, Mychel, etc.
Is there anyway to do this and return exact matches first, then
near
matches.

Also if someone types Simple Man, the app will return A Simple
Man.

Any suggestions?

"Mike H" wrote:

I missed the second bit. If it missed some values you think it
should
have
got then that could be rogue spaces. try this

If UCase(Trim(c.Value)) = UCase(myvar) Then

Mike

"Mike H" wrote:

to overcome the case problem use this

If UCase(c.Value) = UCase(myvar) Then

Mike

"KJ MAN" wrote:

I 've found Mike H's code to be great...
There is however an issue, the search is case sensitive and
I
need
it not to
be.
also, I had one occurance where the first and last return
matched
the search
criteria, however, there were thousands in between that did
not
match the
search criteria.... Any suggestions?

Thanks

"Chip Pearson" wrote:

You need to declare the 'Response' variable. E.g., at the
top
of
the
procedure (before any code), use

Dim Response As Long
' OR, better
Dim Response As VbMsgBoxResult


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"KJ MAN" wrote in
message
...
Gary''s....

I tried your code exactly, I can now get beond the
subscript
error but I
now
have a new error. On the response = MSGBOX statement I
get

Run-Time Error '91':
Object Variable or With Block Variable Not Set:

Work around? Thanks in advance



"Gary''s Student" wrote:

Both:

Private Sub CommandButton2_Click()
myvar = Application.InputBox("Enter Search Criteria",
"Search",
"Enter
Here")
Workbooks.Open ("c:\Otherbook.xls")
With Worksheets("sheet1").Range("c2:c10")
Set ref = .Find(myvar)
End With
Response = MsgBox(ref.Address, vbYesNo, "Test")
End Sub

and

Private Sub CommandButton2_Click()
myvar = Application.InputBox("Enter Search Criteria",
"Search",
"Enter
Here")
Workbooks.Open ("c:\Otherbook.xls")
With Worksheets("sheet1").Range("c2:c10")
Set ref = .Find(myvar)
End With
Response = MsgBox(ref, vbYesNo, "Test")
End Sub

will work. The Workbook qualifier was the problem.
--
Gary''s Student - gsnu200804


"KJ MAN" wrote:

Thaks for the info.

The Subscript error occurs on the With Statement. it
will
not
execute
past
that point. Any suggestions?
Thanks

"Gary''s Student" wrote:

First:
Response = MsgBox(ref.Address, vbYesNo, "Test")

Next is make sure the data can be Found.
--
Gary''s Student - gsnu200804


"KJ MAN" wrote:

Here's my code