Write offset formula reference in VBA code

[Jyggalag]

Hi all,

I have this formula:

Essentially, when I fill in ANY data or make ANY changes to cell D2, i want the VBA to automatically update the current date/time in cell B1, so I can see the last time I made any update to cell D2.

I can do xOffsetColumn = -2

But this will show the date in cell B2, not B1.

Also, referencing the code to "B1" does not work, as you can see above.

Does anybody know how to fix my code so I can refer to cell B1?

Thank you!

 

[Takae]

xOffsetColumn=-2 ---you don't need to change this line.
Rng.offset(-1,xOffsetColumn) ---Three lines should be like this.

 

[6StringJazzer]

Throw away that code and use this.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect([D2], Target) Is Nothing Then
      Application.EnableEvents = False
      [B1] = Date
      Application.EnableEvents = True
   End If

End Sub

 

[Joe4]

Also note that is you declare xOffsetColumn as an Integer (like you did in your code), you cannot set it equal to "B1", as "B1" is text, NOT an integer!

 

[Jyggalag]

tri

xOffsetColumn=-2 ---you don't need to change this line.
Rng.offset(-1,xOffsetColumn) ---Three lines should be like this.

tried this didnt work, it gives an error for "Rng.offset(-1, xOffsetColumn)

 

[Jyggalag]

Throw away that code and use this.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect([D2], Target) Is Nothing Then
      Application.EnableEvents = False
      [B1] = Date
      Application.EnableEvents = True
   End If

End Sub

This is great and it works!

But it only shows the date, not the time, any way to fix this?

 

[Jyggalag]

Fix

This is great and it works!

But it only shows the date, not the time, any way to fix this?

Fixed! Just replace Date with Now

Thank you all btw! Shame my original code does not work haha

 

[6StringJazzer]

Fixed! Just replace Date with Now

Sorry I missed that! Nice one.