Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code error
Does anyone know whats wrong with the code below?
I get an error in the third line where it starts with: Set inputrange=..... Says Application defined or Object defined error Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set inputrange = Sheets("Output").Range("Eng_Range") Set Inrng = Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code error
I don't get that error as long as the named range exists. You're sure that
named range exists and is spelled correctly? "M&M" wrote: Does anyone know whats wrong with the code below? I get an error in the third line where it starts with: Set inputrange=..... Says Application defined or Object defined error Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set inputrange = Sheets("Output").Range("Eng_Range") Set Inrng = Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code error
Yep the name range is spelled right. But hey I typed in not only the named
range but also gave more info on the sheet it is found in and that solved it. Heres what I have and it works!! Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set W3 = Worksheets(3) Set inputrange = Worksheets("Database").Range("Eng_Range") Set Inrng = Worksheets("Database").Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub "JMB" wrote: I don't get that error as long as the named range exists. You're sure that named range exists and is spelled correctly? "M&M" wrote: Does anyone know whats wrong with the code below? I get an error in the third line where it starts with: Set inputrange=..... Says Application defined or Object defined error Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set inputrange = Sheets("Output").Range("Eng_Range") Set Inrng = Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code error
When you give it a try make sure you write your own worksheet name, altough u
may index it as well. "N.F" wrote: Yep the name range is spelled right. But hey I typed in not only the named range but also gave more info on the sheet it is found in and that solved it. Heres what I have and it works!! Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set W3 = Worksheets(3) Set inputrange = Worksheets("Database").Range("Eng_Range") Set Inrng = Worksheets("Database").Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub "JMB" wrote: I don't get that error as long as the named range exists. You're sure that named range exists and is spelled correctly? "M&M" wrote: Does anyone know whats wrong with the code below? I get an error in the third line where it starts with: Set inputrange=..... Says Application defined or Object defined error Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set inputrange = Sheets("Output").Range("Eng_Range") Set Inrng = Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code error
Alternatively, when referencing worksheets in vba - use the code name (see
vba help for details). The VBA project explorer lists the worksheets by codename with the tab name in parenthesis. So "Database" may appear as: Sheet3 (Database) and it could be referenced either by Sheets("Database").Range("Eng_Range") Worksheets("Database").Range("Eng_Range") Sheets(3).Range("Eng_Range") Worksheets(3).Range("Eng_Range") Sheet3.Range("Eng_Range") 'Using the code name The last example, however, is independent of the position of the worksheet in the workbook and the worksheet tab name. Note the Worksheets and Sheets collections will return different results depending on whether or not you have Chart Sheets. Also, the code name cannot be changed programmatically. You can change it to something more descriptive by clicking on the properties button of the control toolbox toolbar (while in Excel environment) or through the VBA properties window (in VBA environment). Since the code name changes less often than the tab name or index number, I rarely use tab names. "N.F" wrote: When you give it a try make sure you write your own worksheet name, altough u may index it as well. "N.F" wrote: Yep the name range is spelled right. But hey I typed in not only the named range but also gave more info on the sheet it is found in and that solved it. Heres what I have and it works!! Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set W3 = Worksheets(3) Set inputrange = Worksheets("Database").Range("Eng_Range") Set Inrng = Worksheets("Database").Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub "JMB" wrote: I don't get that error as long as the named range exists. You're sure that named range exists and is spelled correctly? "M&M" wrote: Does anyone know whats wrong with the code below? I get an error in the third line where it starts with: Set inputrange=..... Says Application defined or Object defined error Private Sub SelectArguments_Click() EngIndex = EngineSelect.List(EngineSelect.ListIndex, -1) Set inputrange = Sheets("Output").Range("Eng_Range") Set Inrng = Range(inputrange(1, EngIndex + 1), inputrange(inputrange.Rows.Count, EngIndex + 1)) For Each Cell In Inrng If IsEmpty(Cell.Value) Then Exit For Else UserForm1.ListBox2.AddItem (Cell.Value) End If Next UserForm1.Show End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in Code | Excel Discussion (Misc queries) | |||
error in this code | Excel Worksheet Functions | |||
Code Error | Excel Discussion (Misc queries) | |||
Error Code 481 | Excel Discussion (Misc queries) | |||
Code error ?? | Excel Discussion (Misc queries) |