| 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
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? |
| 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) | |||
Hybrid Mode
