+ Reply to Thread
Results 1 to 6 of 6

Help with vba code for adding 0 depending on cells' amount of characters

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    34

    Help with vba code for adding 0 depending on cells' amount of characters

    Hi!

    I'm working with a column that contains dates. The dates are pulled from a system that doesnt always have dates in the format
    mm/dd/yyyy => 09/26/2014

    but instead sometimes
    9/26/2014
    or
    4/7/2014

    i.e., 0's are missing.

    I'm looking for a macro that inserts these missing 0's

    **So I suppose the criteria should be that if the cell is 9 characters long (including the "/"s), then it adds a 0 as first character**
    and
    ***the cells that are only 8 characters, in that case there would have to be insert 0 again as character 1, and 0 as character 4 (if 0 is already added as first)***

    EDIT: I'll attach a file that contains dates.

    Help is much appreciated, thanks!

    Chris
    Attached Files Attached Files
    Last edited by ChristopherH; 08-05-2014 at 06:45 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    format them as dates
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    Hi,

    Yeah, its possible, but dates are used in another formula that has to be exact

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    Yeah, you can put range("a1").value = format(dtReturn, vbShortDate) or

    you can put range("a1").value = format(dtReturn, "mm/dd/yyyy")

  5. #5
    Registered User
    Join Date
    01-04-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    nathan,

    hmm, it just turns the cells into blanks.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    You need to change dtRetrurn for your value.

  7. #7
    Registered User
    Join Date
    01-04-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: Help with vba code for adding 0 depending on cells' amount of characters

    nathan. Okay, I see. How do I do that ? .. I'm still learning vba, I dont know how to apply do the dtReturn by code.

    thanks!

+ 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. How to distribute on amount in to different cells depending on the dale range
    By saidmalikov in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2014, 11:51 AM
  2. VBA code to limit the amount of characters in a input box?
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 04:39 PM
  3. [SOLVED] Help In Adding Amount in Cells Based on Name in Another Cell
    By dksodhi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2013, 05:47 AM
  4. Adding 1 Dollar Amount to All Cells
    By acexcel in forum Excel General
    Replies: 2
    Last Post: 01-06-2010, 01:30 PM
  5. function to count the amount of cells depending on part of the cell entry
    By Andyd74 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-25-2006, 06:20 AM

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