Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automatically move down to the next row
I have a macro that automatically inserts the time and date in certain
columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
#2
|
|||
|
|||
You know the row in which you are adding the time/date. Just add 1 to it and
select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" wrote in message ... I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
#3
|
|||
|
|||
The macro is as follows:-
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub "Mangesh" wrote: You know the row in which you are adding the time/date. Just add 1 to it and select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" wrote in message ... I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
#4
|
|||
|
|||
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With Cells(.Row+1,1).Activate End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub -- HTH Bob Phillips "Pank Mehta" wrote in message ... The macro is as follows:- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub "Mangesh" wrote: You know the row in which you are adding the time/date. Just add 1 to it and select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" wrote in message ... I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
#5
|
|||
|
|||
Add the line:
Cells(Target.Row + 1, 1).Select Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With Cells(Target.Row + 1, 1).Select ' new line added End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub - Mangesh "Pank Mehta" wrote in message ... The macro is as follows:- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub "Mangesh" wrote: You know the row in which you are adding the time/date. Just add 1 to it and select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" wrote in message ... I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
#6
|
|||
|
|||
Mangesh, Bob,
Many thanks for the prompt response and the solution. "Mangesh" wrote: Add the line: Cells(Target.Row + 1, 1).Select Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With Cells(Target.Row + 1, 1).Select ' new line added End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub - Mangesh "Pank Mehta" wrote in message ... The macro is as follows:- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Column = 8 Then Me.Unprotect With .Offset(0, 1) .Value = Date .NumberFormat = "dd mmm yy" End With With .Offset(0, 2) .Value = Now .NumberFormat = "hh:mm" End With End If End With ws_exit: Application.EnableEvents = True Me.Protect End Sub "Mangesh" wrote: You know the row in which you are adding the time/date. Just add 1 to it and select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" wrote in message ... I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
Formula referencing deleted row (move up automatically) | Excel Worksheet Functions | |||
Timeline: where it will match up data automatically | Excel Discussion (Misc queries) | |||
command button in excel will move when print. | Excel Discussion (Misc queries) | |||
After scanning a barcode how can I automatically move to the next. | Excel Discussion (Misc queries) |