| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programatically copy cells with dates
I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy
format. 10-12-2005 16-12-2005 03-12-2005 09-12-2005 26-11-2005 02-12-2005 19-11-2005 25-11-2005 12-11-2005 18-11-2005 05-11-2005 11-11-2005 29-10-2005 04-11-2005 22-10-2005 28-10-2005 I then run the following code, Public Sub copydates() Worksheets("Sheet2").Range("a8:b15").Value = Worksheets("Sheet1").Range_("a8:b15").Value Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" End Sub Which result in the following on the second sheet 12-10-2005 12/16/2005 12-03-2005 12-09-2005 11/26/2005 12-02-2005 11/19/2005 11/25/2005 11-12-2005 11/18/2005 11-05-2005 11-11-2005 10/29/2005 11-04-2005 10/22/2005 10/28/2005 Can someone please explain what excel is doing wrong or more lickley what am I doing wrong. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programatically copy cells with dates
Select the original range on sheet1
Give that range a nice format: mmmm dd, yyyy The cells that don't change to a nice long date aren't really dates--they're text that just look like dates. In fact, you may find that some of the real dates aren't what you expect. Is 10-12-2005 really October 12, 2005 or December 10, 2005. With an unambiguous format, you'll see it right away. Francis Brown wrote: I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy format. 10-12-2005 16-12-2005 03-12-2005 09-12-2005 26-11-2005 02-12-2005 19-11-2005 25-11-2005 12-11-2005 18-11-2005 05-11-2005 11-11-2005 29-10-2005 04-11-2005 22-10-2005 28-10-2005 I then run the following code, Public Sub copydates() Worksheets("Sheet2").Range("a8:b15").Value = Worksheets("Sheet1").Range_("a8:b15").Value Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" End Sub Which result in the following on the second sheet 12-10-2005 12/16/2005 12-03-2005 12-09-2005 11/26/2005 12-02-2005 11/19/2005 11/25/2005 11-12-2005 11/18/2005 11-05-2005 11-11-2005 10/29/2005 11-04-2005 10/22/2005 10/28/2005 Can someone please explain what excel is doing wrong or more lickley what am I doing wrong. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programatically copy cells with dates
Hello
Thanks for reply. All the dates are in the format i mentioned. If you change the cell format to general the excel date codes are all correct. like this 38696 38702 38689 38695 38682 38688 38675 38681 38668 38674 38661 38667 38654 38660 38647 38653 any further input. regards Francis. "Dave Peterson" wrote: Select the original range on sheet1 Give that range a nice format: mmmm dd, yyyy The cells that don't change to a nice long date aren't really dates--they're text that just look like dates. In fact, you may find that some of the real dates aren't what you expect. Is 10-12-2005 really October 12, 2005 or December 10, 2005. With an unambiguous format, you'll see it right away. Francis Brown wrote: I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy format. 10-12-2005 16-12-2005 03-12-2005 09-12-2005 26-11-2005 02-12-2005 19-11-2005 25-11-2005 12-11-2005 18-11-2005 05-11-2005 11-11-2005 29-10-2005 04-11-2005 22-10-2005 28-10-2005 I then run the following code, Public Sub copydates() Worksheets("Sheet2").Range("a8:b15").Value = Worksheets("Sheet1").Range_("a8:b15").Value Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" End Sub Which result in the following on the second sheet 12-10-2005 12/16/2005 12-03-2005 12-09-2005 11/26/2005 12-02-2005 11/19/2005 11/25/2005 11-12-2005 11/18/2005 11-05-2005 11-11-2005 10/29/2005 11-04-2005 10/22/2005 10/28/2005 Can someone please explain what excel is doing wrong or more lickley what am I doing wrong. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programatically copy cells with dates
If you change the lines around:
Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" Worksheets("Sheet2").Range("a8:b15").Value _ = Worksheets("Sheet1").Range("a8:b15").Value Does it work better? If I had sheet2!a8:b15 formatted as text to start with, then I didn't get what I wanted. When I swapped the lines to format first, it worked ok. Sometimes screwing with dates becomes lots safer if you use the .value2 property. Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" Worksheets("Sheet2").Range("a8:b15").Value2 _ = Worksheets("Sheet1").Range("a8:b15").Value2 ..value2 will use the serial date (like 38696). Then applying the numberformat in either order was ok. Francis Brown wrote: Hello Thanks for reply. All the dates are in the format i mentioned. If you change the cell format to general the excel date codes are all correct. like this 38696 38702 38689 38695 38682 38688 38675 38681 38668 38674 38661 38667 38654 38660 38647 38653 any further input. regards Francis. "Dave Peterson" wrote: Select the original range on sheet1 Give that range a nice format: mmmm dd, yyyy The cells that don't change to a nice long date aren't really dates--they're text that just look like dates. In fact, you may find that some of the real dates aren't what you expect. Is 10-12-2005 really October 12, 2005 or December 10, 2005. With an unambiguous format, you'll see it right away. Francis Brown wrote: I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy format. 10-12-2005 16-12-2005 03-12-2005 09-12-2005 26-11-2005 02-12-2005 19-11-2005 25-11-2005 12-11-2005 18-11-2005 05-11-2005 11-11-2005 29-10-2005 04-11-2005 22-10-2005 28-10-2005 I then run the following code, Public Sub copydates() Worksheets("Sheet2").Range("a8:b15").Value = Worksheets("Sheet1").Range_("a8:b15").Value Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" End Sub Which result in the following on the second sheet 12-10-2005 12/16/2005 12-03-2005 12-09-2005 11/26/2005 12-02-2005 11/19/2005 11/25/2005 11-12-2005 11/18/2005 11-05-2005 11-11-2005 10/29/2005 11-04-2005 10/22/2005 10/28/2005 Can someone please explain what excel is doing wrong or more lickley what am I doing wrong. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programatically copy cells with dates
I also found a similar method.
Set sheet one number format to general Do the copy Set both sheets number formats back to dd-mm-yyyy. This works. I wasnt aware of value2 setting and will read up on this. Thanks for input. "Dave Peterson" wrote: If you change the lines around: Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" Worksheets("Sheet2").Range("a8:b15").Value _ = Worksheets("Sheet1").Range("a8:b15").Value Does it work better? If I had sheet2!a8:b15 formatted as text to start with, then I didn't get what I wanted. When I swapped the lines to format first, it worked ok. Sometimes screwing with dates becomes lots safer if you use the .value2 property. Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" Worksheets("Sheet2").Range("a8:b15").Value2 _ = Worksheets("Sheet1").Range("a8:b15").Value2 ..value2 will use the serial date (like 38696). Then applying the numberformat in either order was ok. Francis Brown wrote: Hello Thanks for reply. All the dates are in the format i mentioned. If you change the cell format to general the excel date codes are all correct. like this 38696 38702 38689 38695 38682 38688 38675 38681 38668 38674 38661 38667 38654 38660 38647 38653 any further input. regards Francis. "Dave Peterson" wrote: Select the original range on sheet1 Give that range a nice format: mmmm dd, yyyy The cells that don't change to a nice long date aren't really dates--they're text that just look like dates. In fact, you may find that some of the real dates aren't what you expect. Is 10-12-2005 really October 12, 2005 or December 10, 2005. With an unambiguous format, you'll see it right away. Francis Brown wrote: I Start with the following dates in the range a8 to b15 in the dd-mm-yyyy format. 10-12-2005 16-12-2005 03-12-2005 09-12-2005 26-11-2005 02-12-2005 19-11-2005 25-11-2005 12-11-2005 18-11-2005 05-11-2005 11-11-2005 29-10-2005 04-11-2005 22-10-2005 28-10-2005 I then run the following code, Public Sub copydates() Worksheets("Sheet2").Range("a8:b15").Value = Worksheets("Sheet1").Range_("a8:b15").Value Worksheets("Sheet2").Range("a8:b15").NumberFormat = "dd-mm-yyyy" End Sub Which result in the following on the second sheet 12-10-2005 12/16/2005 12-03-2005 12-09-2005 11/26/2005 12-02-2005 11/19/2005 11/25/2005 11-12-2005 11/18/2005 11-05-2005 11-11-2005 10/29/2005 11-04-2005 10/22/2005 10/28/2005 Can someone please explain what excel is doing wrong or more lickley what am I doing wrong. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lock and unlock cells programatically | Excel Programming | |||
| unhide cells programatically | Excel Programming | |||
| programatically merge cells? | Excel Programming | |||
| Can't unmerge merged cells programatically | Excel Programming | |||
| Copy Worksheet to another workbook, programatically? | Excel Programming | |||
Linear Mode
