I currently have a program that can convert codes from 1 letter to 3 and a number that is involved with the letters. This is the macro.
Module 1
Option Explicit
Function myConversion(rng As Range) As String
Dim res As Variant
Dim LookUpTable As Range
Dim iCtr As Long
Dim myStr As String
Set rng = rng(1)
Set LookUpTable = Worksheets("sheet2").Range("a:b")
myStr = ""
For iCtr = 1 To Len(rng.Value)
res = Application.VLookup(Mid(rng.Value, iCtr, 1), _
LookUpTable, 2, False)
If IsError(res) Then
myStr = myStr & "-?"
Else
myStr = myStr & "-" & res
End If
Next iCtr
If myStr <> "" Then
myStr = Mid(myStr, 2)
End If
myConversion = myStr
End Function
Module 2
Option Explicit
Function myConversionA(rng As Range) As Double
'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 Double
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
I've been playing around with the macro trying to get it to covert from 3 to 1 instead of from 1 to 3 but every change I make is ruining the macro. Thanks in advance.
Bookmarks