VBA help to update date & time in a cell based on another cell value

[Gaurangg]

Hi Folks,

I have a vba code which is working fine in my one of the file. In this file, I have applied a rule that whenever in column A and column U data will be entered, current date and time will be captured in another two columns. However there are validation applied in that file from where data to be selected.

I have another file in which data will be filled into column S to V and in column X I have inserted Countif formula that if any one cell from S to V is filled, it will return with Yes else cell in X column will be blank. Now I want is whenever the value in x column turns into "Yes" from blank, date and time should be captured in column "AA"

Kindly help me with required changes into my current code. I have tried myself but I failed to do it.

My current code :

Private Sub Worksheet_Change(ByVal Target as Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.Activesheet.Range("X:X"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
      Application.EnableEvents = False
      For Each Rng in WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
        Rng.Offset(0, xOffsetColumn).Value = Now
Else
       Rng.Offset(0, xOffsetColumn).ClearContents
      Next
      Application.EnableEvents = True
End If
End Sub

 

[Gaurangg]

Hello Friends,

Can anyone help on this?

 

[Joe4]

The issue is that Worksheet_Change only captures manually changes, not changes in formulas.
Since you have formulas in column X, changes to those values are not picked up by Worksheet_Change.

You would either:
- Need to use a Worksheet_Calculate event. However, that one cannot identify where the change took place (there is not "Target" argument), just the fact that a calculation on the sheet ran.
- Check for changes in the columns that are feeding your formula (S-V).

Perhaps do this:
- Check for a change in S-V
- See if column X is "Yes"
- If column "AA" is blank, then populate it with the date/time

Also, your Offset is incorrect. Column AA is 3 columns to the right of column X, not 2.

 

[Gaurangg]

Hi Joe,


Thanks for your time to repond. I have tried your suggestion but unfortunately didn't work. Let me explain again for better clarity in the concern. I have columns from S to V. I want to capture the start time and end time. Means, whenever any first details filled into any columns of S - V should be captured Start Time and any last details captured in S-V, end time to be captured. To calculate it, I have entered the formula in Column X IF(COUNTBLANK($S2:$V2)<4,"Yes",0) which will turn into "Yes" whenever first detail will be entered. And in column Y I have put IF(OR(COUNTBLANK($S2:$V2)=0,COUNTBLANK($S2:$V2)=1),"Yes",0), which will turn into "Yes" when last detail will be filled into any column of S - V. Hence I want to capture start time when X column cell turns into "Yes" and capture the time when Y column turns into "Yes"


I have made the changes as per your suggestion. I would request you to help with coding line with changes where required. I am sorry but I have learnt it by self and hence don't know much fucntionalities of vba

Private Sub Worksheet_Calculate(ByVal Target As Range)
Dim WorkRng, WorkRng1 As Range
Dim Rng, Rng1 As Range
Dim xOffsetColumn, xOffsetColumn1 As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("X:X"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        'If Not VBA.IsEmpty(Rng.Value) Then
        If Rng.Value = "Yes" Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("Y:Y"), Target)
xOffsetColumn1 = 2
If Not WorkRng1 Is Nothing Then
    Application.EnableEvents = False
    For Each Rng1 In WorkRng1
        'If Not VBA.IsEmpty(Rng1.Value) Then
        If Rng1.Value = "Yes" Then
            Rng1.Offset(0, xOffsetColumn1).Value = Now
            Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng1.Offset(0, xOffsetColumn1).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[Joe4]

Assuming the first date is in column AA, and the second in AB, I think I would choose to approach it like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WorkRng As Range
    Dim Rng As Range
    Dim r As Long

    Set WorkRng = Intersect(Range("S:V"), Target)
    
'   Exit sub if update not made in columns S-V
    If WorkRng Is Nothing Then Exit Sub
    
'   Loop through all cells just updated
    For Each Rng In WorkRng
'       Get row number of cell
        r = Rng.Row
'       If column X is "Yes" and column AA does not have a date, add date to AA
        If Cells(r, "X") = "Yes" And Cells(r, "AA") = 0 Then Cells(r, "AA") = Now()
'       If column Y is "Yes" and column AB does not have a date, add date to AB
        If Cells(r, "Y") = "Yes" And Cells(r, "AB") = 0 Then Cells(r, "AB") = Now()
    Next Rng
    
End Sub

 

[Gaurangg]

its working. Amazing . You made the code so short and simple. I will learn this how it works. Thanks a lot Joe.

 

[Joe4]

You are welcome. Let me know if you have any questions.

The only caveat is that once the date/times are added, they won't be removed or updated, regardless of what you do to the values in S-V.
If that needs to happen, we may be able to amend the code, depending on what exactly the logic is.