chrisr01536
New Member
- Joined
- Jan 3, 2012
- Messages
- 7
With thanks to tlowry in a previous post, I have some arrays to loop through some code which generates some graphs:
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
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:
If that isn't challenging enough, ideally, 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)
I hope this is clear enough for someone to help me
Many many thanks in advance :D
Chris
Code:
Sub demo()
Dim aServer
Dim aType
Dim i
aServer = Array("a", "b", "c")
aType = Array("1", "2", "3")
For i = 0 To UBound(aServer)
vServer = aServer(i)
vType = aType(i)
Call Performance_charts_Master(vMnth, vYear)
Next i
End SubI 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")If that isn't challenging enough, ideally, 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)
I hope this is clear enough for someone to help me
Many many thanks in advance :D
Chris
