=Lastmodified()

[CurtJohn]

Hi,

First of all, thanks for the guy who created this macro, it almost works perfectly!
Here's the macro :

Public FunctioLastmodified(c As Range)

Lastmdified = Now()

End Function

THOUGH! I'm facing this problem :
Every time I use the filter in my table, ALL the dates are updated and I don't want this...


Can you guys please help me to fix this problem?

Thanks!

 

[Yongle]

The only way to do what you want is to put the value of NOW() in the cell
VBA Change_Event macro can be used to automate this

Here is an example which places timestamp in column C whenever a change is detected in column B

Place in sheet module (right-click on sheet tab \ View Code \ paste into code window which appears \ back to Excel with {ALT}{F11} )

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    Dim rng As Range: Set rng = Range("B2").Resize(Rows.Count - 1)
    If Not Intersect(rng, Target) Is Nothing Then Target.Offset(, 1) = Now
End Sub

Does not work if placed in a standard module

 

[CurtJohn]

Thanks for the reply but it doesn't work...
This file is my inventory file... I have 376 rows (it might change depending on how many items I have in my store)
On column M I have the quantity of the product, on column N I have the date(dd/mm/yy) the inventory was done.

With your macro, all the date are the sme and depends only on B2 (M2 for me), though I need 1 date per row (product).

 

[Joe4]

Please define the conditions under which the last modified value should be updated.

 

[Yongle]

as requested by @Joe4 we need to understand EXACTLY what you require

1. Changes in which cells should trigger the macro to run?
- your last post suggests that should be any cell in column M
2. When the macro is triggered which cell should take the value of NOW (presumably a cell in the same row, but different column)
- looks to me that you want the adjacent cell in column N

If so

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count > 1 Then Exit Sub
    Dim rng As Range: Set rng = Range("[COLOR=#ff0000]M2[/COLOR]").Resize(Rows.Count - 1)
    If Not Intersect(rng, Target) Is Nothing Then Target.Offset(, 1) = Now
End Sub

If this is not correct, please explain further

 

[Joe4]

1. Changes in which cells should trigger the macro to run?
- looks to me like it's any cells in column M ?

It is also crucial for us to understand how exactly this column (M or whatever else it may be) is being updated.
Is it being updated manually? Or, is it a formula whose value is changing?
If it is a formula, please post exactly what that formula looks like.

 

[CurtJohn]

Sorry,

Well for example, if I modify M2 value, I want N2 to automatically write the date of the modification of M2 eg : 13/06/2019.
I want the same for M3, M4, M5, and so on... if I change M35 -> N35 = 16/06/2019 for example, etc.
So obviously I will have different dates because I don't check the 300 items everyday...

The problem with the first macro is that the date were automatically changing to now if I used a filter to only display some materials. It also happens if I delete a complete row (line), and tha's a problem because every rows had the same date (now, current date) even if I didn't change anything but only delete a row...

 

[Joe4]

Replace the current Worksheet_Change procedure you have in your sheet module with this one:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
    Set rng = Intersect(Target, Columns("M:M"))
    
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell.Row > 1 Then cell.Offset(0, 1) = Now()
    Next cell

End Sub

Any manual change to a cell in column M, in row 2 or below will update column N in the same row with the current date/time (and it will be frozen in time there).

 

[Yongle]

My macro does not change any values except when the value in column M changes
- but your LastModified formula would behave in the way you describe

Remove all the formulas in column N

 

[Joe4]

My macro does not change any values except when the value in column M changes
- but your LastModified formula would behave in the way you describe

Remove all the formulas in column N

Yes, after analysis, Yongle's solution and mine do essentially the same thing, so either should work.
But if you still have references to your original formula, that would be problematic.