Todays Date VBA for column

I need for every column that has "Date" as a column header, the cells to populate today's date when the adjacent 3 columns are filled in (Weekday, Logged in, Status).

This will be worked on daily, so I don't want the previous days date to change to today's - is this possible through vba?

Originally Posted by xladept

Try this in your sheet 1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim H As String, r As Long, c As Long, n As Long
c = Target.Column: r = Target.Row: H = Cells(3, c)
If InStr(1, H, "day") Then n = -1
If InStr(1, H, "gg") Then n = -2
If InStr(1, H, "tat") Then n = -3
Application.EnableEvents = False
If Cells(r, c + n) = "" Then Cells(r, c + n) = Date
Application.EnableEvents = True
End Sub

Wow, this is amazing. Is it possible to tweak it slightly and update with today's date only if "Logged in" and "Status" columns are filled in?

I would like to have a shift under the weekday already inputted in advance, so I don't want the date to populate if the weekday column has data...

Originally Posted by xladept

Sure Here:

Sub Manny(): Dim H As String, r As Long, c As Long, n As Long, T As Range

Set T = ActiveCell: c = T.column: r = T.Row: H = Cells(3, c)
'If InStr(1, H, "day") Then n = -1
If InStr(1, H, "gg") Then n = -2
If InStr(1, H, "tat") Then n = -3
If Cells(r, c + n) = "" Then Cells(r, c + n) = Date
End Sub

And, thanks for the rep!

Welcome!

That isn't populating any dates when I enter data into "logged in" and "status" - Am i doing something wrong?

Originally Posted by xladept

On second thought, since it's event code and nothing happens run this code:

Sub Enable()
Application.EnableEvents=True
End Sub

I don't know what it is, but placing the script you wrote into vba doesn't populate today's date under the "Date" column when something is entered in "logged in" and "status" - Are you able to attach a working sample?

Originally Posted by bakerman2

Give this a try.

That works great. Rep added.


- Are you able to help with this formula I am trying to get right? I want the Time Card tab columns Due in, Login, Status to display the due in, login and status from the Late tab - By agent name and By Date. This is so I can apply it into a Pivot table

Originally Posted by xladept

Hi manny,

Here's the working sample:

I entered the same script, but it didn't work for me for whatever reason, that's great Xladept.

Are you, bakerman, or anyone else reading able to help with the 10th post in the thread RE: Timecard ? I know it's not vba, more just a formula I can't seem to get

Originally Posted by bakerman2

Manny, I would strongly advice to re-design your late Sheet into a 7 column data sheet like Name|Team manager|Day|Date|Due|Logged|Status.
This way you open up a lot more possibilities like AutoFilter, AdvancedFilter and you could get your PivotTable Data straight from your Late Sheet.
Thatway you wouldn't have to resort to an in between solution like you are trying now.

Thanks Bakerman, I realized this (and kept the vba sample in my back pocket). The Timecard attachment I posted includes the idea you pitched, but I am having difficulty with the formula pulling Due|Login|Status from the Late tab according to the agent name and the date