Worksheet_Change

[Guest]

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

 

[JE McGimpsey]

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

 

[Guest]

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]

What line is highlighted when you click Debug?

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.

 

[Guest]

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]

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

 

[Guest]

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 "isect>0" 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 isect>0 fails (the comparison of "...Nothing>0..." 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

 

[Guest]

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.