Excel - Multiple Offsets

Asked By Jon M.

09-Feb-10 04:26 PM

Hi all,
I have a worksheet with the following code listed below.  The idea is that
once a cell in a particular row is populated with an 11 digit claim# it will
jump to the first cell 2 columns over at the top of the particular table of
claim#'s.  I hope that makes sense.  Anyways the first Function works fine,
when I put an 11 digit claim# in row 11 and press enter the correct cell is
selected at the top of the next column I want to enter in.

However when I try to do the same in Rows 23, 35, and 47 nothing happens.
My code is the same for these functions so I cannot figure out why the other
rows will not function the same.  As always any help is always appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 11 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_2(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 23 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_3(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 35 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub

Private Sub Worksheet_Change_4(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 47 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
Else
Exit Sub
End If
End If
End Sub
--
Jon M.

09-Feb-10 04:51 PM

You are only allowed one Worksheet_Change procedure per sheet.  However, you
can organize the code to react to multiple conditions, as shown below.  Give
it a try and post back if there is a problem.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 11 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
ElseIf Target.Row = 23 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
ElseIf Target.Row = 35 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If         `
End If
End If
End Sub

09-Feb-10 05:13 PM

Hi Jon,

You cannot create additional events as you have done. You are restricted to
the ones you can create by selecting Worksheet from the dropdown at the top
of the VBA editor and then the events allowable are the ones you can see at
the dropdown to the right top. You have to then code based on identifying the
target as below.

Note I have included the test for length of the string with the IsEmpty test.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If IsEmpty(Target.Value) Or _
Len(Target) <> 11 Then Exit Sub

Select Case Target.Row
Case 11
Target.Offset(-10, 2).Select
Case 23
Target.Offset(-10, 2).Select
Case 35
Target.Offset(-10, 2).Select
Case 47
Target.Offset(-10, 2).Select
End Select
End Sub


--
Regards,

OssieMac

09-Feb-10 04:51 PM

You can have only one change function.  try this code it is very simple


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If not IsEmpty(Target.Value) Then
If Len(Target) = 11 Then
Select Target.Row
Case 11:
Range("B2").Select
Case 23:
Range("B12").Select
Case 35:
Range("B24").Select
end select
end if
end if
end if
exit sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=177855

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

09-Feb-10 05:17 PM

Missed part of of.  Use this one:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Row = 11 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
ElseIf Target.Row = 23 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
ElseIf Target.Row = 35 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
ElseIf Target.Row = 47 Then
If Len(Target) = 11 Then
ActiveCell.Offset(-10, 2).Select
End If
End If
End Sub

09-Feb-10 05:20 PM

Hi again Jon,

Having another look at the code I am not sure you need the following line.
If Target.Count > 1 Then Exit Sub

Also because the offset is the same for each Case you can code it like the
following.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If IsEmpty(Target.Value) Or _
Len(Target) <> 11 Then Exit Sub

Select Case Target.Row
Case 11, 23, 35, 47
Target.Offset(-10, 2).Select
End Select
End Sub

--
Regards,

OssieMac

12-Feb-10 12:54 PM

A Lifesaver, that is what you are.  Thanks.
--
Jon M.