Increment letters with code

[George Too]

How do you increment letters using code?
For example, I have a For loop and just like I would do with numbers (i = i+1), I would like to increment letters in alphabetical order. If the loop starts with letter = "C", I want letter to increment to "D" then "F"...etc.

This is not possible by adding 1 to "C" because of type mismatch errors —I know, I tried it

Any ideas?

Thanks all,

George

 

[Jack Cowley]

Hmmm. Not sure what you want to do but Asc("C") returns 67 so you can use 67 and add 1 to that if you like... Chr(68) returns "D" so maybe you can use this information to get you started....

hth,
Jack

 

[raskew]

In concert with what Jack said, here are a couple of functions you can play with which demonstrate the asc() and chr() functions.

Function LtrLoop(startLtr As String, numtimes As Integer)
'*******************************************
'Name:      LtrLoop (Function)
'Purpose:   Given a starting letter, increment it numtimes
'           and show the result in the debug window
'Calls:     Function IsAlpha()
'Inputs:    ? LtrLoop("c", 10)
'Output:    See debug window
'*******************************************

Dim n As Integer
Dim msg As String
Dim strHold As String

If IsAlpha(startLtr) Then
   strHold = StrConv(startLtr, 1)
   For n = 0 To numtimes
      Debug.Print strHold
      strHold = Chr(Asc(strHold) + 1)
   Next n
   msg = "All done folks!"
Else
   msg = startLtr & " is not an alpha character.  Try again!"
End If
MsgBox msg, vbInformation, "In response to your enquiry..."
End Function

Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Name:      IsAlpha (Function)
'Purpose:   Determine if a character is alpha
'           i.e. "a" - "z" or "A" - "Z"
'Inputs:    ? IsAlpha("4"),
'Output:    False
'*******************************************

Dim i As Integer
i = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
    InStr("91 92 93 94 95 96", Asc(strIn)) = 0, 2, _
    True, 3)
IsAlpha = IIf(i <> 1, True, False)
End Function

 

[George Too]

My approach was similar to what Jack proposed. I was using Chr() function with the ascii code I wanted to start with and then just adding 1. It worked to a degree. But I'll try again and maybe implement the functions raskew posted.

Thanks guys.

George

 

[Jack Cowley]

Mr. Askew -

You do write some very nice functions and ScrubOMatic and RemoveOMatic are a couple of favorites! Just wanted you to know that you are a legend in your own time!

From another Senior (older) member,
Jack

 

[DukeCleo]

Try this function...

Public Function GetLetter(StartLetter As String, Loops As Integer) As String
Dim strAlpha As String
Dim i As Integer
Dim t As String

strAlpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

i = InStr(strAlpha, StartLetter)
t = StartLetter
For i = i To i + (Loops - 1)
t = Mid(strAlpha, InStr(strAlpha, t) + 1, 1)
Next
GetLetter = t

End Function


Example:

GetLetter("A",5) returns the letter "I"

Hope this helps,

Scott Miles

 

[WayneRyan]

Couldn't resist:

Ltr = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Ltr) + 1, 1)

Wayne

 

[raskew]

Thanks Jack for the kind words. You're my hero!

For those of you who don't know, Jack is apparently too modest to let us know that he has an alternate identity with an amazing 3832 posts (but who's counting?).

At the risk of being accused of campaigning for the competition, suggest you check-out UtterAccess at:
http://www.utteraccess.com/forums/ubbthreads.php?Cat=

Best wishes,

Bob

 

[Jack Cowley]

Bob -

Thank YOU for your kind words and the plug. It would not make us unhappy if we saw just a bit more off your smiling face, wit and erudite responses at the 'competition'. Clever code makers are hard to find...

Kind regards,
Jack

 

[George Too]

You guys are making me cry... I'm glad I started this...


By the way, thanks all for your replies. The code i'm using follows:

Row = 20
Column = Chr(67)
A = 0

For Each control In Forms![frmMain]![childHigh].controls
If control.ControlType = acTextBox Then
.range(Column & Row).Select
.Activecell = control.Tag
A = A + 1
Column = Chr(67 + A)
End If
Next control

It works perfectly!

Regards,
George