Help with Macro error

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

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