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!

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

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.

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

thanks for the input everyone!

You're welcome.