create a table of all values (solved)

[pcc]

Anyone got code to create a table with a row for every combination of a set of variables?
eg if I have 3 years, 12 months in each year, and 2 categories in each month (say actual and budget), then I want a table to be created with a row for each year/month/category combination (ie 3x12x2=72 rows).
I have some code that does it via pivot tables, but if I use a lot of variables, Excel bombs out. I'm sure there's a simple way using arrays but don't want to reinvent the wheel if you guys already have done so.
Thanks.
This message was edited by pcc on 2003-02-13 10:50

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Years As Variant
    Dim Months As Variant
    Dim Cats As Variant
    Dim r As Integer
    Dim x As Integer
    Dim y As Integer
    Dim z As Integer
    Years = Array(2003, 2004, 2005)
    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Cats = Array(1, 2)
    r = 1
    With Worksheets("Sheet1")
        .Cells(r, 1) = "Year"
        .Cells(r, 2) = "Month"
        .Cells(r, 3) = "Category"
        For x = 1 To UBound(Years)
            For y = 1 To UBound(Months)
                For z = 1 To UBound(Cats)
                    r = r + 1
                    .Cells(r, 1) = Years(x)
                    .Cells(r, 2) = Months(y)
                    .Cells(r, 3) = Cats(z)
                Next z
            Next y
        Next x
    End With
End Sub

 

[pcc]

Thanks very much once again!
I had to change the for next loops to
For x = 0 to .... (not For x = 1 to ..)
and it works a treat

Regards

 

[Andrew Poulsom]

On 2003-02-13 05:11, pcc wrote:
Thanks very much once again!
I had to change the for next loops to
For x = 0 to .... (not For x = 1 to ..)
and it works a treat

Regards

I put Option Base 1 at the top of my code. Sorry, forgot to post it.

 

[pcc]

whats the syntax if the values in the array are not fixed?
eg I might have a column with a variable number of rows, and the array needs to be created based on all of the values in that column?
eg
HEADER
fred
bert
jim

need to generate from this:
name=Array("fred" , "bert" , "jim")

 

[Andrew Poulsom]

You can assign a range directly to a variant array using the Value property. If the range is in rows you will need to transpose it. Example:

Option Base 1

Sub Test1()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim MyArray As Variant
    Dim Msg As String
    Dim x As Integer
    Set Sh = Worksheets("Sheet2")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
    MyArray = WorksheetFunction.Transpose(Rng.Value)
    For x = 1 To UBound(MyArray)
        Msg = Msg & MyArray(x) & vbCrLf
    Next x
    MsgBox Msg
End Sub

Change the sheet and column references to suit.

 

[pcc]

Sorry to be a pain, but this is new code for me.
If one of the ranges only has one value in it, I get an error eg

.Cells(r, 5) = freqs(b)
gives 'subscript out of range' error

In this case, there is only one value, so freqs, although having been declared as an array, is an array of only one dimension.

If I can get this to work this will really be useful, so all help much appreciated.
Thanks

 

[Andrew Poulsom]

You can use the IsArray function like this:

Sub Test1()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim MyArray As Variant
    Dim Msg As String
    Dim x As Integer
    Set Sh = Worksheets("Sheet2")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
    MyArray = WorksheetFunction.Transpose(Rng.Value)
    If IsArray(MyArray) Then
        For x = 1 To UBound(MyArray)
'           Your code goes here
            Msg = Msg & MyArray(x) & vbCrLf
        Next x
    Else
'       Your code goes here
        Msg = MyArray
    End If
    MsgBox Msg
End Sub

 

[pcc]

Thanks Andrew. Trouble is that I've got (presently) 6 variables , and at any time any of them might be unique or might have multiple values. Also I am trying to develop a generic tool that can be applied to any number of variables each with any number of values. The code that you posted looks fine but it's going to be a bugger's muddle sorting out all the possible combinations. Still I'll press on regardless. Maybe I can figure out a way of doing this armed with this new code.
Thanks once again for your time.