Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    populate array with letters of alphabet (VBA excel 2002 SP2)

    What is the most efficient way of populating an arrary myarray(26,1) with the letters of the alphabet so that

    myarray(1,1)="A"
    myarray(2,1)="B"
    .
    myarray(26,1)="Z"


    Is it possible to vary the order so that cycle starts at different point?

    myarray(11,1)="A"
    myarray(12,1)="B"
    .
    myarray(10,1)="Z"

    I realise that I could create 26 lines of code, 1 for each letter but was hoping that there is another way, more like

    for x=1 to 9
    myarray(x,1)=x
    next x


    thanks

    Simon

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate array with letters of alphabet (VBA excel 2002 SP2)

    For the first half of your question try:

    For intLoop = 1 to 26
    MyArray(intLoop,1) = chr$(64+intLoop)
    Next

    or if you want lowercase letters use

    For intLoop = 1 to 26
    MyArray(intLoop,1) = chr$(96+intLoop)
    Next

    As for the second half of your question, I can't think of any easy way at the moment. I'm having a "mental code block" as it were. I'm sure there are a couple of ways of doing it, I just can't think at the moment.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate array with letters of alphabet (VBA excel 2002 SP2)

    OK, as soon as I clicked post it, my brain started to work.

    Try this for an offset fill:

    For intLoop = 1 To 26
    Select Case intLoop + intOffset
    Case Is <= 26
    myArray(intLoop, 1) = Chr$(64 + intLoop + intOffset)
    Case Else
    myArray(intLoop, 1) = Chr$(64 + intLoop + intOffset - 26)
    End Select
    Next


    where intOffset is an integer that specifies how many characters to offset the start of the list.

    0 = A B C ... X Y Z
    1 = B C D ... Y Z A
    2 = C D E ... Z A B
    ...
    25 = Z A B ... W X Y
    26 = A B C ... X Y Z

    intOffset also needs to be in the range of 0 - 26 otherwise the code probably won't work.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate array with letters of alphabet (VBA excel 2002 SP2)

    Excellent job,

    I got caught up trying to make for x = a to z work and missed the obvious!!

    We should all let are brains warm up before doing this kind of stuff..

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate array with letters of alphabet (VBA excel 2002 SP2)

    Glad to help.

    Even though my brain was asleep at the wheel for a bit.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate array with letters of alphabet (VBA excel 2002 SP2)

    Or, slightly more simply:

    <pre>Dim I As Integer, iOffset As Integer
    Dim strMyArray(1 To 26, 1 To 1) As String
    iOffset = 10
    For I = 1 To 26
    strMyArray(I, 1) = Chr(Asc("A") + ((I - 1 + iOffset) Mod 26))
    Next I
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •