Check cycle of character 1X2

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Data cells C6:C78, highlighted every 7th row with black background-white fonts, my query is I need to check cycle for 3 characters 1X2 when it is completed.</SPAN></SPAN>

For the example...</SPAN></SPAN>

C6=X, C13=1, C20=2, so far cycle of 1X2 is completed starting from C6 within 14 rows, so far RESULT in row D20=14 </SPAN></SPAN>

C27=1, C34=2, C41=1(repeated), C48=X, so far next cycle of 1X2 is completed starting from C20 within 28 rows, so far RESULT in row D48=28</SPAN></SPAN>

And so on...</SPAN></SPAN>

Example sample data</SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5			P1
6			X
7			1
8			X
9			2
10			1
11			X
12			1
13			1
14			X
15			1
16			1
17			1
18			1
19			1
20			2	14
21			1
22			1
23			1
24			1
25			1
26			1
27			1
28			X
29			1
30			1
31			1
32			X
33			1
34			2
35			X
36			1
37			X
38			X
39			1
40			1
41			1
42			1
43			1
44			1
45			X
46			1
47			1
48			X	28
49			1
50			1
51			1
52			1
53			1
54			X
55			X
56			X
57			1
58			X
59			1
60			1
61			1
62			1
63			1
64			X
65			1
66			1
67			1
68			1
69			2	21
70			1
71			1
72			1
73			1
74			1
75			1
76			1
77			1
78			X
79
80
81
82
83
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN>

 

[Rick Rothstein]

For your posted example, what if cell C27 contained an X... would that complete another cycle even though some of it is part of the previous cycle? If so, what number would go in D27?

 

[Kishan]

For your posted example, what if cell C27 contained an X... would that complete another cycle even though some of it is part of the previous cycle? If so, what number would go in D27?

Hi Rick, thank you for the observations, replying to your question I would say every time the cycle is completed has to rest 0 and start counting again new cycle for 1X2.</SPAN></SPAN>

Same time as per your observations, I found that my query has a mistake, sorry for it.</SPAN></SPAN>

Please check the new example attached. In this one correct is the count will start from C7, so far the 1st cycle for 1X2 would be completed in cell D48=within the period of 42, second would be in cell D69= within the period of 21</SPAN></SPAN>

Note: B column is filled with numbers to show counts clarify the just the example</SPAN></SPAN>

The below one is a modified and correct example... </SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5			P1
6			X
7		1	1
8		2	X
9		3	2
10		4	1
11		5	X
12		6	1
13		7	1
14		8	X
15		9	1
16		10	1
17		11	1
18		12	1
19		13	1
20		14	2
21		15	1
22		16	1
23		17	1
24		18	1
25		19	1
26		20	1
27		21	1
28		22	X
29		23	1
30		24	1
31		25	1
32		26	X
33		27	1
34		28	2
35		29	X
36		30	1
37		31	X
38		32	X
39		33	1
40		34	1
41		35	1
42		36	1
43		37	1
44		38	1
45		39	X
46		40	1
47		41	1
48		42	X	42
49		1	1
50		2	1
51		3	1
52		4	1
53		5	1
54		6	X
55		7	X
56		8	X
57		9	1
58		10	X
59		11	1
60		12	1
61		13	1
62		14	1
63		15	1
64		16	X
65		17	1
66		18	1
67		19	1
68		20	1
69		21	2	21
70		1	1
71		2	1
72		3	1
73		4	1
74		5	1
75		6	1
76		7	1
77		8	1
78		9	X
79
80
81
82
83
Sheet2

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Bump

 

[Rick Rothstein]

