Write macros to automatically Sort AND Convert Letters to Numbers

Hello, i am VERY new to writing vb in excel but have experience with writing in access so im familiar with the coding.

I want to write 1 macros that accomplishes the things below, this can either happen on automatic update of the spreadsheet or maybe a button insert and changes on click, whichever is easier. Thanks for the help!!!


1)I have an ID column (A1) called AMR AIS. I want to convert anything in this column that begins with a letter to a numeric value.
A=1; B=2, C=4, D=5, E=7, F=9 <--These are the only possible conversions. If it begins with a number, then dont do any conversions. Also, if it begins with an "x" or a "y" or "null" it should be deleted.
2) After the above conversion, I need to add "00" to the front of every value in the same column.
3) After step 2, last thing is to sort this column from lowest to highest


If anyone can please help me write a macros for these steps, if greatly appreciate it! Thanks!!

hello, thanks so much! however, im confused to where i should be putting this code. does it go straight into the worksheet, or do i create as a module or? thanks!!

hello, i ran it as a macros, and it errored out, it also did some weird sorting of the data as you can see in the picture attached. I dont want it filtered, just sorted. thanks!!

error1.jpg

hello, thanks for all the help. i downlaoded your copy and it does the conversion but it came up with the same erro message "code execution has been interuppted" i attached a picture of where the code stopped in attachment. im also attaching my copy of the spreadsheet. I noticed on yours, the letters were converted but on mine, they still did not convert. Thanks!!

error2.jpg
testdata2.xls

Thanks so much!! The only thing is, it deletes my row formula in my row count column. Is there a way to avoid this?

hrmm, i cant seem to get it to work right, sorry to keep asking for more but is there a way to code a column append to the macros? for example, can i add some code that will append a column called "Row Count" with the formula =row()-1 every time the macros is ran? THANKS!

or if this is easier, if theres a way to activate the formula in my selected cell to all the cells below it, within the macros. for example if my formula is in cell H1, when the macros run it also assigns the formula in that cell to all the cells beneath it?

awesome, thanks so much!