Visual Basic Arrays - Populating with data using a loop

Hi,

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.
skinnylAsked:
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

0
 
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

HTH
Cal
0
 
skinnylAuthor Commented:
Perfect, just what i was after..

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