Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
I need to have the time and date auto populated in 2 different cells when a
value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
Say we are entering data in B2 thru B10 and want the date/time recorded in C2
thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
Here is my exact scenario. I need the Date to populate in colum C and I need
the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
You don't need any formulas. Leave the col D & C area completely empty.
Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
That worked perfect. One more question.
The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
Can we have the formula in M2, say, display a blank until A2 is filled??
The previous macro only works with cells that change with human input and not as a result of calculation. We can make a different macro. The new macro will look for a blank cell becoming non-blank and then save the date/time. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That worked perfect. One more question. The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
That will work perfect. Unfortunately I do no know how to have M2 display a
blank until A2 is filled. "Gary''s Student" wrote: Can we have the formula in M2, say, display a blank until A2 is filled?? The previous macro only works with cells that change with human input and not as a result of calculation. We can make a different macro. The new macro will look for a blank cell becoming non-blank and then save the date/time. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That worked perfect. One more question. The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
If $A2 is empty, then so should M2 be empty.
Throw away all the previous versons. Use this: Private Sub Worksheet_Calculate() Set r = Range("M2:M100") For Each m In r If m.Value < "" And m.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(m.Row, "C").Value = Date Cells(m.Row, "D").Value = Time Application.EnableEvents = True End If Next End Sub -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That will work perfect. Unfortunately I do no know how to have M2 display a blank until A2 is filled. "Gary''s Student" wrote: Can we have the formula in M2, say, display a blank until A2 is filled?? The previous macro only works with cells that change with human input and not as a result of calculation. We can make a different macro. The new macro will look for a blank cell becoming non-blank and then save the date/time. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That worked perfect. One more question. The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
Works perfect!!
Thanks "Gary''s Student" wrote: If $A2 is empty, then so should M2 be empty. Throw away all the previous versons. Use this: Private Sub Worksheet_Calculate() Set r = Range("M2:M100") For Each m In r If m.Value < "" And m.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(m.Row, "C").Value = Date Cells(m.Row, "D").Value = Time Application.EnableEvents = True End If Next End Sub -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That will work perfect. Unfortunately I do no know how to have M2 display a blank until A2 is filled. "Gary''s Student" wrote: Can we have the formula in M2, say, display a blank until A2 is filled?? The previous macro only works with cells that change with human input and not as a result of calculation. We can make a different macro. The new macro will look for a blank cell becoming non-blank and then save the date/time. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That worked perfect. One more question. The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
I'm doing something similar and can't quite get it to work. I used your code
and modified for my spreadsheet. I want col B (date) and col C (time) to auto entry when someone manually enters an item in column A from a list (name). But, it's still updating with the current date/time instead of staying static. Any suggestions? Do I not have the code quite right? Thanks Private Sub Worksheet_Calculate() Set r = Range("a2:a100") For Each a In r If a.Value < "" And a.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(a.Row, "b").Value = Date Cells(a.Row, "c").Value = Time Application.EnableEvents = True End If Next End Sub -- Cathy "Gary''s Student" wrote: If $A2 is empty, then so should M2 be empty. Throw away all the previous versons. Use this: Private Sub Worksheet_Calculate() Set r = Range("M2:M100") For Each m In r If m.Value < "" And m.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(m.Row, "C").Value = Date Cells(m.Row, "D").Value = Time Application.EnableEvents = True End If Next End Sub -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That will work perfect. Unfortunately I do no know how to have M2 display a blank until A2 is filled. "Gary''s Student" wrote: Can we have the formula in M2, say, display a blank until A2 is filled?? The previous macro only works with cells that change with human input and not as a result of calculation. We can make a different macro. The new macro will look for a blank cell becoming non-blank and then save the date/time. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: That worked perfect. One more question. The purpose of the spreadsheet is to scan a barcode and get the exact date & time the barcode was scanned. I think I am almost there with your help. I have a separate worksheet I scan all these barcodes into. Then I have formulas that break down the values in the barcode and put them into separate cells. right now the macro is working off of a range of cells that require human data entry. Is there a way to have the macro work off of lets say colum "M"? This colum is populated once the barcode is scanned. Here is the current formula in colum "M". =RIGHT(LEFT($A2,36),8) I guess what I am trying to get to is, once the barcode is scanned, I would like to have the date and time appear automatically. Right now it requires human data entry into one cell. Thanks for your help. "Gary''s Student" wrote: You don't need any formulas. Leave the col D & C area completely empty. Discard the previous version. Here is the new version: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Date t.Offset(0, 2).Value = Time Application.EnableEvents = True End Sub You should adjust the "B2:B10" to suit your needs. -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: Here is my exact scenario. I need the Date to populate in colum C and I need the time to populate in column D. I have coppied the macro into the worksheet. Do I need to keep my formula in the cells that I want the static data to stay in? "Gary''s Student" wrote: Say we are entering data in B2 thru B10 and want the date/time recorded in C2 thru C10. But we want the date/time to be static. Enter the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set r = Range("B2:B10") If Intersect(r, t) Is Nothing Then Exit Sub If t.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False t.Offset(0, 1).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200818 "BAKERSMAN" wrote: I need to have the time and date auto populated in 2 different cells when a value is enterd into a different cell. Here is the formula i have now =IF(LEN(B2)=1,NOW(),"") This formula works perfect but here is the problem I run into. Lets say cell B2 equals "1", the date will populate in the cell the formula is entered into. Lets says I enter "1" into cell B3 the next day, the cell with the formula will populate the current date but the above cell's date will change to the current date also. Is there a way to keep the date and times from updating to the current time for all cells with this formula? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
Wrong type of code for what you want.
The For Each will update every cell whenever a calculation takes place. Try this sheet_change version. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Application.Intersect(Range("a2:a100"), Target) Is Nothing Then n = Target.Row If Me.Range("A" & n).Value < "" Then Me.Range("B" & n).Value = Format(Date, "dd mm yyyy") Me.Range("C" & n).Value = Format(Time, "hh:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Fri, 16 Jan 2009 13:42:05 -0800, Cathy wrote: I'm doing something similar and can't quite get it to work. I used your code and modified for my spreadsheet. I want col B (date) and col C (time) to auto entry when someone manually enters an item in column A from a list (name). But, it's still updating with the current date/time instead of staying static. Any suggestions? Do I not have the code quite right? Thanks Private Sub Worksheet_Calculate() Set r = Range("a2:a100") For Each a In r If a.Value < "" And a.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(a.Row, "b").Value = Date Cells(a.Row, "c").Value = Time Application.EnableEvents = True End If Next End Sub |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
time auto entry
This is working great! Thank you for your help.
-- Cathy "Gord Dibben" wrote: Wrong type of code for what you want. The For Each will update every cell whenever a calculation takes place. Try this sheet_change version. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Application.Intersect(Range("a2:a100"), Target) Is Nothing Then n = Target.Row If Me.Range("A" & n).Value < "" Then Me.Range("B" & n).Value = Format(Date, "dd mm yyyy") Me.Range("C" & n).Value = Format(Time, "hh:mm:ss") End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Fri, 16 Jan 2009 13:42:05 -0800, Cathy wrote: I'm doing something similar and can't quite get it to work. I used your code and modified for my spreadsheet. I want col B (date) and col C (time) to auto entry when someone manually enters an item in column A from a list (name). But, it's still updating with the current date/time instead of staying static. Any suggestions? Do I not have the code quite right? Thanks Private Sub Worksheet_Calculate() Set r = Range("a2:a100") For Each a In r If a.Value < "" And a.Offset(0, -10).Value = "" Then Application.EnableEvents = False Cells(a.Row, "b").Value = Date Cells(a.Row, "c").Value = Time Application.EnableEvents = True End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto tag the time in B1 to an entry in cell A! | Excel Discussion (Misc queries) | |||
Auto entry of data based on entry of text in another column or fie | Excel Discussion (Misc queries) | |||
How do I set up entry box to auto-alphabatize each entry in list? | Excel Discussion (Misc queries) | |||
Inconsistent 'auto-entry' | Excel Discussion (Misc queries) | |||
Time Format Auto Entry AM and PM | Excel Discussion (Misc queries) |