+ Reply to Thread
Results 1 to 7 of 7

VBA Searching for numbers with decimals and reporting it

  1. #1
    Registered User
    Join Date
    06-16-2010
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    VBA Searching for numbers with decimals and reporting it

    Hello all!

    My problem is that we receive fairly large spreadsheets from different users and they all like to follow different numerical formats. However we need them to be in standard style for later upload. Some user like to use commas, some use spaces and some use decimals as to indicate number breaks like below

    2000
    2,000
    2.000
    2 000

    What I'm trying to do is write a macro code that will search for decimals in each of the sheets in a workbook (the others will be caught with other later processes) and report to the user when they are found. There will be some text in the document as well so I can't just search for "." unless I can ignore any results that have text in them.

    Ideally it would show a pop up with something like "decimals found on sheet 2".

    what do you think? If any of of the excel geniuses can whip out some code that would be great! but at this point I'll take a friendly nudge in a good direction as well.

    thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA Searching for numbers with decimals and reporting it

    2000, with or without a comma (,) ... 2000 and 2,000 ... is an acceptable number and will be seen as 2000. Similarly, 2.000 is an acceptable number but would be treated as 2, not 2000. But how would you, or Excel, determine whether the value entered should be seen as 2 or 2000? 2 000 is not seen as a number but you could use a global replace of space for nothing (null value).

    So, before anyone can write code, you need to determine the "rules". Probably best to provide a sample workbook with a variety of typical examples.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: VBA Searching for numbers with decimals and reporting it

    I agree with TMS. I am going to make a small assumption that since you "receive" the worksheets the values are all coming as string data. If so this brute force code will probably work:

    Please Login or Register  to view this content.
    If you are happy with my response please click the * in the lower left of my post.

  4. #4
    Registered User
    Join Date
    06-16-2010
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: VBA Searching for numbers with decimals and reporting it

    The reason I'm focusing on the decimal is that we should never receive a number with a decimal. So if we do that's an error we need to know about and also correct. That's why I'm focusing on finding decimals instead of verifying numbers or just replacing decimals as found.

    2000 - is ideal
    2,000 - will work
    2 000 - will be caught at a later stage
    2.000 - will process as 2 instead of 2000 and cause problems

    does that help? the more I look into it the more it sounds like just do a CTRL+ F for the entire workbook and eyeball it and look manually at the results.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA Searching for numbers with decimals and reporting it

    I think you need to post a sample workbook with some typical input. If the data is keyed into Excel, a 2 could be displayed simply as 2, or as 2.0, 2.00, or whatever, depending on the formatting in the cell. There will not be a decimal point, regardless of how many zeroes are displayed. Are you saying that, if you get 2.123, that should be interpreted as 2,123 = 2123? And, if you saw 2.000, that should be interpreted as 2,000 = 2000. In the latter case, you cannot find and replace the decimal point (.) because it simply won't be there.

    All that said, if the cells are formatted as Text, you should be able to do a Replace All for ".", ",", " ", or anything else and replace it with a null value.

    But, maybe this as a starter:

    Please Login or Register  to view this content.

    Regards, TMS

  6. #6
    Registered User
    Join Date
    06-16-2010
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: VBA Searching for numbers with decimals and reporting it

    thanks for the input everyone!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: VBA Searching for numbers with decimals and reporting it

    You're welcome.

+ 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] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  2. Searching a column of cells and reporting results in a list
    By utahmoose in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2013, 07:14 PM
  3. Searching Sheet1 from other sheets, and reporting back multiple results.
    By Mjones in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 06:47 PM
  4. Combo Box is not reporting numbers correctly
    By Freddobonanza in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-18-2011, 03:31 PM
  5. Replies: 3
    Last Post: 03-07-2010, 08:36 AM
  6. whole numbers being changed to decimals
    By PCH in forum Excel General
    Replies: 1
    Last Post: 05-25-2009, 03:34 PM
  7. About decimals numbers
    By RazBoss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2009, 02:39 PM
  8. Numbers have too many decimals
    By stev49 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-18-2009, 07:01 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