Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
Hello,
I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
Something like this maybe...
Sub TimeStamp() Const TimeStampColumn As String = "A" Dim X As Long Dim LastUsedRow As Long With Worksheets("Sheet1") LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row .Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _ Value < ""), TimeStampColumn).Value = Now End With End Sub Rick "valiant" wrote in message ... Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
Hey Rick,
Thanks for the help, that is a great macro for getting the time and filling it in like a list. I really appreciate that. My only question remaining is, is there a way to add to the macro so that it takes the newly recieved time and changes it into a format that only displays the time and keeps the date seperate (or better yet, assigns the date to one column and the time to another)? Thanks again for the help. "Rick Rothstein (MVP - VB)" wrote: Something like this maybe... Sub TimeStamp() Const TimeStampColumn As String = "A" Dim X As Long Dim LastUsedRow As Long With Worksheets("Sheet1") LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row .Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _ Value < ""), TimeStampColumn).Value = Now End With End Sub Rick "valiant" wrote in message ... Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
This macro put the date in Column A and the time in Column B...
Sub DateTimeStamp() Const DateStampColumn As String = "A" Const TimeStampColumn As String = "B" Dim X As Long Dim LastUnusedRow As Long With Worksheets("Sheet1") LastUnusedRow = .Cells(Rows.Count, DateStampColumn).End(xlUp).Row If .Cells(LastUnusedRow, DateStampColumn).Value < "" Then LastUnusedRow = LastUnusedRow + 1 End If .Cells(LastUnusedRow, DateStampColumn).Value = Date .Cells(LastUnusedRow, TimeStampColumn).Value = Time End With End Sub Rick "valiant" wrote in message ... Hey Rick, Thanks for the help, that is a great macro for getting the time and filling it in like a list. I really appreciate that. My only question remaining is, is there a way to add to the macro so that it takes the newly recieved time and changes it into a format that only displays the time and keeps the date seperate (or better yet, assigns the date to one column and the time to another)? Thanks again for the help. "Rick Rothstein (MVP - VB)" wrote: Something like this maybe... Sub TimeStamp() Const TimeStampColumn As String = "A" Dim X As Long Dim LastUsedRow As Long With Worksheets("Sheet1") LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row .Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _ Value < ""), TimeStampColumn).Value = Now End With End Sub Rick "valiant" wrote in message ... Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
Hey Rick,
I gotta thank you. All of your suggestions have really answered all of my questions and I think I have a good grasp of this now. Thank you for all of your help and I will post if I have any more questions in the future. "Rick Rothstein (MVP - VB)" wrote: This macro put the date in Column A and the time in Column B... Sub DateTimeStamp() Const DateStampColumn As String = "A" Const TimeStampColumn As String = "B" Dim X As Long Dim LastUnusedRow As Long With Worksheets("Sheet1") LastUnusedRow = .Cells(Rows.Count, DateStampColumn).End(xlUp).Row If .Cells(LastUnusedRow, DateStampColumn).Value < "" Then LastUnusedRow = LastUnusedRow + 1 End If .Cells(LastUnusedRow, DateStampColumn).Value = Date .Cells(LastUnusedRow, TimeStampColumn).Value = Time End With End Sub Rick "valiant" wrote in message ... Hey Rick, Thanks for the help, that is a great macro for getting the time and filling it in like a list. I really appreciate that. My only question remaining is, is there a way to add to the macro so that it takes the newly recieved time and changes it into a format that only displays the time and keeps the date seperate (or better yet, assigns the date to one column and the time to another)? Thanks again for the help. "Rick Rothstein (MVP - VB)" wrote: Something like this maybe... Sub TimeStamp() Const TimeStampColumn As String = "A" Dim X As Long Dim LastUsedRow As Long With Worksheets("Sheet1") LastUsedRow = .Cells(Rows.Count, TimeStampColumn).End(xlUp).Row .Cells(LastUsedRow - (.Cells(LastUsedRow, TimeStampColumn). _ Value < ""), TimeStampColumn).Value = Now End With End Sub Rick "valiant" wrote in message ... Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
One last question I just came across during my attempts. I realized if I
rename the worksheet or try to get the macro to work with another worksheet, it doesn't work anymore and I get an error message. How can I make it so that this macro can be used with whatever the worksheet is named and potentially with multiple worksheets (So if I were to activate the macro in sheet 1, it would show the results in sheet 1, then I could activate it in sheet 2 without changing the macro and it would show the results in sheet 2)? Sorry for the barrage of questions, I'm kinda new to macros in Excel. Thanks for the help. "valiant" wrote: Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
In my last posted routine, change this line...
With Worksheets("Sheet1") to this... With ActiveSheet Rick "valiant" wrote in message ... One last question I just came across during my attempts. I realized if I rename the worksheet or try to get the macro to work with another worksheet, it doesn't work anymore and I get an error message. How can I make it so that this macro can be used with whatever the worksheet is named and potentially with multiple worksheets (So if I were to activate the macro in sheet 1, it would show the results in sheet 1, then I could activate it in sheet 2 without changing the macro and it would show the results in sheet 2)? Sorry for the barrage of questions, I'm kinda new to macros in Excel. Thanks for the help. "valiant" wrote: Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Time Clock" Functions
Thanks for all your help, Rick. you've answered all my questions for me and
it works great now. I really appreciate it. "Rick Rothstein (MVP - VB)" wrote: In my last posted routine, change this line... With Worksheets("Sheet1") to this... With ActiveSheet Rick "valiant" wrote in message ... One last question I just came across during my attempts. I realized if I rename the worksheet or try to get the macro to work with another worksheet, it doesn't work anymore and I get an error message. How can I make it so that this macro can be used with whatever the worksheet is named and potentially with multiple worksheets (So if I were to activate the macro in sheet 1, it would show the results in sheet 1, then I could activate it in sheet 2 without changing the macro and it would show the results in sheet 2)? Sorry for the barrage of questions, I'm kinda new to macros in Excel. Thanks for the help. "valiant" wrote: Hello, I'm looking to create a macro with similar function to that of a time clock used in work shops (one that when activated will copy the current time (preferably seperated from the date) into another cell for use with other equations). It should also fill it in like a list whenever the macro is activated (so it fills in, for example, cell A1 when the macro is first activated and the next time it is used it will fill in the current time in A2). Can anyone help me with this? I appreciate it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
can we convert "2 days 16 hrs" to " 64hrs" using excel functions | Excel Worksheet Functions | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming |