Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Run Time error 91
-------------------------------------------------------------------------------- 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's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=472109 |
#2
|
|||
|
|||
Casey,
Delete the line: Dim Cells As Range Jim Cone San Francisco, USA "Casey" wrote in message 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 |
#3
|
|||
|
|||
Jim, Worked and then immediately locked Excel requiring a forced shutdown of Excel. Any ideas. -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=472109 |
#4
|
|||
|
|||
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 ... Jim, Worked and then immediately locked Excel requiring a forced shutdown of Excel. Any ideas. Casey |
#5
|
|||
|
|||
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 ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=472109 |
#6
|
|||
|
|||
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and motion chart | Charts and Charting in Excel | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
Time Sheets | New Users to Excel | |||
unmet challenge | Excel Worksheet Functions |