Subscript out of range

[KJ MAN]

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]

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

Next is make sure the data can be Found.

 

[KJ MAN]

Thaks for the info.

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

 

[Mike H]

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

 

[Gary''s Student]

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.

 

[Dave Peterson]

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").Range("c2:c10")

 

[KJ MAN]

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

 

[KJ MAN]

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

 

[Chip Pearson]

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]

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

 

[KJ MAN]

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]

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

 

[Mike H]

to overcome the case problem use this

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

Mike

 

[Mike H]

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

 

[KJ MAN]

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.

 

[KJ MAN]

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]

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

 

[Rick Rothstein]

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, (e-mail address removed)
' 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 Rothstein]

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.

 

[KJ MAN]

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