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

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

I need my command button to search a range from c2:c10000
on a
different
workbook for matching info
and then return the entire row where the match was made.
There will
be
multiple
matches and each one needs to be returned. This is a test
code for
myself to
see if the search will return a value and I get

Run-Time error '9':

Subscript out of range.


Please Help