date and time

[Daniel M]

I have a macro i need to run that fills in the date and time. For example I
have data in column A and i need to put the date and time in column B
everywhere there is data in column A. Date and time format needs to be
1/17/2008 6:30:00 PM.

In addition i need to add a 1 to column C when ever there is data in column A.

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

This macro should do what you want...

Sub ApplyDate()
Dim X As Long
Dim LastDataCell As Long
LastDataCell = Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastDataCell
If Len(Cells(X, 1)) > 0 And Len(Cells(X, 2)) = 0 Then
Cells(X, 2).Value = Now
Cells(X, 3).Value = 1
End If
Next
End Sub

Just one note on it... if there is already a date in Colum B, this macro
will leave it (and all other cells in the row) as is... it will only add
dates to that data not already having a date.

Rick

 

[Daniel M]

Rick Thanks! It did ALMOST everything i need.

I changed one line to
Cells(X, 2).Value = Format(Now(), "m/d/yyyy h:mm:ss AM/PM")
but i cannot seem to get it to display the seconds and am/pm?! If i click on
the cell the formula bar displays the ss and am/pm but not in the cells
itself. also if i right click and do format cells it shows custom m/d/yyyy
h:mm.

Now if i add this
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
before the rest of the macro, it works?!
Any idea why the format doesnt work properly? Thanks.


danielm.

 

[Rick Rothstein \(MVP - VB\)]

It is apparently a display issue with Excel. Select any of the cells in
Column B that had a date filled in by my code (before you added your
NumberFormat change)... if you look at the formula bar, you will see the
cell has the seconds in it... it just seems that Excel doesn't display them
by default. You can also see this if you manually enter in a date and time
(with seconds)... the seconds are not displayed unless you Custom Format the
cell(s) to show them.

Rick