Coverting from 3 letters to 1

[Pookie76]

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.

[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.



Pookie76 wrote:
>
> 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.
>
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php...o&userid=12815
> View this thread: http://www.excelforum.com/showthread...hreadid=469014

--

Dave Peterson

[Pookie76]

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.

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

Thanks.

[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?



Pookie76 wrote:
>
> Dave Peterson Wrote:
> > 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.
> >
> >
>
> The first function coverts a 1 letter code to a 3 letter code. The
> second function coverts the 3 letter code into a sum of numerical
> values.
> Ie.
> If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in a
> sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value becomes
> 6.
>
> Thanks.
>
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php...o&userid=12815
> View this thread: http://www.excelforum.com/showthread...hreadid=469014

--

Dave Peterson

[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.

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?



Pookie76 wrote:
>
> Dave Peterson Wrote:
> > 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.
> >
> >
>
> The first function coverts a 1 letter code to a 3 letter code. The
> second function coverts the 3 letter code into a sum of numerical
> values.
> Ie.
> If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in a
> sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value becomes
> 6.
>
> Thanks.
>
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php...o&userid=12815
> View this thread: http://www.excelforum.com/showthread...hreadid=469014

--

Dave Peterson

[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

Pookie76 wrote:
>
> 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.
>
> Dave Peterson Wrote:
> > 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?
> >
> >
> >
> > Pookie76 wrote:
> > >
> > > Dave Peterson Wrote:
> > > > 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.
> > > >
> > > >
> > >
> > > The first function coverts a 1 letter code to a 3 letter code. The
> > > second function coverts the 3 letter code into a sum of numerical
> > > values.
> > > Ie.
> > > If you type in: ABC in A1. A2 results in Aba-Bca-Cab. A3 results in
> > a
> > > sum of the 3 letter codes. (Aba=1, Bca=2, Cab=3) so A3's value
> > becomes
> > > 6.
> > >
> > > Thanks.
> > >
> > > --
> > > Pookie76
> > >
> > ------------------------------------------------------------------------
> > > Pookie76's Profile:
> > http://www.excelforum.com/member.php...o&userid=12815
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=469014
> >
> > --
> >
> > Dave Peterson
>
> --
> Pookie76
> ------------------------------------------------------------------------
> Pookie76's Profile: http://www.excelforum.com/member.php...o&userid=12815
> View this thread: http://www.excelforum.com/showthread...hreadid=469014

--

Dave Peterson