Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Can anybody see why the object variable will not set? I can't, but it won't.
Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Perhaps Target doesn't include any cells in column E?
Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub In article , JLGWhiz wrote: Can anybody see why the object variable will not set? I can't, but it won't. Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Thanks, J.E. It is still telling me the object variable is not set, but
oddly enough, it does the value assinment from C to B. Methinks there's a grimlin about. I'll keep playing around with it until I figure this out. I hate it when a stupid machine gets the best of me. "JE McGimpsey" wrote: Perhaps Target doesn't include any cells in column E? Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub In article , JLGWhiz wrote: Can anybody see why the object variable will not set? I can't, but it won't. Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
What line is highlighted when you click Debug?
In article , JLGWhiz wrote: Thanks, J.E. It is still telling me the object variable is not set, but oddly enough, it does the value assinment from C to B. Methinks there's a grimlin about. I'll keep playing around with it until I figure this out. I hate it when a stupid machine gets the best of me. "JE McGimpsey" wrote: Perhaps Target doesn't include any cells in column E? Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
This is weird. Stepping through the code, if first jumps from the first If
Not to End Sub with isect not Set. Then it jumps back up to the second if, Sets the isect and performs the events. It's nuts, but I am not getting the variable not set message any more. It does not compute. "JE McGimpsey" wrote: Perhaps Target doesn't include any cells in column E? Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub In article , JLGWhiz wrote: Can anybody see why the object variable will not set? I can't, but it won't. Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
When the value in column B is changed, it fires the Worksheet_Change()
event again, and processing in the original event macro is suspended until that change is dealt with (the Intersect in that case *should* fail). You can prevent this with Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then On Error Resume Next Application.EnableEvents = False .Offset(0, -3).Value = .Offset(0, -2).Value Application.EnableEvents = True On Error GoTo 0 End If End With End If End Sub In article , JLGWhiz wrote: This is weird. Stepping through the code, if first jumps from the first If Not to End Sub with isect not Set. Then it jumps back up to the second if, Sets the isect and performs the events. It's nuts, but I am not getting the variable not set message any more. It does not compute. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Hi JLGWhiz -
Although I've never seen it officially documented, it's not possible to use a comarison operator to compare a variable that has a value of "Nothing" to a value. In other words, the "isect0" comparison in your original post was causing the error. As JE notes, the procedure triggers itself and during the 'second' round of execution, the variable isect is set to Nothing. That is when the comparison isect0 fails (the comparison of "...Nothing0..." is not permitted). In this case, I consider the error dialog misleading: "Object variable..... not set". To see why this is misleading, reproduce the error. In debug mode, highlight the right side of the following statement and press the QuickWatch button: Set isect = Application.Intersect(Target, Range("E:E")) The QuickWatch will confirm that the right side of the equation has a value of "Nothing." So, in essence isect actually IS set to a value. It just so happens that the value to which it is set (Nothing) is not permitted in the statement that follows. One final demonstration of this is to consider the following three statements: Dim isect As Range Set isect = Range("A1") Set isect = Nothing If you step through these statements and watch the value of isect, you'll see that it is actually 'set' to Nothing in the third statement. That's different than isect having a default value of Nothing because it failed to be set. So, all that being said, here is a solution that uses nested IF statements to preclude the problemmatic comparison from occuring (as does JE's approach): Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If Not isect Is Nothing Then If IsNumeric(isect) And isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End If End Sub ---- Jay "JLGWhiz" wrote: This is weird. Stepping through the code, if first jumps from the first If Not to End Sub with isect not Set. Then it jumps back up to the second if, Sets the isect and performs the events. It's nuts, but I am not getting the variable not set message any more. It does not compute. "JE McGimpsey" wrote: Perhaps Target doesn't include any cells in column E? Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub In article , JLGWhiz wrote: Can anybody see why the object variable will not set? I can't, but it won't. Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change
Thanks guys, I can shake some of the cobwebs out of my head now. I had never
had this particular circumstance before and it gave me a fit. My old brain just couldn't decipher this one, but your explanations cleared up the mud. "Jay" wrote: Hi JLGWhiz - Although I've never seen it officially documented, it's not possible to use a comarison operator to compare a variable that has a value of "Nothing" to a value. In other words, the "isect0" comparison in your original post was causing the error. As JE notes, the procedure triggers itself and during the 'second' round of execution, the variable isect is set to Nothing. That is when the comparison isect0 fails (the comparison of "...Nothing0..." is not permitted). In this case, I consider the error dialog misleading: "Object variable..... not set". To see why this is misleading, reproduce the error. In debug mode, highlight the right side of the following statement and press the QuickWatch button: Set isect = Application.Intersect(Target, Range("E:E")) The QuickWatch will confirm that the right side of the equation has a value of "Nothing." So, in essence isect actually IS set to a value. It just so happens that the value to which it is set (Nothing) is not permitted in the statement that follows. One final demonstration of this is to consider the following three statements: Dim isect As Range Set isect = Range("A1") Set isect = Nothing If you step through these statements and watch the value of isect, you'll see that it is actually 'set' to Nothing in the third statement. That's different than isect having a default value of Nothing because it failed to be set. So, all that being said, here is a solution that uses nested IF statements to preclude the problemmatic comparison from occuring (as does JE's approach): Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If Not isect Is Nothing Then If IsNumeric(isect) And isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End If End Sub ---- Jay "JLGWhiz" wrote: This is weird. Stepping through the code, if first jumps from the first If Not to End Sub with isect not Set. Then it jumps back up to the second if, Sets the isect and performs the events. It's nuts, but I am not getting the variable not set message any more. It does not compute. "JE McGimpsey" wrote: Perhaps Target doesn't include any cells in column E? Possible solution: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Me.Range("E:E")) If Not isect Is Nothing Then With isect(1) If .Value 0 Then _ .Offset(0, -3).Value = .Offset(0, -2).Value End With End If End Sub In article , JLGWhiz wrote: Can anybody see why the object variable will not set? I can't, but it won't. Appreciate another set of eyeballs looking at this. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("E:E")) If isect 0 Then Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
about worksheet_change | Excel Programming | |||
Worksheet_change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |