Problem creating a macro to convert numbers which are stored as text

[LemonTwist]

Sometimes when you paste data from a Microsoft Access table into an Excel spreadsheet you get little green triangles in the top left-hand corner of the numeric cells. When you click on it, it says that the number is stored as text, and then there is an option to convert it to number. At the moment, I have to highlight all the affected cells and then click the option to convert them to numbers. Is there any way I can write a macro to automatically convert all the cells in a given column to number?

This is what I've tried so far:
If I highlight all the affected cells and go to the Format Cells dialog box and select Number from the Category list, it doesn't get rid of the green triangles.

The reason I am bothered about this is because it means the Vlookups in my spreadsheet aren't working - it seems that the Vlookups are trying to find numbers, but because the numbers are stored as text, it isn't recognising them. Therefore, all the Vlookups are returning #N/A.

This is a regular process, so it would be good if there was an automated solution to the problem of the numbers being stored as text. I can't change the data formats directly in Access, as this would affect other data.

Does anyone have any ideas on how to resolve this? Thank you in advance for any help.

[StephenR]

Try selecting the cells and then running this code:

Please Login or Register  to view this content.

[LemonTwist]

Thank you for your suggestion - it works a treat!

[kevingb]

Worked for me to! Simples!! Thanks!!!