Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
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 can't figure out why the other rows won't 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
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 "Jon M." wrote in message ... 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 can't figure out why the other rows won't 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
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/sh...d.php?t=177855 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
Hi Jon,
You can't 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 "Jon M." wrote: 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 can't figure out why the other rows won't 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
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 "Jon M." wrote in message ... 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 can't figure out why the other rows won't 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Offsets
A Lifesaver, that's what you are. Thanks.
-- Jon M. "OssieMac" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offsets (I think) | Excel Discussion (Misc queries) | |||
Countif & Offsets | Excel Discussion (Misc queries) | |||
offsets - using ranges | Excel Programming | |||
Named range using offsets | Excel Programming | |||
average, array and offsets | Excel Worksheet Functions |