I think this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub X12cycle()
  Dim R As Long, LastRow As Long, CntRow As Long
  Dim Combo As String, ACell As Range, Data As Variant
  Set ACell = ActiveCell
  CntRow = 6
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C5").Interior.Color = vbCyan
  Range("C5:C" & LastRow).Font.Color = vbBlack
  Range("C13").Interior.Color = vbBlack
  Range("C13").Font.Color = vbWhite
  Range("C7").Resize(6).Interior.Color = vbGreen
  Range("C7:C13").Copy
  Range("C14:C" & LastRow).PasteSpecial xlPasteFormats
  ACell.Select
  Range("C5:C" & LastRow).HorizontalAlignment = xlCenter
  Data = Range("C1:C" & LastRow).Value
  Range("D27", Cells(Rows.Count, "D").End(xlUp)).ClearContents
  For R = 27 To LastRow Step 7
    Combo = Data(R - 14, 1) & Data(R - 7, 1) & Data(R, 1)
    If Combo Like "*1*" And Combo Like "*2*" And Combo Like "*[Xx]*" Then
      Cells(R, "D").Value = R - CntRow
      CntRow = R
      R = R + 14
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

I think this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub X12cycle()
  Dim R As Long, LastRow As Long, CntRow As Long
  Dim Combo As String, ACell As Range, Data As Variant
  Set ACell = ActiveCell
  CntRow = 6
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C5").Interior.Color = vbCyan
  Range("C5:C" & LastRow).Font.Color = vbBlack
  Range("C13").Interior.Color = vbBlack
  Range("C13").Font.Color = vbWhite
  Range("C7").Resize(6).Interior.Color = vbGreen
  Range("C7:C13").Copy
  Range("C14:C" & LastRow).PasteSpecial xlPasteFormats
  ACell.Select
  Range("C5:C" & LastRow).HorizontalAlignment = xlCenter
  Data = Range("C1:C" & LastRow).Value
  Range("D27", Cells(Rows.Count, "D").End(xlUp)).ClearContents
  For R = 27 To LastRow Step 7
    Combo = Data(R - 14, 1) & Data(R - 7, 1) & Data(R, 1)
    If Combo Like "*1*" And Combo Like "*2*" And Combo Like "*[Xx]*" Then
      Cells(R, "D").Value = R - CntRow
      [B][COLOR="#FF0000"]Cells(R, "D").Font.Color = vbRed[/COLOR][/B]
     CntRow = R
      R = R + 14
    End If
  Next
End Sub[/td]
[/tr]
[/table]

Sorry, I forgot about making the numbers in Column D red. Adding the line of code I show above in red will handle that.

 

[Kishan]

I think this macro will do what you want...

Hi Rick, thank you for the macro your macro works it check in the range of every 21 rows if find cycle 1X2 which is perfect, may I did not explain question the way I wanted the results, here is my attempt again, hope this helps, sorry for the troubles.</SPAN></SPAN>

Data are in cells C6:C98 I need to check cycle for 1X2 starting from cell C7</SPAN></SPAN>

For example...First cycle count starts in cell C7 Below</SPAN></SPAN>
In the 1st 7th position found X in the cell C13</SPAN></SPAN>
In the 2nd 7th position found 1 in the cell C20 </SPAN></SPAN>
In the 3rd 7th position found 2 in the cell C27, so far 1X2 cycle completed in the period of 21, Results 21 in cell D27 </SPAN></SPAN>

Next cycle count starts in cell C28 Below</SPAN></SPAN>
In the 1st 7th position found 1 in the cell C34</SPAN></SPAN>
In the 2nd 7th position found 1 in the cell C41 </SPAN></SPAN>
In the 3rd 7th position found X in the cell C48 </SPAN></SPAN>
In the 4th 7th position found 2 in the cell C55, so far 1X2 cycle completed in the period of 28, Results 28 in cell D55</SPAN></SPAN>

Next cycle count starts in cell C56 Below</SPAN></SPAN>
In the 1st 7th position found X in the cell C62</SPAN></SPAN>
In the 2nd 7th position found 1 in the cell C70 </SPAN></SPAN>
In the 3rd 7th position found X in the cell C76 </SPAN></SPAN>
In the 4th 7th position found X in the cell C83 </SPAN></SPAN>
In the 5th 7th position found 2 in the cell C90, so far 1X2 cycle completed in the period of 35, Results 35 in cell D90 </SPAN></SPAN>

Resume: need count period for next cycle from the point where previous cycle is completed </SPAN></SPAN>
Note: Formats colours are not essential </SPAN></SPAN>

Here below are the sample data... </SPAN></SPAN>

Book1
	A	B	C	D	E
