Help with Macro error

jar208 · #1 12-04-2016, 08:27 PM

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

NoSparks · #2 12-05-2016, 12:28 PM

Hmmm... you're selecting column A for the replacements and then formatting column B to "0.00" ???

Assuming the alpha cost codes are in column A and you want the actual cost in the adjacent column B cell, this will do it.
Perhaps there's something you can use or adapt.

Code:

Sub ConvertToCost()

Dim cel As Range
Dim str As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error Resume Next    'in case value doesn't convert

For Each cel In Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
    If cel.Value <> "" And Not IsNumeric(cel.Value) Then
        str = LCase(Trim(cel.Value))
        cel.Offset(0, 1).Value = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace _
              (str, "c", 1), "h", 2), "a", 3), "r", 4), "l", 5), "e", 6), "s", 7), "t", 8), "o", 9), "n", 0) / 100
        cel.Offset(0, 1).NumberFormat = "0.00"
    End If
Next cel

On Error GoTo 0         'turn error check back on

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

jar208 · #3 12-06-2016, 03:57 PM

I'm not a programmer. I can identify some things and can edit this to work, but I could never create it. That is slick.
Thanks!
Jim

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Error in Macro to Add 2 Spaces After Closing Parenthesis	sleake	Word	2	09-16-2013 04:54 AM
Error on macro to insert endnotes on Mac	mbk	Word VBA	11	12-20-2012 01:03 AM
Simple VBA macro error	Formulayeti	PowerPoint	1	12-09-2011 10:02 PM
Why it works but the macro is error in VB?	tinfanide	Excel Programming	5	12-03-2011 12:53 AM
Macro Error 5174	muster36	Word VBA	0	08-12-2011 03:34 AM