Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Search for a column based on the column header and then past data from it to another column in another workbook | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C | Excel Programming |