ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Subscript out of range (https://www.excelbanter.com/excel-programming/417030-subscript-out-range.html)

KJ MAN[_2_]
Subscript out of range
 
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

Gary''s Student
Subscript out of range
 
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

KJ MAN[_2_]
Subscript out of range
 
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

Mike H
Subscript out of range
 
Maybe

Private Sub CommandButton2_Click()
Dim copyrange As Range, c As Range
Dim myvar As String
myvar = Application.InputBox("Enter Search Criteria", "Search", "Enter Here")
Workbooks.Open ("c:\Otherbook.xls")
Set myrange = Workbooks("Otherbook.xls").Sheets("sheet1").Range( "c2:c20")
For Each c In myrange
If c.Value = myvar Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next

If Not copyrange Is Nothing Then
copyrange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A1").PasteSpecial
End If
End Sub


Mike

"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

Gary''s Student
Subscript out of range
 
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

Dave Peterson
Subscript out of range
 
Don't include the drive/path in this statement:
With Workbooks("c:\Otherbook.xls").Worksheets("sheet1") .Range("c2:c10")
try:
With Workbooks("Otherbook.xls").Worksheets("sheet1").Ra nge("c2:c10")


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

--

Dave Peterson

KJ MAN[_2_]
Subscript out of range
 
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

KJ MAN[_2_]
Subscript out of range
 
Mike,


Your Code works Great for a single instance. How can I get the program
to pull all of the matching rows over. There may be 100 or more matching rows
and they each need to be copied.

Thanks

"Mike H" wrote:

Maybe

Private Sub CommandButton2_Click()
Dim copyrange As Range, c As Range
Dim myvar As String
myvar = Application.InputBox("Enter Search Criteria", "Search", "Enter Here")
Workbooks.Open ("c:\Otherbook.xls")
Set myrange = Workbooks("Otherbook.xls").Sheets("sheet1").Range( "c2:c20")
For Each c In myrange
If c.Value = myvar Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next

If Not copyrange Is Nothing Then
copyrange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A1").PasteSpecial
End If
End Sub


Mike

"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

Chip Pearson
Subscript out of range
 
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


KJ MAN[_2_]
Subscript out of range
 
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


KJ MAN[_2_]
Subscript out of range
 
Ahah, I think I found something.

Your code is assuming that the multiple data returned will be sequential in
the list.
It wont be. The matching appearances will be random.
Now, they could be changed to be sequential if the "Otherbook.xls" was
sorted first.


"Mike H" wrote:

Maybe

Private Sub CommandButton2_Click()
Dim copyrange As Range, c As Range
Dim myvar As String
myvar = Application.InputBox("Enter Search Criteria", "Search", "Enter Here")
Workbooks.Open ("c:\Otherbook.xls")
Set myrange = Workbooks("Otherbook.xls").Sheets("sheet1").Range( "c2:c20")
For Each c In myrange
If c.Value = myvar Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next

If Not copyrange Is Nothing Then
copyrange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A1").PasteSpecial
End If
End Sub


Mike

"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

Mike H
Subscript out of range
 
The code will find any instance of myvar in column c range copy each entire
row where it finds it and paste all those rows into the workbook (sheet1)
that the code is in. It doesn't matter if column C is sorted or not.

Mike

"KJ MAN" wrote:

Ahah, I think I found something.

Your code is assuming that the multiple data returned will be sequential in
the list.
It wont be. The matching appearances will be random.
Now, they could be changed to be sequential if the "Otherbook.xls" was
sorted first.


"Mike H" wrote:

Maybe

Private Sub CommandButton2_Click()
Dim copyrange As Range, c As Range
Dim myvar As String
myvar = Application.InputBox("Enter Search Criteria", "Search", "Enter Here")
Workbooks.Open ("c:\Otherbook.xls")
Set myrange = Workbooks("Otherbook.xls").Sheets("sheet1").Range( "c2:c20")
For Each c In myrange
If c.Value = myvar Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next

If Not copyrange Is Nothing Then
copyrange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A1").PasteSpecial
End If
End Sub


Mike

"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

Mike H
Subscript out of range
 
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


Mike H
Subscript out of range
 
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


KJ MAN[_2_]
Subscript out of range
 
Your code does return a range of data from the first instance to the last
instance: for example, the first row where the data was found, the last row
where the data was found, and everything in between (reguardless of matching)
was returned. I am not an experienced vb programmer but it looks to me like
the if then else where the data is matched ends before the data is pasted. I
have overcome that issue using the sort method, however, it would be nice to
not have to sort the data.

"Mike H" wrote:

The code will find any instance of myvar in column c range copy each entire
row where it finds it and paste all those rows into the workbook (sheet1)
that the code is in. It doesn't matter if column C is sorted or not.

Mike

"KJ MAN" wrote:

Ahah, I think I found something.

Your code is assuming that the multiple data returned will be sequential in
the list.
It wont be. The matching appearances will be random.
Now, they could be changed to be sequential if the "Otherbook.xls" was
sorted first.


"Mike H" wrote:

Maybe

Private Sub CommandButton2_Click()
Dim copyrange As Range, c As Range
Dim myvar As String
myvar = Application.InputBox("Enter Search Criteria", "Search", "Enter Here")
Workbooks.Open ("c:\Otherbook.xls")
Set myrange = Workbooks("Otherbook.xls").Sheets("sheet1").Range( "c2:c20")
For Each c In myrange
If c.Value = myvar Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next

If Not copyrange Is Nothing Then
copyrange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Sheets("Sheet1").Range("A1").PasteSpecial
End If
End Sub


Mike

"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

KJ MAN[_2_]
Subscript out of range
 
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


Mike H
Subscript out of range
 
Hi,

Something like
Michel, Michael, Mychel
is a bit thin on commonality to search for but have a look at the 'Like'
operator in VB help.

Simple Man - A Simple Man.
Has a bit more commonality and you could do this with the 'instr' function
also in VB help.

Mike

"KJ MAN" wrote:

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


Rick Rothstein
Subscript out of range
 
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

Rick Rothstein
Subscript out of range
 
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

KJ MAN[_2_]
Subscript out of range
 
The code gets a syntax error at
ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c = 248

I will enclose the line in ***!!*** in your code below

"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

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

KJ MAN[_2_]
Subscript out of range
 
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

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

Rick Rothstein
Subscript out of range
 
***!!*** ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c = 248
***!!***
Then

The word "Then" that appears on the next line all by itself should be on the
end of the line you highlighted... you newsreader wrapped the text because
the entire line (including its leading spaces) got too long (see what it did
to your ***!!*** mark that you put at the end of the line... it did that to
the word "Then" also). Taking away the leading spaces these two lines which
your newsreader is showing you...

ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c = 248
Then

should have actually read this...

ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c = 248 Then

If you make that change, the function's code should run fine.

--
Rick (MVP - Excel)


"KJ MAN" wrote in message
...
The code gets a syntax error at
ElseIf c = 192 And c <= 214 Or c = 216 And c <= 246 Or c = 248

I will enclose the line in ***!!*** in your code below

"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

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

Rick Rothstein
Subscript out of range
 
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

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

KJ MAN[_2_]
Subscript out of range
 
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

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

Rick Rothstein
Subscript out of range
 
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

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

KJ MAN[_2_]
Subscript out of range
 
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

Rick Rothstein
Subscript out of range
 
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


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com