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