I currently have a program that can convert codes from 1 letter to 3 an
a number that is involved with the letters. This is the macro.
MODULE
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
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 fro
3 to 1 instead of from 1 to 3 but every change I make is ruining th
macro. Thanks in advance
--
Pookie7
-----------------------------------------------------------------------
Pookie76's Profile:
http://www.excelforum.com/member.php...fo&userid=1281
View this thread:
http://www.excelforum.com/showthread.php?threadid=46901
Similar Threads
