Hi experts, this second line of code is making me feel foolish.
The first line does its thing where data is entered and upon enter moves
to the right until column 8 then correctly act like a carriage
return back to column B and next row down.

Once I get past G25 I want to return to B8... what have I got screwed up in
the second line?

If ActiveCell.Column 7 Then ActiveCell.Offset(1, -6).Select
If ActiveCell.Column 7 And ActiveCell.Row 25 Then _
ActiveCell.Offset(-18, -6).Select

Thanks,
Howard

"Howard" wrote:
Once I get past G25 I want to return to B8... what have I got
screwed up in the second line?
If ActiveCell.Column 7 Then ActiveCell.Offset(1, -6).Select
If ActiveCell.Column 7 And ActiveCell.Row 25 Then _
ActiveCell.Offset(-18, -6).Select

If ActiveCell.Column 7 Then _
ActiveCell.Offset(IIf(ActiveCell.Row 25,-18,1), -6).Select

"joeu2004" wrote in message
...
"Howard" wrote:
Once I get past G25 I want to return to B8... what have I got
screwed up in the second line?
[....]
If ActiveCell.Column 7 Then _
ActiveCell.Offset(IIf(ActiveCell.Row 25,-18,1), -6).Select

Alternatively:

If ActiveCell.Column 7 Then _
If ActiveCell.Row 25 Then Range("B8").Select _
Else ActiveCell.Offset(1,-6).Select

Howard wrote:

Hi experts, this second line of code is making me feel foolish.
The first line does its thing where data is entered and upon enter moves
to the right until column 8 then correctly act like a carriage
return back to column B and next row down.

Once I get past G25 I want to return to B8... what have I got screwed up in
the second line?

If ActiveCell.Column 7 Then ActiveCell.Offset(1, -6).Select
If ActiveCell.Column 7 And ActiveCell.Row 25 Then _
ActiveCell.Offset(-18, -6).Select

Rather than moving around via offsets, just go straight to the cell you want:

If ActiveCell.Column 7 Then
If ActiveCell.Row 24 Then
Range("B8").Select
Else
Cells(ActiveCell.Row + 1, 2).Select
End If
Else
Cells(ActiveCell.Row, 8).Select
End If

Doing it this way is also somewhat clearer than your method, IMHO.

--
Noisy, opinionated, often wrong... but rarely uncertain.

On Thursday, February 21, 2013 11:54:48 PM UTC-8, Auric__ wrote:
Howard wrote:



Hi experts, this second line of code is making me feel foolish.

The first line does its thing where data is entered and upon enter moves

to the right until column 8 then correctly act like a carriage

return back to column B and next row down.



Once I get past G25 I want to return to B8... what have I got screwed up in

the second line?



If ActiveCell.Column 7 Then ActiveCell.Offset(1, -6).Select

If ActiveCell.Column 7 And ActiveCell.Row 25 Then _

ActiveCell.Offset(-18, -6).Select



Rather than moving around via offsets, just go straight to the cell you want:



If ActiveCell.Column 7 Then

If ActiveCell.Row 24 Then

Range("B8").Select

Else

Cells(ActiveCell.Row + 1, 2).Select

End If

Else

Cells(ActiveCell.Row, 8).Select

End If



Doing it this way is also somewhat clearer than your method, IMHO.



--

Noisy, opinionated, often wrong... but rarely uncertain.

Hi Auric_

Thanks for helping me out.
I tried all three suggestions and can't make it work. Here is the whole code and just to recap, this is what I'm trying to do.

Start in B8 and type in two characters and hit enter, code takes me to the next cell right and repeats until it gets to column 8, then does a carriage like return to column B next row down. When it gets to G25 and enter is hit should go back to B8.

Your codes sure look like that should happen but at G25 I get the carraige like return and if I continue to enter two characters and enter it just carriage returns on down column B. (out of range as the very first statement prompts a exit sub)

I'm puzzled to say the least, perhaps a look at the entire code will shed some light.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Active Range B8 to G25
If ActiveCell.Row < 8 Or ActiveCell.Row 25 Or _
ActiveCell.Column < 2 Or ActiveCell.Column 7 Then
Exit Sub
End If

Dim MyString, MyLen
MyLen = Target
MyString = Target.Value
MyLen = Len(MyString)

If MyLen = 2 Then
If ActiveCell.Column 7 Then
If ActiveCell.Row 24 Then
Range("B8").Select
Else
Cells(ActiveCell.Row + 1, 2).Select
End If
Else
Cells(ActiveCell.Row, 8).Select
End If
End If

End Sub

Thanks,
Howard

Howard,

Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Active Range B8 to G25
If Target.Row < 8 Or Target.Row 25 Or _
Target.Column < 2 Or Target.Column 7 Then
Exit Sub
End If

If Len(Target.Value) = 2 Then
Select Case Target.Column
Case 2 To 6
Target.Offset(0, 1).Select
Case 7
If Target.Row = 25 Then
Range("B8").Select
Else
Target.Offset(1, -5).Select
End If
End Select
End If

End Sub

On Friday, February 22, 2013 4:56:25 AM UTC-8, Ben McClave wrote:
Howard,



Try this:



Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

' Active Range B8 to G25

If Target.Row < 8 Or Target.Row 25 Or _

Target.Column < 2 Or Target.Column 7 Then

Exit Sub

End If



If Len(Target.Value) = 2 Then

Select Case Target.Column

Case 2 To 6

Target.Offset(0, 1).Select

Case 7

If Target.Row = 25 Then

Range("B8").Select

Else

Target.Offset(1, -5).Select

End If

End Select

End If



End Sub

Smack on, Ben. Why does it look so easy when you have the correct solution? <G
Thanks again, I appreciate it and Auric_'s efforts also!

Regards,
Howard

Howard wrote:

Smack on, Ben. Why does it look so easy when you have the correct
solution? <G
Thanks again, I appreciate it and Auric_'s efforts also!

....and joeu2004.

--
So tell me how did you lose yourself at sea?
So tell me why did you cast yourself away?

How about...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Active Range B8 to G25

If Not Intersect(Target, Range($B$8:$G$25) Then Exit Sub

If Len(Target.Value) = 2 Then
Select Case Target.Column
Case 2 To 6
Target.Offset(0, 1).Select
Case 7
If Target.Row = 25 Then
Range("B8").Select
Else
Target.Offset(1, -5).Select
End If
End Select
End If

End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops! ..missing closing parenthesis...

If Not Intersect(Target, Range($B$8:$G$25)) Then Exit Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, February 22, 2013 8:08:12 AM UTC-8, GS wrote:
How about...



Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

' Active Range B8 to G25



If Not Intersect(Target, Range($B$8:$G$25) Then Exit Sub



If Len(Target.Value) = 2 Then

Select Case Target.Column

Case 2 To 6

Target.Offset(0, 1).Select

Case 7

If Target.Row = 25 Then

Range("B8").Select

Else

Target.Offset(1, -5).Select

End If

End Select

End If



End Sub



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi GS,

Much cleaner than "If yada-yada End If".

However, it does error out with "Invalid Character" as soon as I pasted it in my code, entire line to red and refers to first $ sign. ??

Thanks,
Howard