+ Reply to Thread
Results 1 to 6 of 6

Text To Columns fine except when using VBA

  1. #1
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Text To Columns fine except when using VBA

    Hi All,

    I have a column on my sheet, Column F which is full of dates. Now I need these dates to be in this format "dd,mm,yyyy" but sometimes they are in text form, us/armenian format in the original data. Why I apply a macro to convert it in the correct format, it switches the dates and months around sometimes but if I do it manually no such error occurs.

    Am using paste values to get the data in my worksheet and this is my code. It shows the correct month for the format dd/mmm/yyyy but the switches the months and date.



    Please Login or Register  to view this content.


    Lets take cell F2, date is 9th Jan 2009 in the format 09/01/2009.

    When I do my text to columns, it leaves the date as 09/01/2009.

    When I repeat the text to columns (making no changes whatsoever) it changes the date (not the format) to 1st September 2009.

    Does anyone know why this is happening? If I do text to columns manually, I can repeat it all day long with it staying in the format I like.

    The problem only occurs if I repeat the macro or if the format has been corrected once before I copy it , am not sure what I am doing wrong. Have tried changing fieldinfo to array(array(1,4) to no avail.

    Hope someone can help.

    http://www.mrexcel.com/forum/excel-q...ed-around.html

    Have also asked on Mr.excel.
    Last edited by Direj; 06-09-2014 at 06:50 AM.

  2. #2
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: Text To Columns fine except when using VBA

    Hope this can help.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Re: Text To Columns fine except when using VBA

    Quote Originally Posted by Sa DQ View Post
    Hope this can help.
    Thanks for the reply Sa but I need to make the changes in the same column.


    Everytime I apply the macro, the date changes. If its 01/06/1980 it changes to 06/01/1980 and viceversa.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Text To Columns fine except when using VBA

    Hi, Direj,

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.mrexcel.com/forum/excel-q...ed-around.html

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    06-09-2014
    Posts
    3

    Re: Text To Columns fine except when using VBA

    Quote Originally Posted by HaHoBe View Post
    Hi, Direj,

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.mrexcel.com/forum/excel-q...ed-around.html

    Ciao,
    Holger
    Thanks, have edited my OP.

  6. #6
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: Text To Columns fine except when using VBA

    Thanks for the reply Sa but I need to make the changes in the same column.
    PHP Code: 
     Columns("o:O").Copy --> Paste Special . . . . [Value] -> to Columns("G:G"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro Working fine until last line of text file
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2014, 12:10 PM
  2. pull text from 2 columns based on finding text in other columns
    By jimcuk in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-03-2013, 09:21 AM
  3. Find and Insert
    By srikanthk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2012, 02:16 AM
  4. Text To Columns fine except when using VBA
    By johncassell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2009, 07:38 AM
  5. [SOLVED] Linking text columns with text and data columns
    By Edd in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1