CODEWITHSUNDEEP
vbaexcelexcel-formulatimestamp
nmergulh
nmergulh

Reputation: 39

Time stamp cell based on value of a different cell changing from a formula

I'm abit new to vba and all the solutions I have looked at focus on changing a value not a formula.

I have a table called "table1" on sheet2. When column R called changes it should time stamp the Date in column H

The formula is based on concatenation of other columns. Example: Cell A2 would be Dog and B2 Cat. R2 Would show Dog Cat. If I change B2 or A2 I want the date stamp to be in H2.

Please help me :)

Upvotes: 0

Views: 128

Answers (2)

nmergulh
nmergulh

Reputation: 39

I found this which helps:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Const DateStampColumn As Long = 8    'Date stamp column number
For Each r In Target.Rows
    For Each c In r.Cells
        If Not IsEmpty(c) Then
            Application.EnableEvents = False
            Cells(r.Row, DateStampColumn).Value = Date
            Application.EnableEvents = True
            Exit For
        End If
    Next c, r 
End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

You could restrict changes to the table only, like so. No need to separately loop through the rows and columns.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, r1 As Range
Const DateStampColumn As Long = 8    'Date stamp column number

Set r1 = Intersect(Target, Sheet1.ListObjects("Table6").DataBodyRange)

If Not r1 Is Nothing Then
    For Each r In r1
        If Not IsEmpty(r) Then
            Application.EnableEvents = False
            Cells(r.Row, DateStampColumn).Value = Date
            Application.EnableEvents = True
            Exit For
        End If
    Next r
End If

End Sub

Upvotes: 0

Related Questions