C
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 restric
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 th
sixth or last cell in the row. If that happens I would like the othe
five cells in that row to have a value of "0". A couple of weeks ag
Gary's Student gave me some starter Code that I have been trying t
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 hel
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 restric
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 th
sixth or last cell in the row. If that happens I would like the othe
five cells in that row to have a value of "0". A couple of weeks ag
Gary's Student gave me some starter Code that I have been trying t
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 hel