Complicated VBA with 2 Conditions, 1 = Row target Sum, 2= Single number in the row

[motilulla]

Complicated VBA with 2 Conditions, 1 = Row target Sum, 2= Single number in the row</SPAN></SPAN>

Hello,</SPAN></SPAN>

I am looking some specific solution I will try my best to explain here, if it is not clear. Please ask any question I will try to make it clearer</SPAN></SPAN>


There is total 9 Numbers, which are as follow---> 1, 2, 3, 4, 5, 6, 7, 8, and 9, Column A</SPAN></SPAN>

Out of 9 numbers any quantity of numbers can be used in the row to make combinations of 7 in the each rows in the columns D:J</SPAN></SPAN>

I want also a VBA that can apply the following conditions...</SPAN></SPAN>

Condition 1- as per given example row target sum could be selected as desired in the column K (in this example selected sum = 33)</SPAN></SPAN>

Condition 2- as per example given single number 0 to 7 can be used desired in the column L </SPAN></SPAN>
Explanations of condition-2, for examples...</SPAN></SPAN>
L4=0 in the row D4:J4 used 1=3 times, 7=2 times, 8=2 times (Used Single number 0 time)</SPAN></SPAN>

L5=1 in the row D5:J5 used 1=2 times, 4=2 times, 9=2times (5 is used as a single number so far 1 number is single used) same condition is applied to row L5:L11</SPAN></SPAN>

L12=2 in the row D12:J12 used 1=2 times, 8=3 times (3 & 4 is used as a single number so far 2 numbers are single used) same condition is applied to row L12:L18</SPAN></SPAN>

L19=3 in the row D12:J12 used 1=2 times, 9=2 times (3, 6 & 9 is used as a single number so far 3 numbers are single used) same condition is applied to row L19:L37</SPAN></SPAN>

And same you will see for 4, 5 & 7 </SPAN></SPAN>

