Combination with criteria

[tonysmile]

Hello all!

I'm stuck... lots of searching and lots of dead ends. I'm hoping that someone here can help!
The macro I'm looking to build has 2 parts to it.

Here's what I would like to do:

PART 1 -
I would like to create a macro that gives me 6 numbers (which range from 1 to 45 and only occur once in the row) with 1 number in each cell.
The cells are A, B, C, D, E, F.
I would like the numbers to be printed in ascending order.
Once it has done this to then print the next combination in the row below. So on and so forth until all combinations have been done.

i.e.

	B	C	D	E	F	G
Row 1	1	2	3	4	5	6
Row 2	1	2	3	4	5	7

I know that there are over 5 million different combinations so I have put in some parameters to narrow it down and here is where it gets tricky...

PART 2 -
IF the sum of the combination is less than the value in cell H1 OR greater than the value in H2 then I want the macro to NOT print the result otherwise, print the result and move on to the next combination to check that it meets the criteria.

To add to the confusion...
IF the sum of the first 2 numbers of the combination is less than the value in cell H5 OR greater than the value in H6 then I want the macro to NOT print the result otherwise, print the result and move on to the next combination to check that it meets the criteria.

After every line printed onto the sheet, print the next line on the row below.

[nathansav]

I would use something like this, it picks a random number from 1 to 45, adds it to a collection. A collection errors if the data is already present, so we cant add 32 twice, so it goes and re-picks

Please Login or Register  to view this content.

[tonysmile]

A little more than that though... i need all 6 picked then the whole combination tested...

[Vikas_Gautam]

I have got almost to what you want..
Just select 6 cells and run the macro Named Rand_Nums()

Please Login or Register  to view this content.

Conditions...

Please Login or Register  to view this content.

Actually the first code runs beautifully but when I insert the conditions it giving errors ...
I guess... May be you have more realistic values in those cells ..
So you can give a try to the above condition statement by pasting it in the Original code..
I hope it works..

[nathansav]

Sorry . .