If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

"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

If .Column 7 And .Row 25 then go to B8
 

"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

If .Column 7 And .Row 25 then go to B8
 

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.

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

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?

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

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

If .Column 7 And .Row 25 then go to B8
 

On Friday, February 22, 2013 8:22:10 AM UTC-8, GS wrote:
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

Hmmmmm! The revised does the same thing.

Howard

If .Column 7 And .Row 25 then go to B8
 

Hi Howard,

Am Fri, 22 Feb 2013 08:38:59 -0800 (PST) schrieb Howard:

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. ??

the quotes are missing
try:
If Not Intersect(Target, Range("$B$8:$G$25")) Then Exit Sub
You can also try:
If Not Intersect(Target, Range("B8:G25")) Then Exit Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

If .Column 7 And .Row 25 then go to B8
 

Yes, I see that I also forgot to wrap the range address in double
quotes. My bad...

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

My apologies...

--
Garry

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

If .Column 7 And .Row 25 then go to B8
 

Howard formulated the question :
On Friday, February 22, 2013 8:22:10 AM UTC-8, GS wrote:
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

Hmmmmm! The revised does the same thing.

Howard

Yes, but with 1/4 the required processing! Your If construct requires
VBA to do 4 processes (1 for each condition) whereas the Intersect
function does the same thing in a single process. That makes using
Intersect() 300% more efficient in this context.

--
Garry

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

If .Column 7 And .Row 25 then go to B8
 

On Friday, February 22, 2013 8:56:42 AM UTC-8, GS wrote:
Howard formulated the question :

On Friday, February 22, 2013 8:22:10 AM UTC-8, GS wrote:

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



Hmmmmm! The revised does the same thing.



Howard



Yes, but with 1/4 the required processing! Your If construct requires

VBA to do 4 processes (1 for each condition) whereas the Intersect

function does the same thing in a single process. That makes using

Intersect() 300% more efficient in this context.



--

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 Garry and Claus,

I'm feeling like a real troublemake here.

I have tried all corrected versions the two of you offered. If I make entries outside the B8:G25 range I get the familiar "Run-time error 91
Object... Block variable not set" Code line is yellow highlighted up to but excluding Exit Sub.

On Error Resume Next seems to cure it, is it proper to do that?

(Maybe my Excel has the Flu, or maybe I do.)

Howard

If .Column 7 And .Row 25 then go to B8
 

Hi Howard,

Am Fri, 22 Feb 2013 09:17:04 -0800 (PST) schrieb Howard:

I have tried all corrected versions the two of you offered. If I make entries outside the B8:G25 range I get the familiar "Run-time error 91
Object... Block variable not set" Code line is yellow highlighted up to but excluding Exit Sub.

On Error Resume Next seems to cure it, is it proper to do that?

try:
If Intersect(Target, Range("B8:G25")) Is Nothing _
Then Exit Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

If .Column 7 And .Row 25 then go to B8
 

On Friday, February 22, 2013 9:23:07 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Fri, 22 Feb 2013 09:17:04 -0800 (PST) schrieb Howard:



I have tried all corrected versions the two of you offered. If I make entries outside the B8:G25 range I get the familiar "Run-time error 91

Object... Block variable not set" Code line is yellow highlighted up to but excluding Exit Sub.



On Error Resume Next seems to cure it, is it proper to do that?



try:

If Intersect(Target, Range("B8:G25")) Is Nothing _

Then Exit Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Great, works just fine now, without the on error statement.

You guy's and gal's are the best.

Thanks much.

Regards,
Howard

If .Column 7 And .Row 25 then go to B8
 

Howard expressed precisely :
Hi Garry and Claus,

I'm feeling like a real troublemake here.

I have tried all corrected versions the two of you offered. If I make
entries outside the B8:G25 range I get the familiar "Run-time error 91
Object... Block variable not set" Code line is yellow highlighted up to but
excluding Exit Sub.

On Error Resume Next seems to cure it, is it proper to do that?

(Maybe my Excel has the Flu, or maybe I do.)

I don't advise using an error escape. Claus' revision is the way to go,
and is what I should have posted if things around me at the time
weren't so hectic. (I was at the hospital receiving treAtment via IV
and kept getting interupted. No excuse, though! I apologize for all the
extra trouble!)

--
Garry

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

If .Column 7 And .Row 25 then go to B8
 

On Friday, February 22, 2013 10:55:36 AM UTC-8, GS wrote:
Howard expressed precisely :

Hi Garry and Claus,



I'm feeling like a real troublemake here.



I have tried all corrected versions the two of you offered. If I make

entries outside the B8:G25 range I get the familiar "Run-time error 91

Object... Block variable not set" Code line is yellow highlighted up to but

excluding Exit Sub.



On Error Resume Next seems to cure it, is it proper to do that?



(Maybe my Excel has the Flu, or maybe I do.)



I don't advise using an error escape. Claus' revision is the way to go,

and is what I should have posted if things around me at the time

weren't so hectic. (I was at the hospital receiving treAtment via IV

and kept getting interupted. No excuse, though! I apologize for all the

extra trouble!)



--

Garry



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

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

GS,

No need for appoligies, for sure. You have helped me often and I appreciate your patience and your good advice.

Got it working just fine.

I had it in the back of my mind that error escape should be used very sparingly, if at all.

A question on that very thing. Often when using the change event sub for some activity if I click, drag and highlight portions of the worksheet not included in the change event code, say to delete stuff I don't need I get an error about invalid entry or the such. On Error Resume Next seems to eliminat that, is that good practice??

Howard

If .Column 7 And .Row 25 then go to B8
 

Hi Howard,

Am Fri, 22 Feb 2013 12:14:47 -0800 (PST) schrieb Howard:

A question on that very thing. Often when using the change event sub for some activity if I click, drag and highlight portions of the worksheet not included in the change event code, say to delete stuff I don't need I get an error about invalid entry or the such. On Error Resume Next seems to eliminat that, is that good practice??

e.g. try instead:

If Intersect(Target, Range("B8:G25")) Is _
Nothing Or Target.Count 1 Then Exit Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

If .Column 7 And .Row 25 then go to B8
 

On Friday, February 22, 2013 12:21:11 PM UTC-8, Claus Busch wrote:
Hi Howard,



Am Fri, 22 Feb 2013 12:14:47 -0800 (PST) schrieb Howard:



A question on that very thing. Often when using the change event sub for some activity if I click, drag and highlight portions of the worksheet not included in the change event code, say to delete stuff I don't need I get an error about invalid entry or the such. On Error Resume Next seems to eliminat that, is that good practice??



e.g. try instead:



If Intersect(Target, Range("B8:G25")) Is _

Nothing Or Target.Count 1 Then Exit Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Well, that takes care of that problem!

Thanks for the continued excellent advice and guidance.
I am humbled.

Howard

If .Column 7 And .Row 25 then go to B8
 

Thanks for your kind words...

Again, Claus rescues the situation! In light of the added info about
how users may work with the data, Claus' recommendation is the better
way to go!

--
Garry

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