+ Reply to Thread
Results 1 to 32 of 32

VBA to turn cell data to particular format

  1. #1
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    VBA to turn cell data to particular format

    Hi guys. I was hoping someone might have an easy solution for turning:

    0722 in a cell (representing 7hours 22 minutes)

    into

    7 H 22 M

    where anytime 4 numbers are typed into N2:N1000, it will auto convert it to # H # M.

    Another example: I type 0815 into cell n2 and hit enter. It converts to 8 H 15 M.

    Thanks.

  2. #2
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    Use this format

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    Not sure how that answers my question. I'm looking for vba code. The format 7 H 30 M I'm using must stay the way it is as many other formulas depend on it displaying that way.

  4. #4
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    You want format right? Why not the standard Excel format?

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    This is what Jec is saying

    Please Login or Register  to view this content.
    Last edited by AB33; 01-14-2022 at 04:47 PM.

  6. #6
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    because I don't like that format. Also, because I have too many formulas that tie into the # H # M format I'm using. The format I chose is easier to read at a glance. Since I'm using my workbook on a tablet, I don't want to have to type H and M. I just want to type a 4 digit number and have vba correct it for me.

  7. #7
    Registered User
    Join Date
    01-18-2021
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to turn cell data to particular format

    Selection.NumberFormat = "#\h##\m"
    or
    Range("A1").NumberFormat = "#\h##\m"
    theres just an extra backslash in vba numberformat

  8. #8
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    @saeber, you obviously don’t know what formatting does. You don’t need to type H and M with these examples.
    Exactly what you are asking

  9. #9
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    I tried pasting your vba into the sheet and it doesn't do anything. I typed in 0730 into a cell and it just displayed as 730

  10. #10
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    Vba and tablets? Bad idea

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    Paste the code in to the module (Not sheet) and make sure that you have data from N2 to the last active cell in column N.

  12. #12
    Registered User
    Join Date
    01-18-2021
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to turn cell data to particular format

    im not sure i follow? is it the entiresheet you need to format this way? in that case you can just use cells.numberformat = "#\h##\m"

    Edit: I just read it was the range N2:N1000
    in jthat case
    Range("N2:N1000").numberformat = "#\h##\m"
    Last edited by Crib; 01-14-2022 at 05:08 PM.

  13. #13
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    what's wrong with vba and tablets?

  14. #14
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    A lot, depending on which tablet. Microsoft tablets will do.

  15. #15
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    AB33, I created a module and pasted it in. Still doesn't do anything.

  16. #16
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    Maybe it would be easier to attach my workbook? I just tried but when I click on the paperclip icon, it won't do anything

  17. #17
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    Again, with standard formatting....
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    Just Click reply, then in the next page, there is an attachment (Please red the banner at the bottom)

  19. #19
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    refer to mileage and pay tab in column n. I'm also having trouble in column s where when I add text to the formula in the cell return it displays the date and time as a decimal. I'm not sure what the formula is to restructure the decimal back into a date format of mm/dd/ 13:30.
    Attached Files Attached Files
    Last edited by Saeber4777; 01-14-2022 at 05:29 PM.

  20. #20
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: VBA to turn cell data to particular format

    Already tried my file?

  21. #21
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    The code only works with active sheet.
    It works with sheet Mileage & Pay
    what is issue then?

  22. #22
    Registered User
    Join Date
    01-18-2021
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    6

    Re: VBA to turn cell data to particular format

    Ive changed the format in column N (Right click, Format Cells..., Custom, #\H##\M)
    and ive insertet the Text(Value, "mm/dd/yyyy hh:mm") to the formula in column S.

    Hope this solves your issues.
    Attached Files Attached Files

  23. #23
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    Thanks Crib! Works great. Is there a way to make it so that the format 7H20M displays as 7 H 20 M?

  24. #24
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    "#h ## \m"

  25. #25
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    thank you AB33

  26. #26
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    I put that in and it doesn't change the format to 7 H 20 M from 7H20M. It's the same code that was in there before. How do I add the spaces?

  27. #27
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    If I can't add the spaces, my formulas will return an error message because it is trying to read the H and M as numbers.

  28. #28
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    Well, just tried out and it works.
    I have two codes as the name indicate.
    Run the first code on any active sheet as long as your cursor is on that sheet when you run the code.

    Please Login or Register  to view this content.
    Last edited by AB33; 01-14-2022 at 06:40 PM.

  29. #29
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    I can get it to work when I manually run the vba code. When I type it into the cell, it doesn't autocorrect until I manually calc.

  30. #30
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    by manually calc I mean hit the play button in vba window

  31. #31
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to turn cell data to particular format

    Put this code on sheet
    When you add or change any data in column N, the code is auto triggered. The code does not go into a module, rather in a worksheet as attached.
    Attached Files Attached Files

  32. #32
    Forum Contributor Saeber4777's Avatar
    Join Date
    06-06-2014
    Location
    United States
    MS-Off Ver
    365 Enterprise
    Posts
    159

    Re: VBA to turn cell data to particular format

    works. Thank you!

+ 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] turn off changing to date format
    By slamont in forum Excel General
    Replies: 1
    Last Post: 04-19-2021, 08:42 PM
  2. Format cell to turn red after 30 days unless...
    By Bigdaddywhale in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-19-2019, 05:39 AM
  3. [SOLVED] Conditional Format a cell to turn red based upon the color of another cell.
    By rjmall0 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2019, 09:18 PM
  4. how to turn off automatic cell value format on excel
    By mario99ukdw in forum Excel General
    Replies: 1
    Last Post: 03-21-2014, 05:57 AM
  5. Turn Automatic Date Format Off
    By bigson101 in forum Excel General
    Replies: 2
    Last Post: 03-24-2007, 12:24 PM
  6. Turn off automatic date format
    By jpkeller55 in forum Excel General
    Replies: 3
    Last Post: 01-05-2006, 08:10 PM
  7. [SOLVED] Conditional format question:turn Cell
    By Patrick Simonds in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2005, 01:05 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