First, you could use column C of that table worksheet. Then retrieve the value
from there.
(I'm guessing that you will use the same input codes.)
Option Explicit
Function myConversionA(rng As Range) As Long
'returns a whole number???
' As Double
'if you have fractions
Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myValue As Long
Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:c")
myValue = 0
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 3, False)
If IsError(res) Then
'do nothing
Else
If IsNumeric(res) Then
myValue = myValue + res
End If
End If
Next iCtr
myConversionA = myValue
End Function
