Visual Basic Arrays - Populating with data using a loop

Posted on 2007-10-11
Last Modified: 2012-05-05

I'm in the middle of writing a macro to do some automatic updating from excel to powerpoint. the macro is housed in excel. i need to be able to reference cell numbers which will obviously change through out the program.

i'm currently using this to populate an array A-Z
   Dim aAlphabet() As String
   Dim iLetter As Integer
   ReDim aAlphabet(0)
   Dim sLetter As String
   Dim iElement As Integer

'Fill the array with the alphabet
For iLetter = 1 To 26

ReDim Preserve aAlphabet(iLetter)

sLetter = Chr(64 + iLetter)

aAlphabet(iLetter) = sLetter

Next iLetter

this works fine, and i can call and loop the array to give the cells i need, however.. i also need to reference cells up to GN :) not sure about how to do this.. any tips?

Cheers people.
Question by:skinnyl
    LVL 142

    Accepted Solution

    as you want to do this in Excel, use Excel stuff:

    For iLetter = 1 To 26

    ReDim Preserve aAlphabet(iLetter)
       sLetter = replace(replace(ActiveSheet.Cells(1,1).offset(0,iLetter-1).address, "$", ""), "1", "")
      aAlphabet(iLetter) = sLetter
    Next iLetter

    LVL 16

    Expert Comment

    by:Calvin Brine
    The easiest way to reference cells in a rangem, which is what I think your ultimate goal is, is this.

    Dim cell as range
    for each cell in activesheet.range("A1:GU500')
    msgbox cell.address
    next cell


    Author Comment

    Perfect, just what i was after..

    Cheers for your input anyhow Cbrine!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now