# Combination with criteria

#### tonysmile

##### New Member
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.
 A B C D E F Row 1 1 2 3 4 5 6 Row 2 1 2 3 4 5 7

<tbody>
</tbody>

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.

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.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Assuming that a) you're happy to have a single piece of code, b) you don't want to display the rejected combinations, then this seems achievable.

You need a variable to count the row number, and 6 variables with which to loop round the numbers from 1 to 45.

Nest the 6 loops which populate columns A-F, then in the lowest one, check if your various criteria are being met. If they are, increase the row counter. If not, don't - the next loop will then overwrite the rejected one.

Once the loops have completed, re-check the last line (in case it should be rejected) and clear it if necessary.

single piece of code preferred!

I should have added that my programming skills are poor and I only have a basic understanding of coding so this has made everything harder.

It would be amazing if someone could provide me with the code for the macro

Sorry if it sounds patronising, but there's never a bad time to start/practise one's coding!

Build it up in modular sections - do one loop, then do another. Write the filter criteria, check it works, then drop it in.

Replies
15
Views
450
Replies
1
Views
197
Replies
2
Views
192
Replies
1
Views
585
Replies
3
Views
130

1,221,206
Messages
6,158,515
Members
451,497
Latest member
something68

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back