Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
J. E. McGimpsey's code for date stamps works great. My question is, what if
I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
I tried it but it didn't work. It didn't give me any error message, it just
didn't input the date. I figured if I posted the entire code I started with, it would be easier for you or someone else to help. Thanks again. "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
Actually, my original plan was totally different. I don't know if you can
help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
You should ALWAYS state your REAL desires so work is done once. Try this to
copy rows in sheet 1 with closed in col F to sheet 4 Sub copyautofiltered() With Sheets("sheet1") ..Range("f1").AutoFilter Field:=6, Criteria1:="closed" slr = .Cells(.Rows.Count, "f").End(xlUp).Row dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _ Copy Sheets("sheet4").Cells(dlr, 1) ..Range("f1").AutoFilter End With -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... Actually, my original plan was totally different. I don't know if you can help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
After I made a few changes, I was able to get it to work once, then the
second time I pressed the button it didn't work. You must be pretty close, though. I was hoping that I didn't have to create a button and that column F could actually be the trigger to run the macro. When any cell in column F changes to "Closed" it would copy just cells C and J from that same row to the other worksheet. Here is what I ended up with: Sub copyautofiltered() With Sheets("Log") ..Range("F6:F9999").AutoFilter Field:=6, Criteria1:="Closed" slr = .Cells(.Rows.Count, "F").End(xlUp).Row dlr = Sheets("Fundings").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("C6:J" & slr).SpecialCells(xlCellTypeVisible) _ .Copy Sheets("Fundings").Cells(dlr, 1) ..Range("f1").AutoFilter End With End Sub Many thanks for all your continued help. Scott "Don Guillett" wrote: You should ALWAYS state your REAL desires so work is done once. Try this to copy rows in sheet 1 with closed in col F to sheet 4 Sub copyautofiltered() With Sheets("sheet1") ..Range("f1").AutoFilter Field:=6, Criteria1:="closed" slr = .Cells(.Rows.Count, "f").End(xlUp).Row dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _ Copy Sheets("sheet4").Cells(dlr, 1) ..Range("f1").AutoFilter End With -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... Actually, my original plan was totally different. I don't know if you can help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
Change de jour? When you make up your mind, let us know. -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... After I made a few changes, I was able to get it to work once, then the second time I pressed the button it didn't work. You must be pretty close, though. I was hoping that I didn't have to create a button and that column F could actually be the trigger to run the macro. When any cell in column F changes to "Closed" it would copy just cells C and J from that same row to the other worksheet. Here is what I ended up with: Sub copyautofiltered() With Sheets("Log") .Range("F6:F9999").AutoFilter Field:=6, Criteria1:="Closed" slr = .Cells(.Rows.Count, "F").End(xlUp).Row dlr = Sheets("Fundings").Cells(Rows.Count, 1).End(xlUp).Row + 1 .Range("C6:J" & slr).SpecialCells(xlCellTypeVisible) _ .Copy Sheets("Fundings").Cells(dlr, 1) .Range("f1").AutoFilter End With End Sub Many thanks for all your continued help. Scott "Don Guillett" wrote: You should ALWAYS state your REAL desires so work is done once. Try this to copy rows in sheet 1 with closed in col F to sheet 4 Sub copyautofiltered() With Sheets("sheet1") ..Range("f1").AutoFilter Field:=6, Criteria1:="closed" slr = .Cells(.Rows.Count, "f").End(xlUp).Row dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _ Copy Sheets("sheet4").Cells(dlr, 1) ..Range("f1").AutoFilter End With -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... Actually, my original plan was totally different. I don't know if you can help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
What you want is a worksheet_change event. You need not autofilter.
-- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Change de jour? When you make up your mind, let us know. -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... After I made a few changes, I was able to get it to work once, then the second time I pressed the button it didn't work. You must be pretty close, though. I was hoping that I didn't have to create a button and that column F could actually be the trigger to run the macro. When any cell in column F changes to "Closed" it would copy just cells C and J from that same row to the other worksheet. Here is what I ended up with: Sub copyautofiltered() With Sheets("Log") .Range("F6:F9999").AutoFilter Field:=6, Criteria1:="Closed" slr = .Cells(.Rows.Count, "F").End(xlUp).Row dlr = Sheets("Fundings").Cells(Rows.Count, 1).End(xlUp).Row + 1 .Range("C6:J" & slr).SpecialCells(xlCellTypeVisible) _ .Copy Sheets("Fundings").Cells(dlr, 1) .Range("f1").AutoFilter End With End Sub Many thanks for all your continued help. Scott "Don Guillett" wrote: You should ALWAYS state your REAL desires so work is done once. Try this to copy rows in sheet 1 with closed in col F to sheet 4 Sub copyautofiltered() With Sheets("sheet1") ..Range("f1").AutoFilter Field:=6, Criteria1:="closed" slr = .Cells(.Rows.Count, "f").End(xlUp).Row dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _ Copy Sheets("sheet4").Cells(dlr, 1) ..Range("f1").AutoFilter End With -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... Actually, my original plan was totally different. I don't know if you can help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto enter date
:) I didn't want to autofilter if it was avoidable. I posted my original
request three times but didn't get any answers, so I figured it needed to be modified in order for anyone to help me. Sorry about the confusion. Let me explain a little bit so you know exactly what I want to do. I am creating a log for future use, meaning that unlike many other questions posted by users, I'm not looking to sort through existing data. The log is to be used pretty much all day everyday to keep track of changes/progress on files. I am trying to minimize the amount of columns that are used and the amount of data that needs to be entered, because the whole point of this log is to make it easier for us. The log does have each entry's name, dollar amount, and status (among other things). I also want to be able to keep track of all the files which closed each month. At this point, I would have to manually enter the date it closed, which of course requires another column, then sort and select the ones from this month, then add them up. If I wanted to know the names of the closed files, I would have to scroll back to column C and then I couldn't see the dollar amount with it. For that reason, I would like to be able to have the name from column C and the dollar amount from column J to form a new log whenever column F reads "Closed". As far as dividing it up by month, I haven't figured that out exactly yet. I guess I could use a date stamp which will automatically put the date in when a new entry as added to the "new log". I figured if someone sees this whole book as a question, they will get scared off :) but you're right, it would save time in the long run. Thanks again, Scott "Don Guillett" wrote: What you want is a worksheet_change event. You need not autofilter. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Change de jour? When you make up your mind, let us know. -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... After I made a few changes, I was able to get it to work once, then the second time I pressed the button it didn't work. You must be pretty close, though. I was hoping that I didn't have to create a button and that column F could actually be the trigger to run the macro. When any cell in column F changes to "Closed" it would copy just cells C and J from that same row to the other worksheet. Here is what I ended up with: Sub copyautofiltered() With Sheets("Log") .Range("F6:F9999").AutoFilter Field:=6, Criteria1:="Closed" slr = .Cells(.Rows.Count, "F").End(xlUp).Row dlr = Sheets("Fundings").Cells(Rows.Count, 1).End(xlUp).Row + 1 .Range("C6:J" & slr).SpecialCells(xlCellTypeVisible) _ .Copy Sheets("Fundings").Cells(dlr, 1) .Range("f1").AutoFilter End With End Sub Many thanks for all your continued help. Scott "Don Guillett" wrote: You should ALWAYS state your REAL desires so work is done once. Try this to copy rows in sheet 1 with closed in col F to sheet 4 Sub copyautofiltered() With Sheets("sheet1") ..Range("f1").AutoFilter Field:=6, Criteria1:="closed" slr = .Cells(.Rows.Count, "f").End(xlUp).Row dlr = Sheets("sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1 ..Range("c2:j" & slr).SpecialCells(xlCellTypeVisible). _ Copy Sheets("sheet4").Cells(dlr, 1) ..Range("f1").AutoFilter End With -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... Actually, my original plan was totally different. I don't know if you can help or not, but what I wanted to do is: For every row in which cell F reads "Closed" I want the value from cells C and J to form a list in another worksheet. I couldn't figure out how to do this, so I settled for just date stamping the same worksheet and I'll have to manually filter and sum the rows. Do you know if my original plan would be possible? Thanks, Scott "Don Guillett" wrote: What did you do with the answer I sent? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("f2:f1000"), Cells) Is Nothing Then 'Application.EnableEvents = False 'not needed If UCase(Target) = "CLOSED" Then Target.Offset(, 4) = Date End If 'Application.EnableEvents = True 'not needed End Sub -- Don Guillett SalesAid Software "Scott Marcus" wrote in message ... J. E. McGimpsey's code for date stamps works great. My question is, what if I want to date stamp when only a certain value is entered, how would I do that? Here's the code with some of my specifics: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F6:F9999"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 5).ClearContents Else With .Offset(0, 5) .NumberFormat = "mm-dd-yy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub I'd like Column K to be date stamped only when the value of column F = "Closed". With every other value, including blank, I'd like column K to be empty, even if it previously had a value. Does anyone know how I can accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i enter a date before jan 1, 1900 into excel? | Excel Discussion (Misc queries) | |||
Auto Date with Manual Entry Protection | Excel Worksheet Functions | |||
Date formulas | Excel Discussion (Misc queries) | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
auto dating after entering the first date | Excel Worksheet Functions |