1
2
3
4
5			P1
6			X
7		1	1
8		2	X
9		3	X
10		4	2
11		5	X
12		6	1
13		7	X
14		1	1
15		2	1
16		3	1
17		4	1
18		5	1
19		6	X
20		7	1
21		1	1
22		2	1
23		3	1
24		4	1
25		5	1
26		6	1
27		7	2	21
28		1	1
29		2	1
30		3	1
31		4	1
32		5	1
33		6	1
34		7	1
35		1	X
36		2	1
37		3	1
38		4	2
39		5	1
40		6	2
41		7	1
42		1	1
43		2	X
44		3	1
45		4	1
46		5	X
47		6	1
48		7	X
49		1	2
50		2	1
51		3	1
52		4	1
53		5	1
54		6	1
55		7	2	28
56		1	1
57		2	1
58		3	2
59		4	1
60		5	1
61		6	X
62		7	X
63		1	1
64		2	1
65		3	1
66		4	X
67		5	1
68		6	1
69		7	1
70		1	2
71		2	1
72		3	2
73		4	1
74		5	X
75		6	1
76		7	X
77		1	1
78		2	1
79		3	1
80		4	1
81		5	X
82		6	2
83		7	X
84		1	X
85		2	1
86		3	2
87		4	1
88		5	2
89		6	2
90		7	2	35
91		1	X
92		2	X
93		3	1
94		4	1
95		5	X
96		6	2
97		7	1
98		1	X
99
100
Sheet3

Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Please someone could have any solution regarding post#7? </SPAN></SPAN>

Thank you</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Rick Rothstein]

Next cycle count starts in cell C56 Below
In the 1st 7th position found X in the cell C62
In the 2nd 7th position found 1 in the cell C70
In the 3rd 7th position found X in the cell C76
In the 4th 7th position found X in the cell C83
In the 5th 7th position found 2 in the cell C90, so far 1X2 cycle completed in the period of 35, Results 35 in cell D90

Resume: need count period for next cycle from the point where previous cycle is completed
Note: Formats colours are not essential

Oh, I misunderstood from your previous examples... I thought the cycle needed to be in consecutive black squares. Let me work on this and I'll be back when I have a solution. Also, the colors help me, so I'll leave them in but I will identify the code doing the colors in case you want to remove it.

 

[Rick Rothstein]

Oh, I misunderstood from your previous examples... I thought the cycle needed to be in consecutive black squares. Let me work on this and I'll be back when I have a solution. Also, the colors help me, so I'll leave them in but I will identify the code doing the colors in case you want to remove it.

Okay, I think I have it... give this macro a try and let me know if it does what you want.

[table="width: 500"]
[tr]
	[td]Sub X12cycle()
  Dim R As Long, LastRow As Long, CntRow As Long
  Dim X12 As String, ACell As Range, Data As Variant
  X12 = "---"
  CntRow = 6
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row

  [B][COLOR="#008000"]'  Beginning of cell coloring code[/COLOR][/B]
  Set ACell = ActiveCell
  Range("C5").Interior.Color = vbCyan
  Range("C5:C" & LastRow).Font.Color = vbBlack
  Range("C13").Interior.Color = vbBlack
  Range("C13").Font.Color = vbWhite
  Range("C7").Resize(6).Interior.Color = vbGreen
  Range("C7:C13").Copy
  Range("C14:C" & LastRow).PasteSpecial xlPasteFormats
  ACell.Select
  [B][COLOR="#008000"]'  End of cell coloring code[/COLOR][/B]

  Range("C5:C" & LastRow).HorizontalAlignment = xlCenter
  Data = Range("C1:C" & LastRow).Value
  Range("D27", Cells(Rows.Count, "D").End(xlUp)).ClearContents
  For R = 13 To LastRow Step 7
    Mid(X12, InStr("X12", Data(R, 1))) = Data(R, 1)
    If X12 = "X12" Then
      Cells(R, "D").Value = R - CntRow
      Cells(R, "D").Font.Color = vbRed
      CntRow = R
      R = R + 7
      X12 = "---"
    End If
  Next
End Sub[/td]
[/tr]
[/table]