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

H

Howard

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
 
J

joeu2004

Howard said:
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
Click to expand...

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

joeu2004

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

Alternatively:

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

Auric__

Howard said:
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
Click to expand...

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

Howard

Howard wrote:












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.
Click to expand...

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
 
B

Ben McClave

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
 
H

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
Click to expand...

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
 
A

Auric__

Howard said:
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!
Click to expand...

....and joeu2004.
 
G

GS

How about...
Option Explicit

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

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
Click to expand...

--
Garry

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

GS

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
 
H

Howard

How about...







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
Click to expand...

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
 
C

Claus Busch

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. ??
Click to expand...

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
 
G

GS

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
 
G

GS

Howard formulated the question :
Hmmmmm! The revised does the same thing.

Howard
Click to expand...

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
 
H

Howard

Howard formulated the question :




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
Click to expand...

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
 
C

Claus Busch

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?
Click to expand...

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


Regards
Claus Busch
 
H

Howard

Hi Howard,



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







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
Click to expand...

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

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

Thanks much.

Regards,
Howard
 
G

GS

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.)
Click to expand...

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
 
H

Howard

Howard expressed precisely :







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
Click to expand...

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 anerror about invalid entry or the such. On Error Resume Next seems to eliminat that, is that good practice??

Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Perplexing IF statement 4
Border formatting row of cells 22
Return last row no. of column with blanks at top 7
Concat a three column range into one cell Then Next... 2
Code to Concat a row in loop 2
How to do this with macro? 2
Advice on multiple macros, same code just a different column 9
Create if partial match then copy and paste data from one cell to another 1

Top