Jan 4, 2012

I have some arrays to loop through some code which generates some graphs:

Code:

Sub demo()

Dim aServer

Dim aType

Dim i

aServer = Array("a", "b", "c")

aType = Array("1", "2", "3")

[code]....

I am now trying to populate the arrays dynamically and thought I could work that out myself, but this is my first ever time with VBA and it's not as easy as I thought (Or maybe I'm not as clever as I thought!)

I have a table in a worksheet with column headings and row headings which are the values to be added to the array.

Lets say I have the following:

Column headings = "x", "y", "z" (these are the potential aType values)

Row headings= "1", "2", "3" (these are the potential aServer values)

_,x, y, z

1

2

3

The values in the table are either TRUE or FALSE (Or blank which should be interpreted as FALSE). Where there is a TRUE the column heading should be added to the aServer array and the Row heading should be added to the aType array.

In this 9 cell example (Not counting the row and column headings), if the first 2 rows had all the values as TRUE (See example table below) it would therefore result in the following arrays:

_,x, y, z

1,TRUE,TRUE,TRUE

2,TRUE,TRUE,TRUE

3,FALSE,FALSE,FALSE

Code:

aServer = Array("1", "1", "1", "2", "2", "2")

aType = Array("x", "y", "z", "x", "y", "z")

If the table was as follows:

_,x, y, z

1,TRUE,FALSE,FALSE

2,FALSE,FALSE,FALSE

3,FALSE,TRUE,TRUE

It should result in the following arrays:

Code:

aServer = Array("1", "3", "3")

aType = Array("x", "y", "z")

this would work out how many rows and columns there were based on how many column headings and row headings there were (So I wouldn't have to change the hardcoded VBA when the table grows, therefore making it easier to share the spreadsheet with others)

View 3 Replies
View Related