Example </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2											Condition 1	Condition 2
3	Numbers			n1	n2	n3	n4	n5	n6	n7	Row Sum	Use Single Numbers In The Row
4	1			1	7	8	7	1	1	8	33	0
5	2			1	1	4	4	9	9	5	33	1
6	3			1	7	7	1	7	9	1	33	1
7	4			1	8	1	4	8	4	7	33	1
8	5			2	2	8	1	6	6	8	33	1
9	6			2	3	2	3	5	9	9	33	1
10	7			4	1	8	4	8	7	1	33	1
11	8			9	3	5	1	9	1	5	33	1
12	9			1	3	8	4	1	8	8	33	2
13				3	3	3	6	8	8	2	33	2
14				4	6	3	4	9	4	3	33	2
15				5	7	1	5	5	5	5	33	2
16				7	2	2	8	2	4	8	33	2
17				8	3	3	1	3	7	8	33	2
18				8	4	1	4	6	4	6	33	2
19				1	3	1	9	6	4	9	33	3
20				3	3	9	1	6	5	6	33	3
21				3	5	6	9	3	5	2	33	3
22				3	6	4	8	3	1	8	33	3
23				3	6	1	9	6	5	3	33	3
24				4	1	9	6	3	9	1	33	3
25				4	4	7	7	2	1	8	33	3
26				4	5	3	6	5	5	5	33	3
27				5	1	6	6	2	8	5	33	3
28				5	4	3	5	7	3	6	33	3
29				6	4	1	6	7	7	2	33	3
30				6	5	1	4	8	1	8	33	3
31				6	9	2	1	1	8	6	33	3
32				7	3	4	7	3	8	1	33	3
33				7	4	5	2	4	5	6	33	3
34				8	1	9	5	5	4	1	33	3
35				8	8	4	1	6	3	3	33	3
36				9	2	7	1	9	1	4	33	3
37				9	3	9	1	1	6	4	33	3
38				1	5	5	8	6	5	3	33	4
39				1	5	7	5	2	5	8	33	4
40				3	9	6	8	3	1	3	33	4
41				4	3	3	3	5	6	9	33	4
42				6	5	3	7	5	5	2	33	4
43				7	9	5	2	2	6	2	33	4
44				8	6	9	7	1	1	1	33	4
45				1	1	9	5	8	6	3	33	5
46				1	7	1	8	3	9	4	33	5
47				1	8	7	5	3	7	2	33	5
48				2	2	9	1	8	6	5	33	5
49				2	6	5	1	8	8	3	33	5
50				2	8	5	5	3	1	9	33	5
51				2	8	6	3	5	2	7	33	5
52				2	9	4	2	3	7	6	33	5
53				3	1	2	9	7	3	8	33	5
54				3	1	5	7	9	4	4	33	5
55				3	4	5	7	9	4	1	33	5
56				3	8	7	9	3	1	2	33	5
57				4	2	5	9	8	1	4	33	5
58				4	5	8	5	3	1	7	33	5
59				4	6	6	2	1	9	5	33	5
60				5	3	4	1	7	8	5	33	5
61				5	4	3	8	9	3	1	33	5
62				5	6	2	8	2	3	7	33	5
63				5	6	8	3	2	5	4	33	5
64				5	9	5	4	6	1	3	33	5
65				6	4	5	3	5	9	1	33	5
66				6	4	7	1	9	5	1	33	5
67				6	4	7	4	8	3	1	33	5
68				6	5	3	7	1	4	7	33	5
69				6	9	4	3	4	2	5	33	5
70				7	2	8	2	3	6	5	33	5
71				7	8	4	2	2	1	9	33	5
72				8	2	5	6	1	3	8	33	5
73				8	3	4	9	1	7	1	33	5
74				8	5	1	7	3	4	5	33	5
75				9	2	6	4	6	5	1	33	5
76				2	5	4	7	8	6	1	33	7
77				2	8	6	3	1	4	9	33	7
78				4	5	7	1	6	2	8	33	7
79				5	6	2	7	8	4	1	33	7
80				6	2	7	5	8	1	4	33	7
81				6	5	4	1	7	2	8	33	7
82				8	4	6	7	1	5	2	33	7
83				9	4	8	1	6	3	2	33	7
84
85
Sheet6

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>

 

[MickG]

Try this:-
NB:- This code takes approx. 10secs to run!!!
Assuming I understand your requirements then:-
This code is based on conditions in columns "K & L".( These values are required )
The code runs to row 83 !!!

[COLOR="Navy"]Sub[/COLOR] MG16Nov04
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cols [COLOR="Navy"]As[/COLOR] Variant
cols = Array(6, 43, 38, 39, 5, 3, 8, 40, 10)
Application.ScreenUpdating = False

[COLOR="Navy"]For[/COLOR] Rw = 4 To 83
Tot = 0
Randomize
[COLOR="Navy"]Do[/COLOR] Until Tot = Cells(Rw, 11) And oMin = Cells(Rw, 12)
    Tot = 0: oMin = 0
    [COLOR="Navy"]For[/COLOR] n = 1 To 7
        Num = Int(Rnd * 9) + 1
        Tot = Tot + Num
        Cells(Rw, n + 3) = Num
        Cells(Rw, n + 3).Font.ColorIndex = 1
        Cells(Rw, n + 3).Interior.ColorIndex = cols(Num - 1)
        [COLOR="Navy"]If[/COLOR] Num = 5 Or Num = 6 Or Num = 9 [COLOR="Navy"]Then[/COLOR]
            Cells(Rw, n + 3).Font.ColorIndex = 2
        [COLOR="Navy"]Else[/COLOR]
            Cells(Rw, n + 3).Font.ColorIndex = 1
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] Ac = 4 To 10
    oMin = oMin + IIf(Application.CountIf(Cells(Rw, 4).Resize(, 10), Cells(Rw, Ac)) = 1, 1, 0)
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]Next[/COLOR] Rw
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[motilulla]

