Excel HELP
Solved/Closed
Related:
- Excel HELP
- Excel marksheet - Guide
- Number to words in excel - Guide
- Excel apk for pc - Download - Spreadsheets
- Kernel for excel - Download - Backup and recovery
- Gif in excel - Guide
7 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 26, 2012 at 11:27 PM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 27, 2012 at 09:32 PM
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)
"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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 27, 2012 at 09:35 PM
Jan 27, 2012 at 09:35 PM
one correction
replce this statemnt
as
just change "K:K" and "K:L"
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"
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 27, 2012 at 09:44 PM
Jan 27, 2012 at 09:44 PM
sorry my brain is sluggish today
just change "K:K" to "K:L"
just change "K:K" to "K:L"
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.
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.
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 29, 2012 at 11:40 PM
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
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
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
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!
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!
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 30, 2012 at 09:07 AM
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!
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
Jan 27, 2012 at 11:48 AM
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!!