Coverting from 3 letters to 1

P

Pookie76

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
 
D

Dave Peterson

If you don't get a helpful reply, you may want to describe what you're trying to
do.

Maybe some examples of what you start with and what you should end with.

And what function you're using--you posted two functions.
 
P

Pookie76

Dave said:
If you don't get a helpful reply, you may want to describe what you'r
trying to
do.

Maybe some examples of what you start with and what you should en
with.

And what function you're using--you posted two functions.
Click to expand...

The first function coverts a 1 letter code to a 3 letter code. Th
second function coverts the 3 letter code into a sum of numerica
values.
Ie.
If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in
sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value become
6.

Thanks
 
D

Dave Peterson

Do you base that sum on the original value (ABC) or do you have a table that
shows the numeric equivalent for ABA, BCA, CAB, etc?
 
P

Pookie76

Sheet 2 has 3 rows.
column A is for the 1 letter code, column b is the 3 letter code and
column c in the value.
 
D

Dave Peterson

Then maybe it's as simple as looking at columns B:C.

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)
'next line changed
Set LookUpTable = Worksheets("sheet2").Range("b:c")

myValue = 0
'next few lines changed
For iCtr = 1 To Len(rng.Value) step 3
res = Application.VLookup(Mid(rng.Value, iCtr, 3), LookUpTable, 2, 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
 
Top