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.

 

[psycoperl]

Ok. Thank you. I work primarily in Access when doing VBA so this has been a learning experience.

I am playing with two solutions to my problem and this is looking like a viable solution.

 

[Joe4]

I am playing with two solutions to my problem and this is looking like a viable solution.

What is the other solution you are playing around with?

 

[psycoperl]

It is an access based solution. that records a "created" date when we do more of the processing as the Excel spreadsheet gets migrated to Access for other processing.

 

[Joe4]

So fellow MVP Rick Rothstein showed me how to avoid doing the unnecessary extra loop. Just one minor change (highlighted in red):

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[COLOR=#ff0000].Areas(1)[/COLOR].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

So this should be more efficient, and run a little faster.