Re: Convert 1 Character Code to a word

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dave Peterson (ec35720_at_msn.com)
Date: 08/12/04 

Date: Wed, 11 Aug 2004 20:55:15 -0500

First, you could use column C of that table work***. 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

"Pookie76 <" wrote:
>
> Dave Peterson wrote:
> > *Welcome to the world of VBA.
> >
> > You'll see the sun in a few years!
> > *
>
> Now i'm trying tot take the macro that you wrote and recreate an
> additional module so that =myConversion(A1) can be converted into a
> number by adding up all the variables.
>
> I created a 2nd table on *** 3 taking the 3 letter code of my 1
> letter codes and giving them each a number. Depending on the three
> letter code, the second module would add all the numbers up and it
> would be 1 whole number.
>
> I was going to do this by converting 3 letter code into a number, then
> convert that cell into a sum cell.
>
> I took the module that you wrote and changed three things.
>
> Option Explicit
> Function *myConversionA*(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("*sheet3"*).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
>
> *myConversionA* = myStr
>
> End Function
>
> The second table that I created is similar to the first one (1 letter
> code is in A and 3 letter code is in B) the second table is (3 letter
> code in A and the assigned number to it is in B)
>
> I don't get the NAME error but i do get:
>
> ?-?-?-?-?-?-?-?-?-?-?-?-?
>
> :confused:
>
> and I thought I was on a roll here. :rolleyes:
>
> Help...
>
> ---
> Message posted from http://www.ExcelForum.com/ 

-- 
Dave Peterson
ec35720@msn.com
Quantcast