Visual Basic Arrays - Populating with data using a loop


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.
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Calvin BrineCommented:
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

skinnylAuthor Commented:
Perfect, just what i was after..

Cheers for your input anyhow Cbrine!
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.