2 conditions in Worksheet_Change event

D

David

I currently use this workaround which works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(0, 2).Select
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value > 1 Then
'---continue sub

I would like the input range in Column C to be limited to "C5:E35"
e.g. If Intersect(Target, Range("C5:C35")) Then Target.Offset(0, 2).Select

This gets me to desired cell in Column E, but then things bomb 'Runtime
error 91' as soon as I input anything in "E5:E35"

How to fix?
 
B

Bob Phillips

David,

You're not looking are you? The answer is already in the code, use

If Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

David

Bob Phillips wrote
You're not looking are you? The answer is already in the code, use

If Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select
Click to expand...

Well, if it were that obvious, I would have used it. Problem is I only want
that to happen (Target.Offset(0,2).Select) if I enter something in C5:C35.
Your offering moves 2 cols over no matter where I enter something.
 
D

David

David wrote
I currently use this workaround which works:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then Target.Offset(0, 2).Select
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value > 1 Then
'---continue sub

I would like the input range in Column C to be limited to "C5:E35"
e.g. If Intersect(Target, Range("C5:C35")) Then Target.Offset(0,
2).Select

This gets me to desired cell in Column E, but then things bomb
'Runtime error 91' as soon as I input anything in "E5:E35"

How to fix?
Click to expand...

This works the way I want:
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then Target.Offset(0,
2).Select
 
B

Bob Phillips

No you wouldn't because you omitted to test for Is Nothing that is why you
got an error. If you couldn't see that it is unlikely you can solve it.

And it is impossible to be in column C and in E, so better coding would
handle than more efficiently. As it is, rather than re-cut all the code, I
tried to fix it in your style, but I made a small mistake in the code I
posted which I am sure you are now going to spot and solve for yourself.

RP
 
D

David

Bob Phillips wrote
And it is impossible to be in column C and in E, so better coding
would handle than more efficiently. As it is, rather than re-cut all
the code, I tried to fix it in your style, but I made a small mistake
in the code I posted which I am sure you are now going to spot and
solve for yourself.
Click to expand...

See reply to myself for what now works.

I don't see what 'better coding would handle than more efficiently',
although I can't deny there could be. I'm all ears.

My goals:
1) if entry is in C5:C35, move two columns over to E
2) if entry is in E5:E35, then process that entry
There are occasions when I enter in C or E or both

Accomplished with

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then _
Target.Offset(0, 2).Select
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E5:E35")) Is Nothing Then Exit Sub
If Target.Value > 1 Then
'do the stuff I'm not posting here
End Sub

Anyway, I appreciate your efforts to penetrate my thick skull <g>.
 
B

Bob Phillips

David said:
Bob Phillips wrote


See reply to myself for what now works.
Click to expand...

Yeah, you spotted my small mistake, I missed the Not, but you missed Is
Nothing, which is why you got the error.
I don't see what 'better coding would handle than more efficiently',
although I can't deny there could be. I'm all ears.
Click to expand...

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then
Target.Offset(0, 2).Select
ElseIf Not Intersect(Target, Range("E5:E35")) Is Nothing Then
If Target.Value > 1 Then
'do the stuff I'm not posting here
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
My goals:
1) if entry is in C5:C35, move two columns over to E
2) if entry is in E5:E35, then process that entry
There are occasions when I enter in C or E or both
Click to expand...

They will handle independenetly.
 
D

David

Bob Phillips wrote
Yeah, you spotted my small mistake, I missed the Not, but you missed Is
Nothing, which is why you got the error.
Click to expand...

I mistakenly thought these both were the same, both triggering the move if
an entry was made in the range.

1)
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then _
Target.Offset(0, 2).Select

2)
If Intersect(Target, Range("C5:C35")) Then Target.Offset(0, 2).Select

But obviously the latter collides with the later evaluation of changing
E5:E35 for some reason.
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then
Target.Offset(0, 2).Select
ElseIf Not Intersect(Target, Range("E5:E35")) Is Nothing Then
If Target.Value > 1 Then
'do the stuff I'm not posting here
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
Click to expand...

Believe it or not, I was leaning in that direction, but failed to see how
more lines was more efficient than fewer lines. My VBA ignorance must be
showing.
 
Top