VBA convert number stored as text to number

Hi,

I am creating a spreadsheet for others to use. In the past I have found that when others paste data into the spreadsheet sometimes the data is stored as text which means that the formulas won't calculate correctly. They may also have leading or trailing spaces. I have used the following to fix the leading/trailing spaces and it seems to work. I can't figure out how to also convert these columns in the same macro so that the formulas work correctly. You will see in my example that row 7 is correctly calculated but row 8 is not. I have used 'define name' as these columns will change when columns are inserted/deleted by the users. The range is columns D,E,M & S. My knowledge of macros isn't the best, I really just play around with suggestions I find on forums like this but I can't seem to get this one.

Any help would be greatly appreciated.

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

Thanks Jindon, that seems to work. If I want this to run with the macro above when a button is pressed do I need to call your macro within the first macro or is there another way?

Well I'd call the code when the data has been added to the sheet, it depends on you...