Populating fields based on action

[psycoperl]

Hello,

I am trying to create a "DateAdded" field to my spreadsheet. What I would like to be able to happen is that once values are populated in columns A,B,C,D,E and H of a row to have the current date/time in Column BV


Is this possible? I am suspecting that it would involve VBA but not sure. I have experience with VBA in Access, but not excel.

 

[gvillepa]

here's a basic starter formula to build off of:

=IF(A1="","",TODAY())

or you could do AND or OR

This would insert today's date obviously. You can do DATE(manually enter date here or reference another cell to lookup)

 

[psycoperl]

Would this not update every time I open the file? The reason I ask is that I need to be able to sort and search on when it was created.

 

[Joe4]

here's a basic starter formula to build off of:

=IF(A1="","",TODAY())

or you could do AND or OR

This would insert today's date obviously. You can do DATE(manually enter date here or reference another cell to lookup)

I believe that they are looking for a Date stamp. If so, that solution will not work, as the date is not frozen in time. It will ALWAYS display the current date. So if they open it tomorrow, the date showing there will change to the new date.

If you want a date stamp that is frozen in time, VBA is required. To do this, 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 tCol As Long
    Dim tRow As Long
    Dim rng1 As Range
    Dim rng2 As Range

'   Check to see only one cell updates
    If Target.Count > 1 Then Exit Sub
    
'   Check to see if column updated in range from A-E or H
    tCol = Target.Column
    tRow = Target.Row
    If (tCol <= 5) Or (tCol = 8) Then
'       Check to see if all columns A-E and H are populated
        Set rng1 = Range(Cells(tRow, "A"), Cells(tRow, "E"))
        Set rng2 = Cells(tRow, "H")
        If WorksheetFunction.CountBlank(rng1) + WorksheetFunction.CountBlank(rng2) = 0 Then
            Cells(tRow, "BV") = Now
        End If
    End If
    
End Sub

This will run automatically, once all columns A-E and H have been populated.

 

[psycoperl]

Hi Joe -- SpotOn Thanks

Just two questions, as we sometimes copy and paste from a "prep file" how could I account for that as well... I tried copy and pasting values and it did not populate the field, however if I edited the field it populated.

Would there be a way to prevent it from updating the date stamp that is already populated? I.E. we update one of the values on line 301 as there was a typo, the timestamp should remain the original populated time.

I believe that they are looking for a Date stamp. If so, that solution will not work, as the date is not frozen in time. It will ALWAYS display the current date. So if they open it tomorrow, the date showing there will change to the new date.

If you want a date stamp that is frozen in time, VBA is required. To do this, 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 tCol As Long
    Dim tRow As Long
    Dim rng1 As Range
    Dim rng2 As Range

'   Check to see only one cell updates
    If Target.Count > 1 Then Exit Sub
    
'   Check to see if column updated in range from A-E or H
    tCol = Target.Column
    tRow = Target.Row
    If (tCol <= 5) Or (tCol = 8) Then
'       Check to see if all columns A-E and H are populated
        Set rng1 = Range(Cells(tRow, "A"), Cells(tRow, "E"))
        Set rng2 = Cells(tRow, "H")
        If WorksheetFunction.CountBlank(rng1) + WorksheetFunction.CountBlank(rng2) = 0 Then
            Cells(tRow, "BV") = Now
        End If
    End If
    
End Sub

This will run automatically, once all columns A-E and H have been populated.

 

[Joe4]

Would there be a way to prevent it from updating the date stamp that is already populated? I.E. we update one of the values on line 301 as there was a typo, the timestamp should remain the original populated time.

Change this row:

            Cells(tRow, "BV") = Now

to

            If Cells(tRow, "BV") = "" Then Cells(tRow, "BV") = Now

Just two questions, as we sometimes copy and paste from a "prep file" how could I account for that as well... I tried copy and pasting values and it did not populate the field, however if I edited the field it populated.

This can get a bit tricky. Are you copying and pasting just one row at a time, or might you be copying/pasting multiple rows at once?

 

[psycoperl]

Change this row:

            Cells(tRow, "BV") = Now

to

            If Cells(tRow, "BV") = "" Then Cells(tRow, "BV") = Now

This can get a bit tricky. Are you copying and pasting just one row at a time, or might you be copying/pasting multiple rows at once?

We can copy and paste over 1000 at a time.

 

[Joe4]

OK, this may not be the most efficient, as it unnecessarily goes through the loop an extra time because of the gap in your range, but it does seem to work nevertheless.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim isect As Range
    Dim r As Range
    Dim myRow As Long

'   Set columns to look in
    Set rng1 = Application.Union(Range("A:E"), Range("H:H"))
    
'   Check to see what cells being updated insersect with myRange
    Set isect = Intersect(Target, rng1)
    
'   Exit if no cells being updated fall in range
    If isect Is Nothing Then Exit Sub

    Application.ScreenUpdating = False
    
'   Loop through all rows in intersted range
    For Each r In isect.Rows
        myRow = r.row
        Set rng2 = Range("A" & myRow & ":E" & myRow)
        Set rng3 = Range("H" & myRow)
        If WorksheetFunction.CountBlank(rng2) + WorksheetFunction.CountBlank(rng3) = 0 Then
            If Cells(myRow, "BV") = "" Then Cells(myRow, "BV") = Now
        End If
    Next r

    Application.ScreenUpdating = True
    
End Sub

 

[psycoperl]

Would it be more efficient if we just went with columns A:H?
When I was working on my conditions, I was trying to check the minimum number of columns - column G may not be populated (as its for Middle Name) but all the others would.

 

[Joe4]

The code should work just fine, as-is. From a technical standpoint, it could run more efficiently behind the scenes. But I don't know that you will notice much difference.
Just try running a test, copying 1000 rows and see how long it takes. If it is not long, I wouldn't worry about that.

(Really, that comment is more for any other MVPs/experts who review it and ask "do you know that you are looping through the rows twice"? I just kind of beat them to the punch there!
It is still more efficient then looping through every cell in the range).