Run time error '1004'

[Guest]

Hi, I have written a small piece of code to populate 2 listboxes on an excel
worksheet. The code works fine when run in later versions of Excel (2000),
but when run in Excel 97 (which the majority of the users will be running) I
receive the following run time error;

Rumtime error '1004'
Unable to get the find property of the range class.

If I debug the program it seems to be the following piece of code which is
generating the error;

Rw1 = ws.Columns(2).Find(Me.ListBox1.Value, lookat:=xlWhole).Row

The whole of my code looks like;

Private Rw1 As Long, Rw2 As Long

Private Sub Worksheet_Activate()
Dim ws As Worksheet, r As Range, i As Integer, Header() As String
Set ws = Sheets("sheet2")
With ws
For Each r In .Range("b6,b19,b26,b49,b56,b83,b89,b96")
ReDim Preserve Header(i)
Header(i) = r.Value: i = i + 1
Next
End With
Me.ListBox1.List() = Header
End Sub

Private Sub ListBox1_Change()
Dim ws As Worksheet, a, x
x = Me.ListBox1.ListIndex
Set ws = Sheets("sheet2")
With ws
Rw1 = ws.Columns(2).Find(Me.ListBox1.Value, lookat:=xlWhole).Row
If Me.ListBox1.ListIndex < Me.ListBox1.ListCount - 1 Then
x = Me.ListBox1.List(x + 1)
Rw2 = ws.Columns(2).Find(what:=x, lookat:=xlWhole).Row
Else
Rw2 = ws.Range("b65536").End(xlUp).Row + 1
End If
a = .Range(.Cells(Rw1 + 1, "b"), .Cells(Rw2 - 1, "b")).Value
End With
Me.ListBox2.List = a
Erase a
End Sub

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Range, ws As Worksheet
Set ws = Sheets("sheet2")
With ws
Set r = .Range(.Cells(Rw1 + 1, "b"), .Cells(Rw2 - 1,
"b")).Find(what:=Me.ListBox2.Value, lookat:=xlWhole)
If Not r Is Nothing Then
Set r = r.Resize(, 3)
Me.Range("c65536").End(xlUp).Offset(1).Resize(, 3).Value = r.Value
End If
End With
End Sub

Sheet2 contains a list of items under headings. On sheet1, Listbox1 displays
all of the headings and then when a heading is selected displays all of the
data for that header in Listbox2, then each item form Listbox2 can be double
clicked and entered into the last free cell on Sheet1. The program works
perfect in Excel 2000, which is what it was written in, unfortunately many of
the users will be running Excel 97.

Any help with this issue would be much appreciated as I have trawled every
article I can find on this issue.

Regards

Jon Keane

P.S If anybody should wish to look at the Excel workbook in question, I have
made it available @
http://www.hillsdata.co.uk/public/jonkeane/runtime_err.xls

 

[Bob Phillips]

Jon,

Where is the error? I see no error handling around the Find and the value
returned, but seems to work if valid data.

 

[Guest]

Bob,

The error occurs when you click on an item in listbox 1, this should then
populate listbox2 with the corresponding data for that header. But instead I
recieve the error stated in the original message. I cant see why the data
would not be valid as it works in later versions of Excel fine. Any ideas of
anything I could try to confirm??

Thanks