Loop through variables in VBA

I have to provide a code for a user's name.  So this is how it's suppose to be done.
 A = 01
 B =02
 C= 03  etc...
 D = 04
 E = 05
So when a user types in Abbott as a last name, the code generated in a form field = 0102

If the last name was Beatle the code would be 0205  

Anyone?
TimKestermontAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:

when do u want to call it, on the afterupdate event of txtlastname?

private sub txtlastname_afterupdate
    CodeNumber.value = GetCodeForName(txtlastname.value)
end sub

0
 
rockiroadsCommented:
u could try this


Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer

    For i = 1 To Len(sName)
        sNewName = sNewName & Format(Asc(Mid$(sName, i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function


test function
Public Sub TestIt()
    msbox GetCodeForName("ABC")
End Sub
       
0
 
jefftwilleyCommented:
You'll need a table
2 columns
Letter   |   Value
a               01
b               02

etc...

You'll need a form. Two unbound text boxes and a button
textbox one where you will enter the name
the button you will press to get the code
textbox two where the code will show up

Code here in a minute..got called away for a sec.
J

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
rockiroadsCommented:
urm, slight change

if u want ABC to return same results as abc

Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer

    For i = 1 To Len(sName)
        sNewName = sNewName & Format(Asc(Mid$(UCase$(sName), i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function



0
 
jefftwilleyCommented:
nevermind...good paste rock!! dont you have any real work to do? hehehe
J
0
 
rockiroadsCommented:
looking at your question, is it just the first two letters you want coding?

if so, then a small change

Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer
    dim iMaxLen as integer

    iMaxLen = 2
    if len(sName) < iMaxLen then iMaxLen = len(sName)
    For i = 1 To iMaxLen
        sNewName = sNewName & Format(Asc(Mid$(UCase$(sName), i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function
0
 
rockiroadsCommented:
come on Jeff, I told u to be quick!
0
 
TimKestermontAuthor Commented:
rockiroads

If txtlastname is on the form and CodeNumber is on the form, how does that translate into your formula
0
 
TimKestermontAuthor Commented:
Thanks,

Works like a charm.  Quick and easy, just like I like it.  

TK
0
 
rockiroadsCommented:
No probs
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.