Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error '1004'
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/jo...untime_err.xls |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error '1004'
Jon,
Where is the error? I see no error handling around the Find and the value returned, but seems to work if valid data. -- HTH Bob Phillips "Jon Keane" wrote in message ... 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/jo...untime_err.xls |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error '1004'
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 "Bob Phillips" wrote: Jon, Where is the error? I see no error handling around the Find and the value returned, but seems to work if valid data. -- HTH Bob Phillips "Jon Keane" wrote in message ... 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/jo...untime_err.xls |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error '1004'
Anybody got any further ideas about this. There must be a solution as it runs
fine in later versions of Excel. Regards. "Jon Keane" wrote: 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 "Bob Phillips" wrote: Jon, Where is the error? I see no error handling around the Find and the value returned, but seems to work if valid data. -- HTH Bob Phillips "Jon Keane" wrote in message ... 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/jo...untime_err.xls |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Run-time error '1004' | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming | |||
Application Run Time Error 1004 and Stack Error | Excel Programming |