Employee LogBook

[Kmit37]

I am trying to make a excel based logbook for my employees so that I can get rid of paper based logs of daily output.

I want to have a button with a macro for each new log entry called "New Log Entry'

So far this is the VBA I have to auto fill date and time of entry

LogEntry Macro
'
'
Range("B6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("D7").Select
End Sub

My problem is that I need this macro to move to the next empty row and run =NOW() in colum B and C of that empty row respectively. This macro would then leave the cursor or active cell in row D of the empty row.

Thanks, any help would be greatly appreciated.

Kmit37

 

[texasalynn]

Welcome to MrExcel board...

try this

LogEntry Macro
Dim LR As Long
LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1
'
'
Range("B" & LR).FormulaR1C1 = "=NOW()"
Range("C" & LR).FormulaR1C1 = "=NOW()"
Range("D" & LR).Select
End Sub

 

[Smitty]

Welcome to the Board!

Instead of using NOW(), I'd use NOW, which will enter a static date/time, as NOW() will continue to update.

.Value = Now

HTH,

 

[texasalynn]

question - do you really want the date to be a formula? or would you just want the date the macro was executed. Since if you open the file on a different date, the cell value doesn't change.

Range("B" & LR).FormulaR1C1 = Date
Range("C" & LR).FormulaR1C1 = Date

 

[Kmit37]

Thanks guys,

Yes, the .value = Now works better and makes each log entry a unique punch as opposed to the now() which changed after each time the macro was run.

Thanks Again!

Kmit37