Te data you initially receive is brought in as a STRING. When you copy it down, Excel is converting it to a DATE (number). So, no matter WHAT date format you choose, the two VALUES will be different. So, you have a choice, either you play around with forcing Excel to duplicate the values as String values, or you ask (force) Excel to convert the string values a date value.
If you utilize string values, when you create the table of dates to report, THESE must be strings also! or... By adding the two lines below: Else Cells(i,5).Value = Cells(i,5).Value you accomplish the move to a Date Value and then your future problems are avoided! Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: Darwin Chan <darwin.chankaw...@gmail.com> To: excel-macros@googlegroups.com Sent: Thu, January 5, 2012 11:15:42 AM Subject: Re: $$Excel-Macros$$ VBA code for pasting date, not plain text Dear all, I would choose dd/mm/yyyy format first, however, should I standardize all the date format first before running the macro, or simply use the code from Paul as, If IsEmpty(Cells(i, 5)) Then Cells(i, 5).Value = Cells(i - 1, 5).Value Else Cells(i, 5).Value = Cells(i, 5).Value End If Or from Rajan, Sub CopyDate() Dim rngDate As Range Set rngDate = Selection ActiveCell.Resize(rngDate.Rows.Count, 1).Value = rngDate.Value End Sub Thanks~ 2012/1/5 dguillett1 <dguille...@gmail.com> Thanks Paul, Don’t remember encountering that. However, when I changed the destination date, formulas calculated... > >1/1/2012 >2/1/2012 > > >Don Guillett >SalesAid Software >dguille...@gmail.com > >From: Paul Schreiner >Sent: Thursday, January 05, 2012 8:12 AM >To: excel-macros@googlegroups.com >Subject: Re: $$Excel-Macros$$ VBA code for pasting date, not plain text > >Actually, I almost made the same mistake! >What's really happening is that HIS Windows environment is configured to >display >dates as dd/mm/yyyy >so to HIM both date formats look the same. > >But since OUR environment settings use mm/dd/yyyy, then >when we open the workbook, all legitimate Excel "dates" (which are really >numbers displayed in date format) >change to display as our configuration directs it. >But the STRING values remain unchanged. > >I guess what I'm saying is that to US, the look like they're in different >formats, >but to HIM, they may look identical. > >The problem is that one is a date string while the other is a number DISPLAYED >as a date. > >So, your reply is probably totally confusing to him! > >Paul >----------------------------------------- >“Do all the good you can, >By all the means you can, >In all the ways you can, >In all the places you can, >At all the times you can, >To all the people you can, >As long as ever you can.” - John Wesley >----------------------------------------- > > > > ________________________________ From: dguillett1 <dguille...@gmail.com> >To: excel-macros@googlegroups.com >Sent: Thu, January 5, 2012 9:05:16 AM >Subject: Re: $$Excel-Macros$$ VBA code for pasting date, not plain text > > >You may want to reconsider what you are asking for. >One set dd/mm/yyyy and the other mm/dd/yyyy > > >Don Guillett >SalesAid Software >dguille...@gmail.com > >From: Darwin Chan >Sent: Thursday, January 05, 2012 3:42 AM >To: excel-macros@googlegroups.com >Subject: $$Excel-Macros$$ VBA code for pasting date, not plain text >Dear group, > >I have written a macro for recording data for customer transaction every hour >within a day. First I refresh the pivot table and copy the data to another >area >for ease to lookup. However, I found the VBA code i use just paste the date >with >wrong format. This makes me cannot lookup the desire value. Could anyone help >to >improve my code? > >*Remarks: Procedure name is "sp" > >-- >Darwin Chan >darwin.chankaw...@gmail.com >kw42c...@yahoo.com.hk >-- >FORUM RULES (986+ members already BANNED for violation) > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. > >2) Don't post a question in the thread of another member. > >3) Don't post questions regarding breaking or bypassing any security measure. > >4) Acknowledge the responses you receive, good or bad. > >5) Cross-promotion of, or links to, forums competitive to this forum in >signatures are prohibited. > > >NOTE : Don't ever post personal or confidential data in a workbook. Forum >owners >and members are not responsible for any loss. > >------------------------------------------------------------------------------------------------------ > >To post to this group, send email to excel-macros@googlegroups.com >-- >FORUM RULES (986+ members already BANNED for violation) > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. > >2) Don't post a question in the thread of another member. > >3) Don't post questions regarding breaking or bypassing any security measure. > >4) Acknowledge the responses you receive, good or bad. > >5) Cross-promotion of, or links to, forums competitive to this forum in >signatures are prohibited. > > >NOTE : Don't ever post personal or confidential data in a workbook. Forum >owners >and members are not responsible for any loss. > >------------------------------------------------------------------------------------------------------ > >To post to this group, send email to excel-macros@googlegroups.com >-- >FORUM RULES (986+ members already BANNED for violation) > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. > >2) Don't post a question in the thread of another member. > >3) Don't post questions regarding breaking or bypassing any security measure. > >4) Acknowledge the responses you receive, good or bad. > >5) Cross-promotion of, or links to, forums competitive to this forum in >signatures are prohibited. > > >NOTE : Don't ever post personal or confidential data in a workbook. Forum >owners >and members are not responsible for any loss. > >------------------------------------------------------------------------------------------------------ > >To post to this group, send email to excel-macros@googlegroups.com > >-- >FORUM RULES (986+ members already BANNED for violation) > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. > >2) Don't post a question in the thread of another member. > >3) Don't post questions regarding breaking or bypassing any security measure. > >4) Acknowledge the responses you receive, good or bad. > >5) Cross-promotion of, or links to, forums competitive to this forum in >signatures are prohibited. > > >NOTE : Don't ever post personal or confidential data in a workbook. Forum >owners >and members are not responsible for any loss. > >------------------------------------------------------------------------------------------------------ > >To post to this group, send email to excel-macros@googlegroups.com > -- Darwin Chan darwin.chankaw...@gmail.com kw42c...@yahoo.com.hk -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
