Forum Discussion
Solved
VBA coding help
Hey guys! I need some help!
So my company has a "Master Project List" spreadsheet that summarizes the important information of all projects into one place.
I currently have a VBA code that I found on the internet that will update the date in column N if the cell in column M is updated. The problem that I am having is that every time one cell is updated, every cell in column N will update as well; I only want the the one cell to update. (i.e., if I change the number in cell M103, only the date in cell N103 should update; all other cells in column N should remain the same.)
Can someone either give me a new VBA code or help me fix my current code?
Code:
Public Function Lastmodified(c As Range)
Lastmodified = Now()
End Function
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L1")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End Sub
how it should look (different dates in the last column):
how it looks after updating (only the date in the last row should change):
I've added the worksheet change event to your worksheet "Master Project List" for entries in range M6:M200. If i enter a value in any of these cells the current date and time is entered in the adjacent cell of column N.
One possible solution is to change the line
Target.Offset(1, 0) = Now
to
Target.Offset(0, 1) = Now
This will update the cell in column N that corresponds to the changed cell in column M, instead of the cell below it.
Another possible solution is to use the Worksheet_Calculate event instead of the Worksheet_Change event. This will update the cells in column N only when the values in column M are recalculated.
You can use a formula like this in column N:
=IF(M2<>"",Lastmodified(M2),"")
This will call the Lastmodified function only when the cell in column M is not empty.
- I tried this but it says "Compile error: Procedure declaration does not match description of event or procedure have the same name"
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngBereich As Range Set rngBereich = Range("M100:M200") If Target.Cells.Count > 1 Then GoTo done If Not Application.Intersect(Target, rngBereich) Is Nothing Then If Target.Value <> "" Then Target.Offset(0, 1).Value = Now Else Target.Offset(0, 1).Value = "" End If End If done: Application.EnableEvents = True Exit Sub End SubYou can try this code. In the attached file you can enter data in a cell in range M100:M200 and the adjacent cell in column N returns the current date and time.
- This code still changes all cells in column N
The code returns the expected result in my file. Can you attach your file without sensitive data?
