Run Time error 91

[Casey]

--------------------------------------------------------------------------------

Hi,
I have a little matrix 5R x 6C. Located on a sheet in Cells E23:J27. All cells within the matrix have data validation in them to restrict the input to "1" or "0". It is OK to have mutiple selections of "1's" in the same row, except if the user happen to select a "1" for the sixth or last cell in the row. If that happens I would like the other five cells in that row to have a value of "0". A couple of weeks ago Gary's Student gave me some starter Code that I have been trying to make into a Worksheet_Change procedure, but no luck.
My Data looks something like:

E F G H I J
23 1 0 0 1 1 0 ok
24 1 0 1 0 0 1 Not ok
25 0 0 0 0 0 1 ok
26 0 0 0 1 1 1 Not ok
27 1 1 1 1 1 0 ok

When the code executes I get the following error

Run time error '91'
Object variable or with block varible not set.

Here is the Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim N As Integer
Dim Cells As Range
Dim wks1 As Worksheet

Set wks1 = Worksheets("SET UP SHT(1)")

If Not Intersect(Target, Range("E23:J27")) Is Nothing Then

For i = 23 To 27
N = wks1.Cells(i, 10).Value
If N = 1 Then
Cells(i, 5) = 0
Cells(i, 6) = 0
Cells(i, 7) = 0
Cells(i, 8) = 0
Cells(i, 9) = 0
End If
Next i


End If
End Sub

Thanks for any help

[Jim Cone]

Casey,

Delete the line: Dim Cells As Range

Jim Cone
San Francisco, USA


"Casey" wrote in message
news:[email protected]
Hi,
I have a little matrix 5R x 6C. Located on a sheet in Cells E23:J27.
All cells within the matrix have data validation in them to restrict
the input to "1" or "0". It is OK to have mutiple selections of "1's"
in the same row, except if the user happen to select a "1" for the
sixth or last cell in the row. If that happens I would like the other
five cells in that row to have a value of "0". A couple of weeks ago
Gary's Student gave me some starter Code that I have been trying to
make into a Worksheet_Change procedure, but no luck.
My Data looks something like:

E F G H I J
23 1 0 0 1 1 0 ok
24 1 0 1 0 0 1 Not ok
25 0 0 0 0 0 1 ok
26 0 0 0 1 1 1 Not ok
27 1 1 1 1 1 0 ok

When the code executes I get the following error
Run time error '91'
Object variable or with block varible not set.
Here is the Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim N As Integer
Dim Cells As Range
Dim wks1 As Worksheet
Set wks1 = Worksheets("SET UP SHT(1)")
If Not Intersect(Target, Range("E23:J27")) Is Nothing Then
For i = 23 To 27
N = wks1.Cells(i, 10).Value
If N = 1 Then
Cells(i, 5) = 0
Cells(i, 6) = 0
Cells(i, 7) = 0
Cells(i, 8) = 0
Cells(i, 9) = 0
End If
Next i
End If
End Sub
Thanks for any help--
Casey

[Casey]

Jim,
Worked and then immediately locked Excel requiring a forced shutdown of Excel. Any ideas.

[Jim Cone]

Casey,

I vetted the change I suggested in a standard module, so there
was not a problem. However, the code belongs in the module
behind the sheet "SET UP SHT(1)".

When that is done, the code will run every time there is a change
to any cell in the specified range. Therefore, when a cell was
changed the code ran and changed cells which caused the code
to run again to change cells and so on until Excel froze.

To prevent that from happening the code should turn off the
recycling event using "Application.EnableEvents = False".
However, EnableEvents has to be turned on again after the code runs.
Regards,
Jim Cone
San Francisco, USA

'---------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Dim i As Long
Dim N As Variant
Application.EnableEvents = False

If Not Intersect(Target, Range("E23:J27")) Is Nothing Then
For i = 23 To 27
N = Cells(i, 10).Value
If N = 1 Then
Range(Cells(i, 5), Cells(i, 9)).Value = 0
'The next two lines can be commented out or deleted
'and the code will still work...
Else
Range(Cells(i, 5), Cells(i, 9)).Value = vbNullString
End If
Next 'i
End If

BadChange:
Application.EnableEvents = True
End Sub
'----------------------------------------


"Casey"
wrote in message
news:[email protected]...

Jim,
Worked and then immediately locked Excel requiring a forced shutdown of
Excel. Any ideas.
Casey

[Casey]

Jim,
Thank you so much for the help. I got what I needed with the Enable Events. I tried running your code but it cleared the entire first 5 columns whenever I put a "1" in any row in the 6th column, not what I was looking for. Below is the Code I pasted together with your input. Two questions if you have time. One, is it necessary to change the N varible data type to variant? Two is the error handling procedure necessary, do I need to incorporate it into my revised code? Thanks again Jim for the code and the cascading event lesson, I really appreciate your time.

Here's my revised code which works great.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim N As Integer
Dim wks1 As Worksheet

Set wks1 = Worksheets("SET UP SHT(1)")
Application.EnableEvents = False

If Not Intersect(Target, Range("E23:J27")) Is Nothing Then

For i = 23 To 27
N = wks1.Cells(i, 10).Value
If N = 1 Then
Cells(i, 5) = 0
Cells(i, 6) = 0
Cells(i, 7) = 0
Cells(i, 8) = 0
Cells(i, 9) = 0
End If
Next i


End If
Application.EnableEvents = True
End Sub

[Jim Cone]

Casey,

If you absolutely sure that numeric values only will be used in
the worksheet than a data type of Double is what I would use.
Numeric values are returned as Doubles from a worksheet range.
To use another data type forces Excel to convert the value.
It you are not positive about what will be entered into the cells then use a variant.

Yes it is necessary to use something similar to the error handling
I wrote. If the code ever threw an error, then you would have events
disabled in the entire Excel application.

Its usually best to have error handling included in all code.
Even if you want the code to continue on despite any errors,
you can be alerted to the fact that something went wrong with...

On Error Goto Err_Handler
'Code here
Exit sub
Err_Handler:
Beep
Resume Next
End Sub
'------------
Regards,
Jim Cone


"Casey" wrote in message
news:[email protected]
Jim,
Thank you so much for the help. I got what I needed with the Enable
Events. I tried running your code but it cleared the entire first 5
columns whenever I put a "1" in any row in the 6th column, not what I
was looking for. Below is the Code I pasted together with your input.
Two questions if you have time. One, is it necessary to change the N
varible data type to variant? Two is the error handling procedure
necessary, do I need to incorporate it into my revised code? Thanks
again Jim for the code and the cascading event lesson, I really
appreciate your time.

Here's my revised code which works great.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim N As Integer
Dim wks1 As Worksheet

Set wks1 = Worksheets("SET UP SHT(1)")
Application.EnableEvents = False

If Not Intersect(Target, Range("E23:J27")) Is Nothing Then

For i = 23 To 27
N = wks1.Cells(i, 10).Value
If N = 1 Then
Cells(i, 5) = 0
Cells(i, 6) = 0
Cells(i, 7) = 0
Cells(i, 8) = 0
Cells(i, 9) = 0
End If
Next i
End If
Application.EnableEvents = True
End Sub
--
Casey