Find cycle with in 4 rounds

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000

Hi,

I need a macro, which look cycle for "1X2" within 4 rounds if not found go to next row and search within 4 next rounds

Data are in cells C6:C82
Step1-code start searching cycle for "1X2" in cells C6:C9 if cycle for "1X2" does not find continue to step-2

Setp-2 go to next 4 cycles in cells C7:C10 if cycle for "1X2" find insert 4 in to cell E7 and colour cells C7:C10 in green

Setp-3 go to next 4 cycles in cells C8:C11 if cycle for "1X2" does not find continue to step-4

Setp-4 go to next 4 cycles in cells C9:C12 if cycle for "1X2" find insert 4 in to cell E9 and colour cells C9:C12 in yellow (overleaping the colour C9:C10 cell will be change in yellow too)

And continue so on till end of the column c

Data example...


Book1
ABCDEF
1
2Find Cycle
3Within
44 Rounds
5C14
6X
7X4
8X
924
101
111
12X
131
141
151
16X
171
181
19X4
201
2114
222
231
24X
251
26X
271
281
291
301
311
321
332
3414
352
361
37X
38X
39X4
401
41X
4224
43X
44X4
451
4614
472
481
49X
50X
51X
52X
53X4
542
55X
561
57X4
581
591
602
61X
622
631
641
651
66X
67X
681
69X
701
711
721
731
741
7514
761
772
78X4
791
801
812
822
83
84
85
86
Sheet2


Thank you in advance

Regards,
Kishan
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Ok, so if I understand you. you need to evaluate the column C and find "x" and then evaluate the cell above it and the cell below it for a "1" and a "2" and if both are true meaning the cells are C6, C7, C8 = 1, x, 2 then you need to change the color and continue to search the column for the next set of "1,x,2" and do the same thing.

is that about right
 
Upvote 0
Drrellik said:
Ok, so if I understand you. you need to evaluate the column C and find "x" and then evaluate the cell above it and the cell below it for a "1" and a "2" and if both are true meaning the cells are C6, C7, C8 = 1, x, 2 then you need to change the color and continue to search the column for the next set of "1,x,2" and do the same thing.

is that about right
Click to expand...
Hi Drrellik,

I want set of "1,x,2" to be find with in 4 rows starting in C6, C7, C8, C9 set can be (1, 1, x, 2), (x, 1, x, 2), (1, 1, 2, x), (2, x, x, 1) cycle must be completed in 4 rounds or in other words within 4 rows there is no cycle in first 4 rows, so look in to next 4 cells C7, C8, C9, C10 yes it is there (x, x, 2, 1) set of "1,x,2" so colour these cells in green and place 4 in the cell E7, --->continue to cells C8, C9, C10, C11 set of "1,x,2" not found, --->continue to cells C9, C10, C11, C12 yes it is there (2, 1, 1, x) set of "1,x,2" so colour these cells in yellow and place 4 in the cell E9 (of course colouring cells C9, C10, C11, C12 in yellow C9 & C10 will be changed green to yellow due to overlapping yellow colour)

And will continue finding set of "1,x,2" next 4 rows C10, C11, C12, C13 and so on....

Thank you

Regards,
Kishan


 
Upvote 0
Why do rows 8,9,10 & 11 not qualify. ???

<tbody>
[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00FF00]#00FF00[/URL] , align: center"]X[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]1[/TD]

</tbody>



hh
 
Upvote 0
MickG said:
Why do rows 8,9,10 & 11 not qualify. ???

<tbody>
[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1
[/TD]

</tbody>
Click to expand...
Hi MickG, I missed the true part explanation; you caught the point. Important is the cycle, not the 4 signs in the 4 rows. But always within 4 rounds

For example: there are 3 characters 1, X & 2

If cycle start with "1" it has to closed by "X or 2" like (1, X, X, 2) (1, 2, 1, X) (1, X, X, 2) or (1, 2, 2, X)
If cycle start with "X" it has to closed by "1 or 2" like (X, X, 1, 2) (X, X, 1, 2) (X, 1, X, 2) or (X, 2, 2, 1)
If cycle start with "2" it has to closed by "1 or X" like (2, 1, 1, X) (2, X, X, 1) (2, 1, 2, X) or (2, X, X, 1)

Hope this help

Thank you

Regards,
Kishan
 
Last edited:
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG24Sep18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n3 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
Col = vbGreen
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    n1 = 0: n2 = 0: n3 = 0
    [COLOR="Navy"]For[/COLOR] n = 0 To 3
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Offset(n).value
            [COLOR="Navy"]Case[/COLOR] 1: n1 = 1
            [COLOR="Navy"]Case[/COLOR] 2: n2 = 2
            [COLOR="Navy"]Case[/COLOR] "X": n3 = 3
        [COLOR="Navy"]End[/COLOR] Select
        [COLOR="Navy"]If[/COLOR] Application.Product(n1, n2, n3) > 0 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] n = 3 And Application.Product(n1, n2, n3) > 0 [COLOR="Navy"]Then[/COLOR]
    Dn.Offset(, 2).value = 4
    Dn.Resize(4).Interior.Color = Col
    Col = IIf(Col = vbGreen, vbYellow, vbGreen)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=navy]Sub[/COLOR] MG24Sep18
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n1 [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] n2 [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] n3 [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
Col = vbGreen
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    n1 = 0: n2 = 0: n3 = 0
    [COLOR=navy]For[/COLOR] n = 0 To 3
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] Dn.Offset(n).value
            [COLOR=navy]Case[/COLOR] 1: n1 = 1
            [COLOR=navy]Case[/COLOR] 2: n2 = 2
            [COLOR=navy]Case[/COLOR] "X": n3 = 3
        [COLOR=navy]End[/COLOR] Select
        [COLOR=navy]If[/COLOR] Application.Product(n1, n2, n3) > 0 [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]If[/COLOR] n = 3 And Application.Product(n1, n2, n3) > 0 [COLOR=navy]Then[/COLOR]
    Dn.Offset(, 2).value = 4
    Dn.Resize(4).Interior.Color = Col
    Col = IIf(Col = vbGreen, vbYellow, vbGreen)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

You've been very helpful :pray: MickG, code is running 100% OK!!

I thank you from the bottom of my heart.

Have a nice day

Kind Regards,

Kishan :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Continuous cell colouring
Replies
1
Views
367
Joe4
Joe4
GomaPile
  • Question Question
Find Function
4 5 6
Replies
58
Views
4K
Akuini
Akuini
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
591
StrawberryDreams
S
S
  • Locked
  • Question Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
262
Fluff
Fluff
P
  • Solved
Time conversion to existing formula
2
Replies
13
Views
854
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top