Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste results not as expected
First post, so please be gentle on me!!
I have a workbook with data exported from another DB which contain a mixture of dates number and times. These are mixed within the same columns. If I try to copy and paste to another worksheet or workbook within the same instance of excel the dates are not pasted as dates (even if I use paste special and select values only). The dates are formatted to the left by default. I have got around this by opening another copy of excel and then using paste special which gives me an entirley different dialog box and then I select text or csv as the paste special format. Thsi gives me dates as dates (formatted to the right by default) So far so good, but now I want to automate the copy ansd paste special I am using ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False This gives the original error of the dates not being true dates. Now they are formatted to the left by default and any calulation using the dates fails. The macro was recorded and does not work despite the fact that the original action that created the macro worked. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste results not as expected
First, I wouldn't give up on just using one instance of excel and trying to get
that to work correctly. I can't recall a time when I copied a date and pasted and it didn't stay a date. Are you sure your original data is really dates? If you put =isnumber(a1) (if a1 is one of the cells copied) do you get true or false? If you do the same thing in the receiving worksheet/workbook, do you get true or false? The only time I've ever had trouble with dates is when I copied from a different workbook and that workbook used a different starting date (1904 vs. 1900, on tools|options|calculation tab). But the values get pasted as dates, just not the dates I want! Barbar wrote: First post, so please be gentle on me!! I have a workbook with data exported from another DB which contain a mixture of dates number and times. These are mixed within the same columns. If I try to copy and paste to another worksheet or workbook within the same instance of excel the dates are not pasted as dates (even if I use paste special and select values only). The dates are formatted to the left by default. I have got around this by opening another copy of excel and then using paste special which gives me an entirley different dialog box and then I select text or csv as the paste special format. Thsi gives me dates as dates (formatted to the right by default) So far so good, but now I want to automate the copy ansd paste special I am using ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False This gives the original error of the dates not being true dates. Now they are formatted to the left by default and any calulation using the dates fails. The macro was recorded and does not work despite the fact that the original action that created the macro worked. Any suggestions? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste results not as expected
Thanks for your response.
I did what you said and the original data returned TRUE for all of the cells. For the pasted cells doing it manually it returned TRUE for 1 date and FALSE for another. I am using UK dates and I noticed that a date such as 30/10/2006 did not paste correctly using the macro but a date such as 01/11/2006 did paste correctly. Hope that helps to clarify things. "Dave Peterson" wrote: First, I wouldn't give up on just using one instance of excel and trying to get that to work correctly. I can't recall a time when I copied a date and pasted and it didn't stay a date. Are you sure your original data is really dates? If you put =isnumber(a1) (if a1 is one of the cells copied) do you get true or false? If you do the same thing in the receiving worksheet/workbook, do you get true or false? The only time I've ever had trouble with dates is when I copied from a different workbook and that workbook used a different starting date (1904 vs. 1900, on tools|options|calculation tab). But the values get pasted as dates, just not the dates I want! Barbar wrote: First post, so please be gentle on me!! I have a workbook with data exported from another DB which contain a mixture of dates number and times. These are mixed within the same columns. If I try to copy and paste to another worksheet or workbook within the same instance of excel the dates are not pasted as dates (even if I use paste special and select values only). The dates are formatted to the left by default. I have got around this by opening another copy of excel and then using paste special which gives me an entirley different dialog box and then I select text or csv as the paste special format. Thsi gives me dates as dates (formatted to the right by default) So far so good, but now I want to automate the copy ansd paste special I am using ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False This gives the original error of the dates not being true dates. Now they are formatted to the left by default and any calulation using the dates fails. The macro was recorded and does not work despite the fact that the original action that created the macro worked. Any suggestions? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste results not as expected
I have cured it by the way.
I used the following code Dim Cell As Object For Each Cell In Selection If IsDate(Cell.Value) Then Cell.Value = DateValue(Cell.Value) End If Next Cell End Sub I guess that will work with both one or two instances of excel. Thanks for your help, it got me thinking on the right lines. "Dave Peterson" wrote: First, I wouldn't give up on just using one instance of excel and trying to get that to work correctly. I can't recall a time when I copied a date and pasted and it didn't stay a date. Are you sure your original data is really dates? If you put =isnumber(a1) (if a1 is one of the cells copied) do you get true or false? If you do the same thing in the receiving worksheet/workbook, do you get true or false? The only time I've ever had trouble with dates is when I copied from a different workbook and that workbook used a different starting date (1904 vs. 1900, on tools|options|calculation tab). But the values get pasted as dates, just not the dates I want! Barbar wrote: First post, so please be gentle on me!! I have a workbook with data exported from another DB which contain a mixture of dates number and times. These are mixed within the same columns. If I try to copy and paste to another worksheet or workbook within the same instance of excel the dates are not pasted as dates (even if I use paste special and select values only). The dates are formatted to the left by default. I have got around this by opening another copy of excel and then using paste special which gives me an entirley different dialog box and then I select text or csv as the paste special format. Thsi gives me dates as dates (formatted to the right by default) So far so good, but now I want to automate the copy ansd paste special I am using ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False This gives the original error of the dates not being true dates. Now they are formatted to the left by default and any calulation using the dates fails. The macro was recorded and does not work despite the fact that the original action that created the macro worked. Any suggestions? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste results not as expected
Be careful.
If you import strings that look like dates, you may get a real date--but not the one you want. For instance: if I type 30/10/2006 in a cell using my USA settings (mdy), I end up with a string--not a date. But if I type 01/11/2006 in a cell, I get a real data--January 11, 2006. But if I expected November 1st, 2006, I didn't get it. If I were you, I'd re-import the text data once more--and make those fields text. Then use something else to convert them to dates. Select a column Data|Text to columns fixed width (remove any lines) Choose date (DMY) and then format it the way you want. And while you're doing this, I'd recommend that you use unambiguous date formats just to make sure things are running smoothly: MMM DD, YYYY would work ok. Barbar wrote: I have cured it by the way. I used the following code Dim Cell As Object For Each Cell In Selection If IsDate(Cell.Value) Then Cell.Value = DateValue(Cell.Value) End If Next Cell End Sub I guess that will work with both one or two instances of excel. Thanks for your help, it got me thinking on the right lines. "Dave Peterson" wrote: First, I wouldn't give up on just using one instance of excel and trying to get that to work correctly. I can't recall a time when I copied a date and pasted and it didn't stay a date. Are you sure your original data is really dates? If you put =isnumber(a1) (if a1 is one of the cells copied) do you get true or false? If you do the same thing in the receiving worksheet/workbook, do you get true or false? The only time I've ever had trouble with dates is when I copied from a different workbook and that workbook used a different starting date (1904 vs. 1900, on tools|options|calculation tab). But the values get pasted as dates, just not the dates I want! Barbar wrote: First post, so please be gentle on me!! I have a workbook with data exported from another DB which contain a mixture of dates number and times. These are mixed within the same columns. If I try to copy and paste to another worksheet or workbook within the same instance of excel the dates are not pasted as dates (even if I use paste special and select values only). The dates are formatted to the left by default. I have got around this by opening another copy of excel and then using paste special which gives me an entirley different dialog box and then I select text or csv as the paste special format. Thsi gives me dates as dates (formatted to the right by default) So far so good, but now I want to automate the copy ansd paste special I am using ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _ False This gives the original error of the dates not being true dates. Now they are formatted to the left by default and any calulation using the dates fails. The macro was recorded and does not work despite the fact that the original action that created the macro worked. Any suggestions? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro won't paste results | Excel Discussion (Misc queries) | |||
unable to get expected results while anding two coloumns | Excel Discussion (Misc queries) | |||
Sumproduct not returning expected results | Excel Worksheet Functions | |||
Paste results consecutively within column (based off other worksheet) | Excel Worksheet Functions | |||
copy and paste cells to a different worksheet according to IF results | Excel Programming |