Hi all,

I have three numbers 10, 20 & 30 and I want all possible combinations of the
three numbers which I can get by generating a three dimentional table.

Has anyone got any ideas of how I could go about putting something like that
in excel.

Thanks in advance.

Pantelis

I think one of the two macros is what you are looking for.

The first lists all possible combinations of length, width
and height. The second restricts the list to only
dimentions that refer to unique shapes since length, width
and height are arbitrary designations.

Option Base 1 'Place at top of module

Sub Combinations1()
Dim i As Integer, ii As Integer, iii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
For iii = 1 To 3
Txt = Ar(i) & " x " & Ar(ii) & " x " & Ar(iii)
Range("A65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next iii
Next ii
Next i

End Sub

Sub Combinations2()
Dim i As Integer, ii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
Txt = Ar(i) & " x " & Ar(i) & " x " & Ar(ii)
Range("B65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next ii
Next i
End Sub

Done with minimal testing so check for accuracy.

Regards,
Greg



-----Original Message-----
Hi all,

I have three numbers 10, 20 & 30 and I want all possible
combinations of the
three numbers which I can get by generating a three
dimentional table.

Has anyone got any ideas of how I could go about putting
something like that
in excel.

Thanks in advance.

Pantelis


.

Greg,

Thanks for your suggestion, looks like it could work however the macro
stalls at
Txt = Ar(i) & " x " & Ar(ii) & " x " & Ar(iii)
any suggestons on how to fix.

thanks
Pantelis

"Greg Wilson" wrote in message
...
I think one of the two macros is what you are looking for.

The first lists all possible combinations of length, width
and height. The second restricts the list to only
dimentions that refer to unique shapes since length, width
and height are arbitrary designations.

Option Base 1 'Place at top of module

Sub Combinations1()
Dim i As Integer, ii As Integer, iii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
For iii = 1 To 3
Txt = Ar(i) & " x " & Ar(ii) & " x " & Ar(iii)
Range("A65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next iii
Next ii
Next i

End Sub

Sub Combinations2()
Dim i As Integer, ii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
Txt = Ar(i) & " x " & Ar(i) & " x " & Ar(ii)
Range("B65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next ii
Next i
End Sub

Done with minimal testing so check for accuracy.

Regards,
Greg



-----Original Message-----
Hi all,

I have three numbers 10, 20 & 30 and I want all possible
combinations of the
three numbers which I can get by generating a three
dimentional table.

Has anyone got any ideas of how I could go about putting
something like that
in excel.

Thanks in advance.

Pantelis


.

Hi Pantelis,

My assumption is that you didn't include the
declaration "Option Base 1" at the top of the module. You
must place this statement above the first listed procedure
in the module. This statement changes the default lower
bound for all arrays in the module from 0 to 1 without
having to specify them individually. In this case it lets
me refer to the first element in the array as Ar(1)
instead of Ar(0). This is my preference when working with
arrays.

If this assuption is correct then you should get the error
message: "Subscript out of range". If this is not the
case then what error message do you get or what exactly
happens? Note that I tested both macros before posting
and they worked (although I won't guarantee that they are
appropriate).

Regards,
Greg



-----Original Message-----
Greg,

Thanks for your suggestion, looks like it could work
however the macro
stalls at
Txt = Ar(i) & " x " & Ar(ii) & " x " & Ar(iii)
any suggestons on how to fix.

thanks
Pantelis

"Greg Wilson" wrote in message
...
I think one of the two macros is what you are looking
for.

The first lists all possible combinations of length,
width
and height. The second restricts the list to only
dimentions that refer to unique shapes since length,
width
and height are arbitrary designations.

Option Base 1 'Place at top of module

Sub Combinations1()
Dim i As Integer, ii As Integer, iii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
For iii = 1 To 3
Txt = Ar(i) & " x " & Ar(ii) & " x " & Ar
(iii)
Range("A65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next iii
Next ii
Next i

End Sub

Sub Combinations2()
Dim i As Integer, ii As Integer
Dim Ar As Variant, Txt As String
Ar = Array(10, 20, 30)

For i = 1 To 3
For ii = 1 To 3
Txt = Ar(i) & " x " & Ar(i) & " x " & Ar(ii)
Range("B65536").End(xlUp).Offset(1) = Txt
Txt = ""
Next ii
Next i
End Sub

Done with minimal testing so check for accuracy.

Regards,
Greg



-----Original Message-----
Hi all,

I have three numbers 10, 20 & 30 and I want all
possible
combinations of the
three numbers which I can get by generating a three
dimentional table.

Has anyone got any ideas of how I could go about
putting
something like that
in excel.

Thanks in advance.

Pantelis


.



.