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_Chang e(ByVal Target As Range)



Application.Ena bleEvents = False
If (Target.Address = "$AF$7") Then
ActiveCell.Offs et(0, 7).Select
ActiveCell.Form ulaR1C1 = "today()"

Application.Ena bleEvents = 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,M OD(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(AV 6,1),AW6,LEFT(A V6,1)&" – "&NOW()))," ")

This in AX6 - gives the full timestamp

=IF(AW6<>"",VAL UE(RIGHT(AW6,LE N(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.