Forum Discussion
How to track when values change between columns?
Hello Everyone.
If I have a table that looks like this:
| Requirement | UI Designed | Number of Days | UX Designed | Number of Days | Tested | Number of Days | Released | Number of Days |
| Release user manual | 1 | 1 | 1 | 1 | ||||
| Software UX | 1 | 1 | 0 | 0 | ||||
| Software UI | 1 | 0 | 0 | 0 | ||||
| Test software | 1 | 1 | 1 | 0 |
Where '0' means not done yet and '1' means task is done.
How can I track how many days, say 'Software UX' has 1 for UX Designed and then a '1' goes in to 'Tested'? and then the 'Number of Days' next to 'UX Designed' column gets a number..
This will allow me to get a rough estimate of how many days each requirement remains in each status.
So, for example:
If 'Software UI' has a 1 in UI Designed on 26th May 2019 (according to system date), and then, on 30th May 2019, I add a 1 in the UX Designed column, then I expect the 'Number of Days' column next to UI Designed to have the value of 4.
Because it is 4 days past since 26th May and 30th May.
I hope that makes sense and thanks for any help.
- Hi ianwuk, Put the code behind Sheet1. Then change any cells in your sheet1. All changes will be written to sheet2. regards Bernd
4 Replies
- Hi ianwuk, Put the code behind Sheet1. Then change any cells in your sheet1. All changes will be written to sheet2. regards Bernd
- Thanks very much again, Bernd. I will give it a try!
Hi, one idea: Documentate changes automatically with help of Worksheet_change event.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long
With Sheet2
lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lngRow , 1).Value = Date
.Cells(lngRow , 2).Value = Time
.Cells(lngRow , 3).Value = Target.Address
.Cells(lngRow , 4).Value = Target.Value
.Cells(lngRow , 5).Value = Environ("username")
.Cells(lngRow , 6).Value = Environ("computername")
End With
End SubRegards from germany
Bernd
http://www.vba-Tanker.com
- Thanks Bernd for taking the time to reply.
I'm not really familiar with VBA. I assume I just add the above code to the sheet? What would the end result be running this code?
Thanks.
