Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error '9' New Question
I have posted this problem before and didnt get it resolved, fishing again
hoping to get lucky. Here is my code: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = ActiveSheet With wsSheet Set rngNext = .Range("F65536").End(xlUp).Offset(1, 0) End With rngNext.Select Set myRange = Range("F2", rngNext) 'populate Group combobox list With combStation Selection.End(xlUp).Select For Each rngNext In myRange If rngNext = "" Then Selection.End(xlUp).Select End If .AddItem rngNext Next rngNext End With Here is my problem: When I have the worksheet set at: Set wsSheet = ActiveSheet The userform works as planned and I continue. When I try to set it to: Set wsSheet = Worksheets("List1") I get a Run-Time error '9': and the userform fails. I have isolated the problem to that line a code. I tried the obvious and saved the workbook. From there I need ya'll help. Also with the help of a few members here I have also ruled out adding the code setting the workbook. Any help you can give me would be most appreciated. Thanks in advance, Ryan -- Fighting Texas Aggie Class of 2009 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error '9' New Question
I think the problem could be this line:
Set myRange = Range("F2", rngNext) You could try: Set myRange = Range(ws.range("F2"), rngNext) "Texas Aggie" wrote: I have posted this problem before and didnt get it resolved, fishing again hoping to get lucky. Here is my code: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = ActiveSheet With wsSheet Set rngNext = .Range("F65536").End(xlUp).Offset(1, 0) End With rngNext.Select Set myRange = Range("F2", rngNext) 'populate Group combobox list With combStation Selection.End(xlUp).Select For Each rngNext In myRange If rngNext = "" Then Selection.End(xlUp).Select End If .AddItem rngNext Next rngNext End With Here is my problem: When I have the worksheet set at: Set wsSheet = ActiveSheet The userform works as planned and I continue. When I try to set it to: Set wsSheet = Worksheets("List1") I get a Run-Time error '9': and the userform fails. I have isolated the problem to that line a code. I tried the obvious and saved the workbook. From there I need ya'll help. Also with the help of a few members here I have also ruled out adding the code setting the workbook. Any help you can give me would be most appreciated. Thanks in advance, Ryan -- Fighting Texas Aggie Class of 2009 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error '9' New Question
You don't have a worksheet named List1 in the activeworkbook.
Maybe the workbook you want to use isn't active. Or maybe there's a typo in the code or in the name of the worksheet. And after you find your solution to that, I'd drop the .select's and use something like: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = thisworkbook.worksheets("List1") 'ActiveSheet With wsSheet Set myRange = .Range("F2", .cells(.rows.count,"F").end(xlup)) End With 'populate Group combobox list With combStation For Each rngNext In myRange.Cells If rngNext.value = "" Then 'do nothing else .AddItem rngNext.Value end if Next rngNext End With Texas Aggie wrote: I have posted this problem before and didnt get it resolved, fishing again hoping to get lucky. Here is my code: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = ActiveSheet With wsSheet Set rngNext = .Range("F65536").End(xlUp).Offset(1, 0) End With rngNext.Select Set myRange = Range("F2", rngNext) 'populate Group combobox list With combStation Selection.End(xlUp).Select For Each rngNext In myRange If rngNext = "" Then Selection.End(xlUp).Select End If .AddItem rngNext Next rngNext End With Here is my problem: When I have the worksheet set at: Set wsSheet = ActiveSheet The userform works as planned and I continue. When I try to set it to: Set wsSheet = Worksheets("List1") I get a Run-Time error '9': and the userform fails. I have isolated the problem to that line a code. I tried the obvious and saved the workbook. From there I need ya'll help. Also with the help of a few members here I have also ruled out adding the code setting the workbook. Any help you can give me would be most appreciated. Thanks in advance, Ryan -- Fighting Texas Aggie Class of 2009 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error '9' New Question
Your awsome,
the new code works and dropping the .selects was the key. Thanks alot for the help this has been hanging over my head since last night. -- Fighting Texas Aggie Class of 2009 "Dave Peterson" wrote: You don't have a worksheet named List1 in the activeworkbook. Maybe the workbook you want to use isn't active. Or maybe there's a typo in the code or in the name of the worksheet. And after you find your solution to that, I'd drop the .select's and use something like: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = thisworkbook.worksheets("List1") 'ActiveSheet With wsSheet Set myRange = .Range("F2", .cells(.rows.count,"F").end(xlup)) End With 'populate Group combobox list With combStation For Each rngNext In myRange.Cells If rngNext.value = "" Then 'do nothing else .AddItem rngNext.Value end if Next rngNext End With Texas Aggie wrote: I have posted this problem before and didnt get it resolved, fishing again hoping to get lucky. Here is my code: Dim wsSheet As Worksheet Dim rngNext As Range Dim myRange As Range Set wsSheet = ActiveSheet With wsSheet Set rngNext = .Range("F65536").End(xlUp).Offset(1, 0) End With rngNext.Select Set myRange = Range("F2", rngNext) 'populate Group combobox list With combStation Selection.End(xlUp).Select For Each rngNext In myRange If rngNext = "" Then Selection.End(xlUp).Select End If .AddItem rngNext Next rngNext End With Here is my problem: When I have the worksheet set at: Set wsSheet = ActiveSheet The userform works as planned and I continue. When I try to set it to: Set wsSheet = Worksheets("List1") I get a Run-Time error '9': and the userform fails. I have isolated the problem to that line a code. I tried the obvious and saved the workbook. From there I need ya'll help. Also with the help of a few members here I have also ruled out adding the code setting the workbook. Any help you can give me would be most appreciated. Thanks in advance, Ryan -- Fighting Texas Aggie Class of 2009 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Run-time error question | Excel Programming | |||
VBA Run-time Error "13" - Question | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |