| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto return
I want to be able to go automate after entering data in the last cell in a
row to then go back to the beginning of the next line? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Put this in worksheet code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Hi
Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
But Roger, the OP specifically want the auto-return after data was entered in
the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Hi
Reading the OP's post literally, you are absolutely right. I had assumed (perhaps wrongly) that he had data stretching across maybe a dozen or so columns, and wanted to return to the beginning of the next line after entering the last of "his" row of data. Shows how we can all interpret a question quite differently <bg -- Regards Roger Govier "Gary''s Student" wrote in message ... But Roger, the OP specifically want the auto-return after data was entered in the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
O.K. then.... to auto-return from the last cell in the currently used range:
Private Sub Worksheet_Change(ByVal Target As Range) Set r = ActiveSheet.UsedRange nLastColumn = r.Columns.Count + r.Column - 1 If Target.Column < nLastColumn Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Reading the OP's post literally, you are absolutely right. I had assumed (perhaps wrongly) that he had data stretching across maybe a dozen or so columns, and wanted to return to the beginning of the next line after entering the last of "his" row of data. Shows how we can all interpret a question quite differently <bg -- Regards Roger Govier "Gary''s Student" wrote in message ... But Roger, the OP specifically want the auto-return after data was entered in the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Hi
Firstly, I don't know how to address you, even though I have read so many of your postings over the years. Is it Gary? Secondly, that is excellent and adjusts to the relevant last column without the user having to specify the column number. However, could you explain the use of +r.Column - 1 r.column returns 1, hence I don't see why this has been included. As a totally different alternative for the OP, and the way in which I usually enable users to quickly enter data into an area of the sheet with automatic dropping to the next row, is to set Scroll Area. This is a toggle which switches on or off the scroll area on Sheet1 Sub LockArea() If Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" Then Sheets("Sheet1").ScrollArea = "" Else Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" End If End Sub This sub has to be entered into a standard module of course, and activated by selecting the macro (or its shortcut) first. Whilst one needs to know in advance the area you are locking to, it has the advantage of not using any processing power during data entry looking at change events, and even if you do not enter data in the last column, pressing enter will take you to the beginning of the next row. -- Regards Roger Govier "Gary''s Student" wrote in message ... O.K. then.... to auto-return from the last cell in the currently used range: Private Sub Worksheet_Change(ByVal Target As Range) Set r = ActiveSheet.UsedRange nLastColumn = r.Columns.Count + r.Column - 1 If Target.Column < nLastColumn Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Reading the OP's post literally, you are absolutely right. I had assumed (perhaps wrongly) that he had data stretching across maybe a dozen or so columns, and wanted to return to the beginning of the next line after entering the last of "his" row of data. Shows how we can all interpret a question quite differently <bg -- Regards Roger Govier "Gary''s Student" wrote in message ... But Roger, the OP specifically want the auto-return after data was entered in the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Richo
Example ranges only.............. Select A1 then Tab across to J1. Hit ENTER key to move to A2 Alternative method. Unlock columns A:J then protect the sheet. Gord Dibben MS Excel MVP On Mon, 19 Feb 2007 03:43:42 -0800, Richo wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
You can certainly call me Gary. My username is dedicated to the person who
taught me Excel. I use it to always remind me to show the same patience to others as he showed to me. About r.Columns.Count + r.Column - 1 Consider a worksheet that is completely empty except for cells K1 and L1 r.Columns.Count returns a 2 - the width of the range r.Column returns 11 - where the range begins The sum of these two values is 13 - because we are counting column K twice. That's why we subtract one - we want the last used column, not the first column after the last used coumn. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Firstly, I don't know how to address you, even though I have read so many of your postings over the years. Is it Gary? Secondly, that is excellent and adjusts to the relevant last column without the user having to specify the column number. However, could you explain the use of +r.Column - 1 r.column returns 1, hence I don't see why this has been included. As a totally different alternative for the OP, and the way in which I usually enable users to quickly enter data into an area of the sheet with automatic dropping to the next row, is to set Scroll Area. This is a toggle which switches on or off the scroll area on Sheet1 Sub LockArea() If Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" Then Sheets("Sheet1").ScrollArea = "" Else Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" End If End Sub This sub has to be entered into a standard module of course, and activated by selecting the macro (or its shortcut) first. Whilst one needs to know in advance the area you are locking to, it has the advantage of not using any processing power during data entry looking at change events, and even if you do not enter data in the last column, pressing enter will take you to the beginning of the next row. -- Regards Roger Govier "Gary''s Student" wrote in message ... O.K. then.... to auto-return from the last cell in the currently used range: Private Sub Worksheet_Change(ByVal Target As Range) Set r = ActiveSheet.UsedRange nLastColumn = r.Columns.Count + r.Column - 1 If Target.Column < nLastColumn Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Reading the OP's post literally, you are absolutely right. I had assumed (perhaps wrongly) that he had data stretching across maybe a dozen or so columns, and wanted to return to the beginning of the next line after entering the last of "his" row of data. Shows how we can all interpret a question quite differently <bg -- Regards Roger Govier "Gary''s Student" wrote in message ... But Roger, the OP specifically want the auto-return after data was entered in the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto return
Hi Gary
Thank you. I can see that now. I had been testing with a range that ran from A to M, so columns.count = 13, and the r.columns.count - 1 wasn't changing the value of nlastcolumn. Yet again, my VBA knowledge has been enhanced. -- Regards Roger Govier "Gary''s Student" wrote in message ... You can certainly call me Gary. My username is dedicated to the person who taught me Excel. I use it to always remind me to show the same patience to others as he showed to me. About r.Columns.Count + r.Column - 1 Consider a worksheet that is completely empty except for cells K1 and L1 r.Columns.Count returns a 2 - the width of the range r.Column returns 11 - where the range begins The sum of these two values is 13 - because we are counting column K twice. That's why we subtract one - we want the last used column, not the first column after the last used coumn. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Firstly, I don't know how to address you, even though I have read so many of your postings over the years. Is it Gary? Secondly, that is excellent and adjusts to the relevant last column without the user having to specify the column number. However, could you explain the use of +r.Column - 1 r.column returns 1, hence I don't see why this has been included. As a totally different alternative for the OP, and the way in which I usually enable users to quickly enter data into an area of the sheet with automatic dropping to the next row, is to set Scroll Area. This is a toggle which switches on or off the scroll area on Sheet1 Sub LockArea() If Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" Then Sheets("Sheet1").ScrollArea = "" Else Sheets("Sheet1").ScrollArea = "$D$2:$M$1000" End If End Sub This sub has to be entered into a standard module of course, and activated by selecting the macro (or its shortcut) first. Whilst one needs to know in advance the area you are locking to, it has the advantage of not using any processing power during data entry looking at change events, and even if you do not enter data in the last column, pressing enter will take you to the beginning of the next row. -- Regards Roger Govier "Gary''s Student" wrote in message ... O.K. then.... to auto-return from the last cell in the currently used range: Private Sub Worksheet_Change(ByVal Target As Range) Set r = ActiveSheet.UsedRange nLastColumn = r.Columns.Count + r.Column - 1 If Target.Column < nLastColumn Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Reading the OP's post literally, you are absolutely right. I had assumed (perhaps wrongly) that he had data stretching across maybe a dozen or so columns, and wanted to return to the beginning of the next line after entering the last of "his" row of data. Shows how we can all interpret a question quite differently <bg -- Regards Roger Govier "Gary''s Student" wrote in message ... But Roger, the OP specifically want the auto-return after data was entered in the last cell in the row. Usually the last cell in a row is the cell in column 256 (column IV) I used Cells.Columns.Count in case the OP was using Excel 2007. -- Gary's Student gsnu200707 "Roger Govier" wrote: Hi Unless the user wants to use the whole row of 256 columns before jumping to the next line, wouldn't they need to insert a value equal to their last column number, rather than using Cells.Columns.Count If Target.Column < Cells.Columns.Count Then Cells.Columns.Count always returns 256 (in XL2003 and lower) -- Regards Roger Govier "Gary''s Student" wrote in message ... Put this in worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < Cells.Columns.Count Then Exit Sub Else i = Target.Row + 1 Cells(i, 1).Select End If End Sub REMEMBER worksheet code, not a standard module -- Gary's Student gsnu200707 "Richo" wrote: I want to be able to go automate after entering data in the last cell in a row to then go back to the beginning of the next line? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I set up an auto return to the next column in excell? | Excel Worksheet Functions | |||
| how can you auto return from columns B1 to A2 | Excel Discussion (Misc queries) | |||
| Auto return month as text date | Excel Discussion (Misc queries) | |||
| "Auto-return" | Excel Discussion (Misc queries) | |||
| Auto Return | Excel Discussion (Misc queries) | |||
Linear Mode