Try this:-
NB:- This code takes approx. 10secs to run!!!
Assuming I understand your requirements then:-
This code is based on conditions in columns "K & L".( These values are required )
The code runs to row 83 !!!
Regards Mick

MickG, I want to thank you for looking a my request that is really complicated, I done various test it seems condition-1 in the column K works perfect but condition-2 in the column L fails every time I run the code it gives different results both conditions values are there in the column K & L</SPAN></SPAN>

To check I have created in column O:Y to test the output results </SPAN></SPAN>
Formula in O4 =COUNTIF($D4:$J4,O$3) across & down to W83 </SPAN></SPAN>
Formula in X4 =SUM(D4:J4) down to X83</SPAN></SPAN>
Formula in Y4 =COUNTIF(O4:W4,1)</SPAN></SPAN>

Here is the result.... Use Single Numbers In The Row does not match </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2											Condition 1	Condition 2												Condition 1	Condition 2
3	Numbers			n1	n2	n3	n4	n5	n6	n7	Row Sum	Use Single Numbers In The Row			1	2	3	4	5	6	7	8	9	Row Sum	Use Single Numbers In The Row
4	1			5	5	7	7	2	2	5	33	0			0	2	0	0	3	0	2	0	0	33	0
5	2			6	4	4	6	2	2	9	33	1			0	2	0	2	0	2	0	0	1	33	1
6	3			6	8	3	6	1	3	6	33	1			1	0	2	0	0	3	0	1	0	33	2
7	4			8	1	3	7	3	8	3	33	1			1	0	3	0	0	0	1	2	0	33	2
8	5			1	9	1	9	3	7	3	33	1			2	0	2	0	0	0	1	0	2	33	1
9	6			1	4	1	7	4	7	9	33	1			2	0	0	2	0	0	2	0	1	33	1
10	7			7	2	6	6	7	2	3	33	1			0	2	1	0	0	2	2	0	0	33	1
11	8			4	1	4	4	9	9	2	33	1			1	1	0	3	0	0	0	0	2	33	2
12	9			2	9	4	9	1	1	7	33	2			2	1	0	1	0	0	1	0	2	33	3
13				5	8	3	5	7	3	2	33	2			0	1	2	0	2	0	1	1	0	33	3
14				4	4	7	7	3	6	2	33	2			0	1	1	2	0	1	2	0	0	33	3
15				2	1	8	7	1	8	6	33	2			2	1	0	0	0	1	1	2	0	33	3
16				8	5	4	2	3	3	8	33	2			0	1	2	1	1	0	0	2	0	33	3
17				3	4	7	3	3	4	9	33	2			0	0	3	2	0	0	1	0	1	33	2
18				1	7	4	4	7	3	7	33	2			1	0	1	2	0	0	3	0	0	33	2
19				1	6	2	8	9	1	6	33	3			2	1	0	0	0	2	0	1	1	33	3
20				6	5	5	6	2	1	8	33	3			1	1	0	0	2	2	0	1	0	33	3
21				7	6	2	7	2	4	5	33	3			0	2	0	1	1	1	2	0	0	33	3
22				5	5	6	3	7	2	5	33	3			0	1	1	0	3	1	1	0	0	33	4
23				8	1	2	6	9	6	1	33	3			2	1	0	0	0	2	0	1	1	33	3
24				4	4	6	3	5	7	4	33	3			0	0	1	3	1	1	1	0	0	33	4
25				6	2	1	7	5	7	5	33	3			1	1	0	0	2	1	2	0	0	33	3
26				1	1	5	4	7	8	7	33	3			2	0	0	1	1	0	2	1	0	33	3
27				3	6	6	5	1	9	3	33	3			1	0	2	0	1	2	0	0	1	33	3
28				3	2	5	8	3	7	5	33	3			0	1	2	0	2	0	1	1	0	33	3
29				5	2	5	3	9	4	5	33	3			0	1	1	1	3	0	0	0	1	33	4
30				3	6	4	6	7	1	6	33	3			1	0	1	1	0	3	1	0	0	33	4
31				2	6	5	5	7	3	5	33	3			0	1	1	0	3	1	1	0	0	33	4
32				6	2	2	7	6	9	1	33	3			1	2	0	0	0	2	1	0	1	33	3
33				2	5	4	2	6	5	9	33	3			0	2	0	1	2	1	0	0	1	33	3
34				5	1	6	5	1	8	7	33	3			2	0	0	0	2	1	1	1	0	33	3
35				2	1	9	2	9	6	4	33	3			1	2	0	1	0	1	0	0	2	33	3
36				9	6	1	1	7	7	2	33	3			2	1	0	0	0	1	2	0	1	33	3
37				2	8	7	1	1	6	8	33	3			2	1	0	0	0	1	1	2	0	33	3
38				2	9	3	2	2	7	8	33	4			0	3	1	0	0	0	1	1	1	33	4
39				4	6	5	3	4	7	4	33	4			0	0	1	3	1	1	1	0	0	33	4
40				1	1	7	4	3	8	9	33	4			2	0	1	1	0	0	1	1	1	33	5
41				9	3	2	2	5	8	4	33	4			0	2	1	1	1	0	0	1	1	33	5
42				6	5	8	6	1	3	4	33	4			1	0	1	1	1	2	0	1	0	33	5
43				6	3	4	9	5	1	5	33	4			1	0	1	1	2	1	0	0	1	33	5
44				4	1	5	5	6	9	3	33	4			1	0	1	1	2	1	0	0	1	33	5
45				8	9	5	3	5	1	2	33	5			1	1	1	0	2	0	0	1	1	33	5
46				9	1	7	3	8	2	3	33	5			1	1	2	0	0	0	1	1	1	33	5
47				9	7	1	4	3	3	6	33	5			1	0	2	1	0	1	1	0	1	33	5
48				4	7	6	2	1	4	9	33	5			1	1	0	2	0	1	1	0	1	33	5
49				2	4	8	7	8	3	1	33	5			1	1	1	1	0	0	1	2	0	33	5
50				7	1	3	9	2	8	3	33	5			1	1	2	0	0	0	1	1	1	33	5
51				3	7	4	8	1	8	2	33	5			1	1	1	1	0	0	1	2	0	33	5
52				1	9	8	3	5	2	5	33	5			1	1	1	0	2	0	0	1	1	33	5
53				2	9	7	4	6	4	1	33	5			1	1	0	2	0	1	1	0	1	33	5
54				3	4	7	5	5	1	8	33	5			1	0	1	1	2	0	1	1	0	33	5
55				7	7	1	3	9	2	4	33	5			1	1	1	1	0	0	2	0	1	33	5
56				9	7	3	4	7	2	1	33	5			1	1	1	1	0	0	2	0	1	33	5
57				4	1	9	5	5	2	7	33	5			1	1	0	1	2	0	1	0	1	33	5
58				2	3	1	8	7	6	6	33	5			1	1	1	0	0	2	1	1	0	33	5
59				4	2	2	7	9	3	6	33	5			0	2	1	1	0	1	1	0	1	33	5
60				7	4	9	6	1	2	4	33	5			1	1	0	2	0	1	1	0	1	33	5
61				3	6	4	8	4	7	1	33	5			1	0	1	2	0	1	1	1	0	33	5
62				7	4	1	3	9	7	2	33	5			1	1	1	1	0	0	2	0	1	33	5
63				5	1	5	2	9	7	4	33	5			1	1	0	1	2	0	1	0	1	33	5
64				9	3	2	6	4	7	2	33	5			0	2	1	1	0	1	1	0	1	33	5
65				9	7	4	1	1	3	8	33	5			2	0	1	1	0	0	1	1	1	33	5
66				3	5	2	6	4	8	5	33	5			0	1	1	1	2	1	0	1	0	33	5
67				1	3	9	3	7	6	4	33	5			1	0	2	1	0	1	1	0	1	33	5
68				4	1	7	9	6	2	4	33	5			1	1	0	2	0	1	1	0	1	33	5
69				4	3	2	7	1	9	7	33	5			1	1	1	1	0	0	2	0	1	33	5
70				7	9	6	3	3	4	1	33	5			1	0	2	1	0	1	1	0	1	33	5
71				2	4	6	3	9	2	7	33	5			0	2	1	1	0	1	1	0	1	33	5
72				4	8	5	1	5	3	7	33	5			1	0	1	1	2	0	1	1	0	33	5
73				7	9	7	1	4	2	3	33	5			1	1	1	1	0	0	2	0	1	33	5
74				5	9	8	1	2	5	3	33	5			1	1	1	0	2	0	0	1	1	33	5
75				7	3	4	8	2	1	8	33	5			1	1	1	1	0	0	1	2	0	33	5
76				2	4	6	3	1	8	9	33	7			1	1	1	1	0	1	0	1	1	33	7
77				1	2	9	3	4	6	8	33	7			1	1	1	1	0	1	0	1	1	33	7
78				6	4	2	3	9	8	1	33	7			1	1	1	1	0	1	0	1	1	33	7
79				9	8	1	6	3	2	4	33	7			1	1	1	1	0	1	0	1	1	33	7
80				8	6	1	9	4	3	2	33	7			1	1	1	1	0	1	0	1	1	33	7
81				2	1	3	8	4	6	9	33	7			1	1	1	1	0	1	0	1	1	33	7
82				2	6	1	8	9	3	4	33	7			1	1	1	1	0	1	0	1	1	33	7
83				1	2	9	3	8	6	4	33	7			1	1	1	1	0	1	0	1	1	33	7
84
85
Sheet7

