Help with this error Unknown reason for error with Range().Select

Here is my code. The error happens when the sheet is changed and it selects
the starting cell for Movie1 sheet. The error says "Select Method of Range
Class failed". I have never ran into this problem before. If anyone has any
ideas on other ways to accomplish what I am trying to do I am open to any
suggestions

'Day is row to place info in
'Movie is the name of the movie for that time
'PCount is the Performance Count for that time
Dim Day As Integer
Dim PCount(9 To 22) As Integer
Dim Movie(9 To 22) As Integer
Dim X As Integer

Private Sub cmdApply_Click()
Worksheets("Main").Activate
Range("A3").Select
If ActiveCell.Value = "" Then GoTo E
Day = ActiveCell.Value

For X = 9 To 22
Range("C5").Select
Movie(X) = ActiveCell.Value
ActiveCell.Offset(0, 2).Select
Next X

For X = 9 To 22
Range("C3").Select
PCount(X) = ActiveCell.Value
ActiveCell.Offset(0, 2).Select
Next X

Worksheets("Movie1").Activate
Range("C3").Select
ActiveCell.Offset(Day, 0).Select
For X = 9 To 22
If X = Movie(X) Then
ActiveCell.Value = PCount(X)
Else
ActiveCell.Value = 0
End If
ActiveCell.Offset(0, 2).Select
Next X

Worksheets("Movie2").Activate
Range("C3").Select
ActiveCell.Offset(Day, 0).Select
For X = 9 To 22
If X = Movie(X) Then
ActiveCell.Value = PCount(X)
Else
ActiveCell.Value = 0
End If
ActiveCell.Offset(0, 2).Select
Next X

Worksheets("Movie3").Activate
Range("C3").Select
ActiveCell.Offset(Day, 0).Select
For X = 9 To 22
If X = Movie(X) Then
ActiveCell.Value = PCount(X)
Else
ActiveCell.Value = 0
End If
ActiveCell.Offset(0, 2).Select
Next X

Worksheets("Movie4").Activate
Range("C3").Select
ActiveCell.Offset(Day, 0).Select
For X = 9 To 22
If X = Movie(X) Then
ActiveCell.Value = PCount(X)
Else
ActiveCell.Value = 0
End If
ActiveCell.Offset(0, 2).Select
Next X

E: End Sub

Hi Brad,

Since your code resides in a sheet module, any unqualified range reference
will be interpreted as referring to the sheet which holds the code and not
to the active sheet.

Since a range cannot be selected on an inactive sheet,

a line like:

> Range("A3").Select

will cause an error if the active sheet is not the sheet holding the code.

The solution is fully to qualify each range reference with the parent sheet,
i.e.:

Worksheets("Main").Range("A3")

Additionally, whilst your code activates various sheets and selects various
ranges, such physical selections are unnecessary and inefficient. For
example, instead of:

> Worksheets("Main").Activate
> Range("A3").Select
> If ActiveCell.Value = "" Then GoTo E
> Day = ActiveCell.Value

You could, without selections use:

With Worksheets("Main")
With .Range("A3")
If .Value = "" Then GoTo E
rw = .Value
End With

(Since Day is a VBA function, I have replaced Day with rw as the variable)

---
Regards,
Norman



"Brad Sumner" <[email protected]> wrote in message
news:[email protected]...
> Here is my code. The error happens when the sheet is changed and it
> selects
> the starting cell for Movie1 sheet. The error says "Select Method of
> Range
> Class failed". I have never ran into this problem before. If anyone has
> any
> ideas on other ways to accomplish what I am trying to do I am open to any
> suggestions
>
> 'Day is row to place info in
> 'Movie is the name of the movie for that time
> 'PCount is the Performance Count for that time
> Dim Day As Integer
> Dim PCount(9 To 22) As Integer
> Dim Movie(9 To 22) As Integer
> Dim X As Integer
>
> Private Sub cmdApply_Click()
> Worksheets("Main").Activate
> Range("A3").Select
> If ActiveCell.Value = "" Then GoTo E
> Day = ActiveCell.Value
>
> For X = 9 To 22
> Range("C5").Select
> Movie(X) = ActiveCell.Value
> ActiveCell.Offset(0, 2).Select
> Next X
>
> For X = 9 To 22
> Range("C3").Select
> PCount(X) = ActiveCell.Value
> ActiveCell.Offset(0, 2).Select
> Next X
>
> Worksheets("Movie1").Activate
> Range("C3").Select
> ActiveCell.Offset(Day, 0).Select
> For X = 9 To 22
> If X = Movie(X) Then
> ActiveCell.Value = PCount(X)
> Else
> ActiveCell.Value = 0
> End If
> ActiveCell.Offset(0, 2).Select
> Next X
>
> Worksheets("Movie2").Activate
> Range("C3").Select
> ActiveCell.Offset(Day, 0).Select
> For X = 9 To 22
> If X = Movie(X) Then
> ActiveCell.Value = PCount(X)
> Else
> ActiveCell.Value = 0
> End If
> ActiveCell.Offset(0, 2).Select
> Next X
>
> Worksheets("Movie3").Activate
> Range("C3").Select
> ActiveCell.Offset(Day, 0).Select
> For X = 9 To 22
> If X = Movie(X) Then
> ActiveCell.Value = PCount(X)
> Else
> ActiveCell.Value = 0
> End If
> ActiveCell.Offset(0, 2).Select
> Next X
>
> Worksheets("Movie4").Activate
> Range("C3").Select
> ActiveCell.Offset(Day, 0).Select
> For X = 9 To 22
> If X = Movie(X) Then
> ActiveCell.Value = PCount(X)
> Else
> ActiveCell.Value = 0
> End If
> ActiveCell.Offset(0, 2).Select
> Next X
>
> E: End Sub
>