folks,
My niece has a small business and I help her with her annual inventory. She has always put the cost of her merchandise on her tags using a code Charleston. C =1, h=2, etc. I created a spreadsheet and we have several with laptops entering the code, retail price and quantity. ( I know a POS system would make this unnecessary and someday we'll get there).
I created a macro that should run through the sheet and convert the code to numerical value. For the most part it works. However there are some combinations that don't convert properly. These are the ones I've found so far: REL, CEL, AREL, LEL, ALE, AEL, AND HEL. An example of the error it returns is AEL should after conversion and decimal placement show 3.65, instead it shows 3000.
I know just enough to be dangerous. I can't figure out why it renders all correctly except for these exceptions. The macro I use is below. Any help would be greatly appreciated.
Thanks
Jim
Range("a2:a4000").Select

Selection.Replace What:="c", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="h", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="a", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="r", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="l", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="e", Replacement:="6", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="s", Replacement:="7", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="t", Replacement:="8", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="o", Replacement:="9", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="n", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.NumberFormat = "0.00"
Range("E9").Select
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Selection.OnAction = "Form"
Range("F6").Select
Range("C7").Select
End Sub