Here is another simplified example also does not match may this help</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2											Condition 1	Condition 2												Condition 1	Condition 2
3	Numbers			n1	n2	n3	n4	n5	n6	n7	Row Sum	Use Single Numbers In The Row			1	2	3	4	5	6	7	8	9	Row Sum	Use Single Numbers In The Row
4	1			2	3	9	4	4	1	4	27	3			1	1	1	3	0	0	0	0	1	27	4
5	2			4	5	4	2	9	1	2	27	3			1	2	0	2	1	0	0	0	1	27	3
6	3			6	5	5	1	4	3	3	27	3			1	0	2	1	2	1	0	0	0	27	3
7	4			6	4	5	1	3	3	5	27	3			1	0	2	1	2	1	0	0	0	27	3
8	5			4	7	3	8	1	3	1	27	3			2	0	2	1	0	0	1	1	0	27	3
9	6			2	3	4	1	9	4	4	27	3			1	1	1	3	0	0	0	0	1	27	4
10	7			1	3	1	9	5	7	1	27	3			3	0	1	0	1	0	1	0	1	27	4
11	8			2	7	4	3	3	1	7	27	3			1	1	2	1	0	0	2	0	0	27	3
12	9			5	7	9	1	1	3	1	27	3			3	0	1	0	1	0	1	0	1	27	4
13				3	4	5	4	1	3	7	27	3			1	0	2	2	1	0	1	0	0	27	3
14				3	7	1	1	5	1	9	27	3			3	0	1	0	1	0	1	0	1	27	4
15				4	6	7	2	3	3	2	27	3			0	2	2	1	0	1	1	0	0	27	3
16				3	3	9	7	2	2	1	27	3			1	2	2	0	0	0	1	0	1	27	3
17				6	5	3	4	2	3	4	27	3			0	1	2	2	1	1	0	0	0	27	3
18				2	2	1	8	2	9	3	27	3			1	3	1	0	0	0	0	1	1	27	4
19				3	2	1	7	9	3	2	27	3			1	2	2	0	0	0	1	0	1	27	3
20				2	2	3	8	4	6	2	27	3			0	3	1	1	0	1	0	1	0	27	4
21				1	2	5	6	7	5	1	27	3			2	1	0	0	2	1	1	0	0	27	3
22				1	4	1	9	4	2	6	27	3			2	1	0	2	0	1	0	0	1	27	3
23				9	2	2	8	1	3	2	27	3			1	3	1	0	0	0	0	1	1	27	4
24				4	1	5	2	1	5	9	27	3			2	1	0	1	2	0	0	0	1	27	3
25				9	2	1	5	1	4	5	27	3			2	1	0	1	2	0	0	0	1	27	3
26				3	9	3	3	5	3	1	27	3			1	0	4	0	1	0	0	0	1	27	3
27				1	2	1	5	2	9	7	27	3			2	2	0	0	1	0	1	0	1	27	3
28				1	9	2	1	2	5	7	27	3			2	2	0	0	1	0	1	0	1	27	3
29				4	2	9	1	5	5	1	27	3			2	1	0	1	2	0	0	0	1	27	3
30				2	4	2	1	5	9	4	27	3			1	2	0	2	1	0	0	0	1	27	3
31				2	7	3	2	9	3	1	27	3			1	2	2	0	0	0	1	0	1	27	3
32				1	3	1	8	6	5	3	27	3			2	0	2	0	1	1	0	1	0	27	3
33				8	4	4	4	2	1	4	27	3			1	1	0	4	0	0	0	1	0	27	3
34				2	5	1	4	2	5	8	27	3			1	2	0	1	2	0	0	1	0	27	3
35				1	1	8	1	3	7	6	27	3			3	0	1	0	0	1	1	1	0	27	4
36				1	3	2	7	9	2	3	27	3			1	2	2	0	0	0	1	0	1	27	3
37				9	3	3	5	1	3	3	27	3			1	0	4	0	1	0	0	0	1	27	3
38				2	4	7	7	1	1	5	27	3			2	1	0	1	1	0	2	0	0	27	3
39				1	1	6	8	1	9	1	27	3			4	0	0	0	0	1	0	1	1	27	3
40				2	8	2	4	6	4	1	27	3			1	2	0	2	0	1	0	1	0	27	3
41				1	1	9	4	6	2	4	27	3			2	1	0	2	0	1	0	0	1	27	3
42				2	4	8	2	3	2	6	27	3			0	3	1	1	0	1	0	1	0	27	4
43				4	3	2	7	3	2	6	27	3			0	2	2	1	0	1	1	0	0	27	3
44				3	5	3	6	4	5	1	27	3			1	0	2	1	2	1	0	0	0	27	3
45				3	3	2	6	5	4	4	27	3			0	1	2	2	1	1	0	0	0	27	3
46				3	2	6	2	7	4	3	27	3			0	2	2	1	0	1	1	0	0	27	3
47				4	5	5	2	1	9	1	27	3			2	1	0	1	2	0	0	0	1	27	3
48				6	5	3	4	2	3	4	27	3			0	1	2	2	1	1	0	0	0	27	3
49				3	1	1	1	8	7	6	27	3			3	0	1	0	0	1	1	1	0	27	4
50				7	8	2	2	1	6	1	27	3			2	2	0	0	0	1	1	1	0	27	3
51				6	2	2	2	8	5	2	27	3			0	4	0	0	1	1	0	1	0	27	3
52				4	5	2	1	2	4	9	27	3			1	2	0	2	1	0	0	0	1	27	3
53				5	1	5	5	6	3	2	27	3			1	1	1	0	3	1	0	0	0	27	4
54				2	6	4	2	2	8	3	27	3			0	3	1	1	0	1	0	1	0	27	4
55				1	2	4	4	5	6	5	27	3			1	1	0	2	2	1	0	0	0	27	3
56				4	1	6	9	4	1	2	27	3			2	1	0	2	0	1	0	0	1	27	3
57				2	4	5	2	3	9	2	27	3			0	3	1	1	1	0	0	0	1	27	4
58				1	2	8	2	4	9	1	27	3			2	2	0	1	0	0	0	1	1	27	3
59				2	6	1	7	2	1	8	27	3			2	2	0	0	0	1	1	1	0	27	3
60				1	1	5	5	6	2	7	27	3			2	1	0	0	2	1	1	0	0	27	3
61				3	1	6	1	7	1	8	27	3			3	0	1	0	0	1	1	1	0	27	4
62				2	2	1	9	8	3	2	27	3			1	3	1	0	0	0	0	1	1	27	4
63				1	4	6	9	2	4	1	27	3			2	1	0	2	0	1	0	0	1	27	3
64				1	9	2	4	8	2	1	27	3			2	2	0	1	0	0	0	1	1	27	3
65				3	6	1	5	5	5	2	27	3			1	1	1	0	3	1	0	0	0	27	4
66				4	8	6	1	4	2	2	27	3			1	2	0	2	0	1	0	1	0	27	3
67				6	1	3	1	3	4	9	27	3			2	0	2	1	0	1	0	0	1	27	3
68				8	6	2	2	1	7	1	27	3			2	2	0	0	0	1	1	1	0	27	3
69				2	4	5	3	3	4	6	27	3			0	1	2	2	1	1	0	0	0	27	3
70				2	9	1	1	8	2	4	27	3			2	2	0	1	0	0	0	1	1	27	3
71				4	6	5	1	3	4	4	27	3			1	0	1	3	1	1	0	0	0	27	4
72				1	5	7	2	7	1	4	27	3			2	1	0	1	1	0	2	0	0	27	3
73				6	2	2	3	2	8	4	27	3			0	3	1	1	0	1	0	1	0	27	4
74				7	3	4	2	1	3	7	27	3			1	1	2	1	0	0	2	0	0	27	3
75				2	7	3	4	7	1	3	27	3			1	1	2	1	0	0	2	0	0	27	3
76				1	8	1	4	2	2	9	27	3			2	2	0	1	0	0	0	1	1	27	3
77				4	3	4	5	7	3	1	27	3			1	0	2	2	1	0	1	0	0	27	3
78				3	2	6	4	5	4	3	27	3			0	1	2	2	1	1	0	0	0	27	3
79				3	8	5	2	5	1	3	27	3			1	1	2	0	2	0	0	1	0	27	3
80				2	1	7	1	6	8	2	27	3			2	2	0	0	0	1	1	1	0	27	3
81				2	8	4	1	1	9	2	27	3			2	2	0	1	0	0	0	1	1	27	3
82				1	3	1	9	7	1	5	27	3			3	0	1	0	1	0	1	0	1	27	4
83				3	6	2	7	3	3	3	27	3			0	1	4	0	0	1	1	0	0	27	3
84
85
Sheet8

Please could you take a look? </SPAN></SPAN>



Regards,</SPAN>
Moti</SPAN>

 

[MickG]

Try changing the "10" to a "7" (Counting error), in line below:-

oMin = oMin + IIf(Application.CountIf(Cells(Rw, 4).Resize(,[COLOR="#FF0000"][B][SIZE=4] 7), [/SIZE][/B][/COLOR]Cells(Rw, Ac)) = 1, 1, 0)

 

[motilulla]

Try changing the "10" to a "7" (Counting error), in line below:-

oMin = oMin + IIf(Application.CountIf(Cells(Rw, 4).Resize(,[COLOR=#ff0000][B][SIZE=4] 7), [/SIZE][/B][/COLOR]Cells(Rw, Ac)) = 1, 1, 0)

Amazing Mick, I am sure this were a difficult and challenging task to code it as per request. Hats off to you! As you said changing the "10" to a "7" macro worked great it return all results as per both conditions in columns "K & L" I am very happy to see the results </SPAN></SPAN>

I appreciate your time and effort to help me out.</SPAN></SPAN>

Have a great weekend and good luck to you.</SPAN></SPAN>

Kind regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[MickG]

You're very wecome