Add today's date to another cell after cell value changes ExcelVBA

[grego9]

I am trying to write a procedure so that when data in row AF of Excel is amended then the date the amendment took place is entered into column AM. I thought I would start by trying to get it to work for one cell rather than a range and did the following:


Private Sub Worksheet_Change(ByVal Target As Range)



Application.EnableEvents = False
If (Target.Address = "$AF$7") Then
ActiveCell.Offset(0, 7).Select
ActiveCell.FormulaR1C1 = "today()"

Application.EnableEvents = True

End Sub

This does not error - but it doesn't post the date (or in fact anything!)

Please help!

[Killer42]

The first thing I'd suggest is to put a breakpoint on your If statement and display the value of Target.Address to make sure it matches what you expect.

Secondly, I suspect you may be setting the formula incorrectly. Pretty sure it needs an "=" at the start.

Hm... actually, I'd fix the formula first. Then chase up the address if it still doesn't work.

[grego9]

I ended up sussing this one without using VBA
The values in column AF were the ones that were changing and the ones I wanted to timestamp. I put this formula in AU6.

=IF(ISBLANK(AF6),"",IF(AU6="",NOW(),AU6))

I put this in AV6 (gives the iteration number and the data held):

=IF(AT6<>"",IF(AV6="",0&" - "&AT6,IF(RIGHT(AV6,LEN(AV6)-4)=""&AT6,AV6,MOD(LEFT(AV6,1)+1,10)&" – "&AT6)),"")

This in AW6 (gives the date the event happened
=IF(AT6<>"",IF(AW6="",0&" – "&NOW(),IF(LEFT(AW6,1)=LEFT(AV6,1),AW6,LEFT(AV6,1) &" – "&NOW())),"")

This in AX6 - gives the full timestamp

=IF(AW6<>"",VALUE(RIGHT(AW6,LEN(AW6)-4)),"")


Hope this saves a few hours for some other users!

[Killer42]

Glad to hear you got it sorted.

And thanks for posting your solution here for posterity. As you said, it could save other people hours trying to figure it out.