+ Reply to Thread
Results 1 to 7 of 7

Format Problems

  1. #1
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    297

    Format Problems

    Hello!

    Im finding some problems with formats in my workbook that I hope someone can help me to find where the error is cause I cannot see it...

    I prefer to attach the file so that you can better see what I mean.

    If you just open the workbook and run the macro, you will see in the ListBox there are numbers with plenty of decimals. Those should be whole numbers but dont know why they are displayed like that...

    I would appreciate if someone could take a small look to what I mean.

    Thank you very much!!

    Regards,
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Format Problems

    Hola pezalmendra,

    The problem is the formatted number displayed is not the same as the internally stored value. Since a LitBox control can only hold strings, the fix is an easy. Instead of loading the cell's Vlaue property, load the Text Property. Here is the macro with the correction in bold.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    297

    Re: Format Problems

    Excellent Leith!! Problem solved in the ListBox!! )

    But... do you know why for example Cell(L13) that comes by the difference between K13 - J13 gives me those decimals when they are both whole number?

    Also, I have now notice that the values stored in Column D are not as they should be (should be like in Column E)

    This is the code Im using for it: Cells(FilaActual, 4).Value = CDate(Cells(FilaActual, 3).Value) + CDate(Cells(FilaActual, 45).Value)

    Thanks Leith like always for your time and attention!!!

    Regardss

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Format Problems

    Hello penzalmendra,

    VBA stores a Date and Time as a single number. The Date is the integer part and the Time is the fractional part of the number. The date starts from December 31, 1899 or 1. The time is in seconds since midnight or zero hours. One second would be 1/86400.

    The values in column "C" are Time values (decimal fractions). These can be added directly to the Date in column "B" to produce a "complete" date/time value. The values in column "AS" need to be converted from an integer into a Time value in hours before it can be added to the date/time sum of "B" and "C". This done using the TimeSerial method. This converts Hours, Minutes and Seconds into a fractional Time value.

    Corrected Equation
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    297

    Re: Format Problems

    OK!!

    Great! SOLVED the problem

    Regards

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Format Problems

    Hola penzalmendra,

    Todo es fácil una vez que sepa cómo hacerlo. Verdad?

  7. #7
    Forum Contributor
    Join Date
    07-22-2012
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    297

    Re: Format Problems

    Hola!

    Tienes razón! Yo estoy empezando con esto y me cuesta bastante la verdad... me paso todo el día preguntando... hehehe

    Ahora estoy peleándome con una macro que creo que no tardaré mucho en preguntarte!!! La tengo bastante avanzada pero no consigo avanzar más

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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