Run-Time error '9' New Question

[Texas Aggie]

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

 

[Sam Wilson]

I think the problem could be this line:

Set myRange = Range("F2", rngNext)

You could try:

Set myRange = Range(ws.range("F2"), rngNext)

 

[Dave Peterson]

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]

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

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