Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4919
  • Last Modified:

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.
1 Solution
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!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now