VBA Code to Fill in Date Automatically

[Xirom431]

Hi. Thanks in advance for the help. I need help with fill in date automatically. If the Cell value in K6:K27 is > than 0, fill In the Date automatically and lock it in the adjacent column L.

 

[Joe4]

I am assuming that one column L is filled with a date, that you do not further runs of this VBA code to override an existing date.

Here is the code I came up with:

Sub MyDateFill()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Loop through range K6:K27
    For Each cell In Range("K6:K27")
'       Check to see that col K > 0 and col L is empty
        If (cell > 0) And (cell.Offset(0, 1) = "") Then
'           Put current date in column L
            cell.Offset(0, 1) = Date
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Xirom431]

How about if the K6 cell already has 0 and I change it to 1. I am sorry that I did not make myself clear. Mainly I want it to fill in the date when I change from 0 to any number.

 

[Joe4]

How about if the K6 cell already has 0 and I change it to 1. I am sorry that I did not make myself clear. Mainly I want it to fill in the date when I change from 0 to any number.

Yes, that is a bit different. We can make automated code that runs automatiically upon data being manually entered into certain cells (like your range K6:K27). But that code does not know the previous value of the value you just updated (I think there is a way to do that, but it gets a bit tricky, and I would need to see if I can look it up and find it).

Will the rows with 0 in column K always be blank in column L?
If so, then I think we can avoid that problem, and just say if the value in K is updated to something other than 0, and if column L is blank.

 

[Joe4]

If the assumptions in my previous post are correct, here is some automated code that will do that.

This must be placed in the proper sheet module. One way to ensure that happens is to go to the worksheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   See if any cells updated in range K6:K27
    Set rng = Intersect(Target, Range("K6:K27"))
    
'   Exit sub if no cells updated in our watched range
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells just updated in our range
    For Each cell In rng
'       Check to see if value is greater than 0 and column L blank
        If (cell > 0) And (cell.Offset(0, 1) = "") Then
'           Update column L with current time
            cell.Offset(0, 1) = Date
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[abdelfattah]

hi, joe i would ask about function offset my knowledge is simple about it but as i see many codes if you move from right to left should the value -1 not 1 as you case if use 1 it moves from left to right should in col j not l but the mystery when you use 1 it moves to col j actually i tested your code but i don't understand about function offset may you explain or correct me how is it

 

[Joe4]

I just figured out how to compare it to the previous value. We have to set a global value, and also use a Worksheet_SelectionChange event procedure to capture the cells value as we enter the cell.

So try this:

'Declare Global Variable
Dim prev As Variant


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   Get value of cell upon first entering it
    prev = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit sub if no cells updated in our watched range
    If Intersect(Target, Range("K6:K27")) Is Nothing Then Exit Sub
    
'   Check to see if previous value is zero, new value is greater than 0, and column L blank
    If (prev = 0) And (Target > 0) And (Target.Offset(0, 1) = "") Then
'       Update column L with current time
        Target.Offset(0, 1) = Date
    End If
    
End Sub

 

[Joe4]

hi, joe i would ask about function offset my knowledge is simple about it but as i see many codes if you move from right to left should the value -1 not 1 as you case if use 1 it moves from left to right should in col j not l but the mystery when you use 1 it moves to col j actually i tested your code but i don't understand about function offset may you explain or correct me how is it

I think you misread the question. There is no mention of column J, just columns K and L.
They are checking column K, and then then populating column L. That is moving from left-to-right, which is a positive number in the Offset function.

 

[abdelfattah]

I think you misread the question. There is no mention of column J, just columns K and L.

no i don't misread the question i know your code works for col k,l when i mentioned col j as what i know i was wanting for you inform me the right way how the offset works so if i would move from col k to j or i it supposes like this (0,-1) or (0,-2)?

 

[Xirom431]

Hi Joe, the code works great. Can it clear the date when the user enters 0 after realizing the info was entered in the wrong cell?