Automatic offset

[dkehler]

I am using excel 10 and have my cursor set to move to the right and would like to do the following:

When I make an entry into any line in column A and press enter, I want the target cell to be in column E on the same line.

Then when the target cell is G, I want it to move to K.

Then when I enter and the target is M, I want to go back to A on the next line. Thanks for any help.

 

[Smitty]

Hopefully I read that right:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A, G:G, M:M")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column<br>                <SPAN style="color:#00007F">Case</SPAN> 1 <SPAN style="color:#007F00">'    A</SPAN><br>                    Cells(Target.Row, "E").Select<br>                <SPAN style="color:#00007F">Case</SPAN> 7 <SPAN style="color:#007F00">'    G</SPAN><br>                    Cells(Target.Row, "K").Select<br>                <SPAN style="color:#00007F">Case</SPAN> 13 <SPAN style="color:#007F00">'   M</SPAN><br>                    Cells(Target.Row + 1, "A").Select<br>            <SPAN style="color:#00007F">End</SPAN> Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[RickXL]

I see Smitty and I are two minds but with a single thought!

I have a slightly different version of the same thing.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Select Case False
        Case Intersect(Range("A:A"), Target) Is Nothing ' If the target is A ...
            Range("E" & Target.Row).Select              ' ... go to E
        Case Intersect(Range("G:G"), Target) Is Nothing ' If the target is G ...
            Range("K" & Target.Row).Select              ' ... go to K
        Case Intersect(Range("M:M"), Target) Is Nothing ' If the target is M ...
            Range("A" & Target.Row + 1).Select          ' ... go to A on next row
    End Select
    
End Sub

 

[dkehler]

Hopefully I read that right:

Private Sub Worksheet_Change(ByVal Target As Range)
****'** Code goes in the Worksheet specific module
****Dim rng As Range
********'** Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
********Set rng = Target.Parent.Range("A:A, G:G, M:M")
************ '** Only look at single cell changes
************If Target.Count > 1 Then Exit Sub
************'** Only look at that range
************If Intersect(Target, rng) Is Nothing Then Exit Sub
************'** Action if Condition(s) are met (do your thing here...)
************Select Case Target.Column
****************Case 1 '****A
********************Cells(Target.Row, "E").Select
****************Case 7 '****G
********************Cells(Target.Row, "K").Select
****************Case 13 '** M
********************Cells(Target.Row + 1, "A").Select
************End Select
End Sub


HTH,[/QUOTE

Thanks, what parts of this do I paste into the code page on the worksheet? Dave

 

[Smitty]

Thanks, what parts of this do I paste into the code page on the worksheet?

Paste the whole thing (less the *** of course ).

 

[dkehler]

It works like I wanted. Thanks a bunch!! Dave K

 

[dkehler]

Thanks to RickXL and Smitty. I appreciate the help

 

[RickXL]

Thanks for the feedback.

I will try and beat Smitty next time

 

[dkehler]

RickXL. No problem with the feedback. I greatly appreciate the help. I ended up using the code from RickXL since it was a little easier to make changes to if I change the worksheet.