Excel - Subscript out of range

Asked By KJMA on 14-Sep-08 10:46 AM
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




GarysStuden replied on 14-Sep-08 10:59 AM
First:
Response = MsgBox(ref.Address, vbYesNo, "Test")

Next is make sure the data can be Found.
--
Gary''s Student - gsnu200804
KJMA replied on 14-Sep-08 11:02 AM
Thaks for the info.

The Subscript error occurs on the With Statement. it will not execute past
that point. Any suggestions?
Thanks
Mike replied on 14-Sep-08 11:14 AM
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
Dave Peterson replied on 14-Sep-08 11:20 AM
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")



--

Dave Peterson
GarysStuden replied on 14-Sep-08 11:20 AM
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
KJMA replied on 14-Sep-08 11:31 AM
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
KJMA replied on 14-Sep-08 11:41 AM
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 replied on 14-Sep-08 11:44 AM
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)
KJMA replied on 14-Sep-08 11:52 AM
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
KJMA replied on 14-Sep-08 11:58 AM
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 replied on 14-Sep-08 12:19 PM
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 replied on 14-Sep-08 12:20 PM
to overcome the case problem use this

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

Mike
Mike replied on 14-Sep-08 12:26 PM
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
KJMA replied on 14-Sep-08 01:07 PM
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.
KJMA replied on 14-Sep-08 01:15 PM
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 replied on 14-Sep-08 02:13 PM
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 replied on 14-Sep-08 09:48 PM
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, chdh@source-code.biz
'   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)
Rick Rothstein replied on 15-Sep-08 04:23 AM
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)
KJMA replied on 15-Sep-08 10:16 PM
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 replied on 16-Sep-08 04:47 AM
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)
KJMA replied on 16-Sep-08 08:45 PM
Thanks
Rick Rothstein replied on 16-Sep-08 11:11 PM
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)