Previous Topic Next Topic

Excel HELP

Solved/Closed
CQB - Jan 26, 2012 at 05:04 PM
 CQB - Jan 30, 2012 at 09:47 AM
Hello,

If possible, can you help me in coding this:

Collumn J will have the drop down menu of Open, In Progress, Complete [Open as default]

And I need K and L to record when the changes is made as an example.

Task J2 is in progress, then K2 should record the time for that specific task and should not change once time is recorded.

When task J2 is completed, then L should record the time for that task and should not change.

Thanks!

Related:

7 responses

Answer 1 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 26, 2012 at 11:27 PM
suppose validation are there in column J then try this macro (this will succeed only as far as J cell are under validation

right click sheet tab and click view code and in that window copy paste this event code 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("J1").Column Then Exit Sub

Application.EnableEvents = False
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Cells(Target.Row, "L") = Target.Value
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
0
CQB
Jan 27, 2012 at 11:48 AM
If by Validation, having the drop down menu, then yeap it is included on there.

Unfortunately, it may need a little tweeking because if J2 is 'In Progress' then K2 records the time and L2 copies J2 = 'In Progress'.

What is needed is to have K2 record the time if J2 is 'In Progress' and have L2 record the time if J2 is 'Completed'.

Thanks!!
0
Answer 2 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 27, 2012 at 09:32 PM
is this what you want? remember the spelling of the validation list and the word given in the macro should be exact and also case sensitive
"In Progress" is different from "in progress"(differences in case) 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("j1").Column Then Exit Sub

Application.EnableEvents = False

Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.Clear
If Target.Value = "In Progress" Then
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
If Target = "Complete" Then
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
0
Answer 3 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 27, 2012 at 09:35 PM
one correction
replce this statemnt 

Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"


as 
Columns("K:L").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"


just change "K:K" and "K:L"
0
Answer 4 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 27, 2012 at 09:44 PM
sorry my brain is sluggish today

just change "K:K" to "K:L"
0
CQB
Jan 29, 2012 at 08:02 PM
Hey Venkat1926,

First I want to say THANKS!!!! for all the help. We still need last tweeking on the code:

I need the time that was in K/L to stay and not disappear/change. Because I need to record the actual times of when the task/row was started and finished.

Thanks again. I hope to hear from you soon.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 29, 2012 at 11:40 PM
does it change???idid you check
as long as you do not change the value of J which has ALREADY BEEN filled by selection of validation.


see my code 
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues

it is true first it find current time but it copied it on itself as pastevalues
so that entry is no more "now"

explain with one or two examples please
0
Answer 6 / 7
CQB
Jan 30, 2012 at 08:13 AM
When I tried the code above, it records the times correctly:
When J2 was placed 'In Progress' it recorded the proper time in column K. But once the task J2 was placed 'Complete' then it removes the time in Column K and record the proper time in L. I need the time in K to stay the same after J2 was changed to complete.

Example:
Let's say I have 10 tasks that is in row 1-10: Task 1,2,3 is now 'In Progress' and I need column K to record the times of tasks 123 when it was started.

And then once 'Completed', I need column L to record the finish time. But I need both times to stay in the proper column.

Thanks!
0
Answer 7 / 7
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 30, 2012 at 09:07 AM
I am rephrasing the condition

if j is open or in progress then k should lime (which should not change for any subsequent change in that J value) and L should be "open" or "In Progress"

once j is completed corresponding k value will r emain same as time for in progress and L should have completed time

see whether this macro does what you want! 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Range("j1").Column Then Exit Sub

Application.EnableEvents = False
If Target = "Open" Or Target = "In Progress" Then
'Range(Target.Offset(0, 1), Target.Offset(0, 2)).Cells.Clear
'If Target.Value = "In Progress" Then
Cells(Target.Row, "K") = CDate(Now)
With Cells(Target.Row, "k")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Cells(Target.Row, "L") = Target
End If
If Target = "Complete" Then
Cells(Target.Row, "L") = CDate(Now)
With Cells(Target.Row, "L")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End If
Range("J1:L1").EntireColumn.AutoFit
Columns("K:K").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
0
CQB
Jan 30, 2012 at 09:47 AM
perfect! thanks a lot!
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response