Check cycle of character 1X2

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDE
1
2
3
4
5P1
6X
71
8X
92
101
11X
121
131
14X
151
161
171
181
191
20214
211
221
231
241
251
261
271
28X
291
301
311
32X
331
342
35X
361
37X
38X
391
401
411
421
431
441
45X
461
471
48X28
491
501
511
521
531
54X
55X
56X
571
58X
591
601
611
621
631
64X
651
661
671
681
69221
701
711
721
731
741
751
761
771
78X
79
80
81
82
83
Sheet1


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

Regards,</SPAN>
Kishan</SPAN>
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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
ABCDE
1
2
3
4
5P1
6X
711
82X
932
1041
115X
1261
1371
148X
1591
16101
17111
18121
19131
20142
21151
22161
23171
24181
25191
26201
27211
2822X
29231
30241
31251
3226X
33271
34282
3529X
36301
3731X
3832X
39331
40341
41351
42361
43371
44381
4539X
46401
47411
4842X42
4911
5021
5131
5241
5351
546X
557X
568X
5791
5810X
59111
60121
61131
62141
63151
6416X
65171
66181
67191
68201
6921221
7011
7121
7231
7341
7451
7561
7671
7781
789X
79
80
81
82
83
Sheet2


Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0
I think this macro will do what you want...
Code:
[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]
 
Upvote 0
I think this macro will do what you want...
Code:
[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.
 
Upvote 0
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
ABCDE
1
2
3
4
5P1
6X
711
82X
93X
1042
115X
1261
137X
1411
1521
1631
1741
1851
196X
2071
2111
2221
2331
2441
2551
2661
277221
2811
2921
3031
3141
3251
3361
3471
351X
3621
3731
3842
3951
4062
4171
4211
432X
4431
4541
465X
4761
487X
4912
5021
5131
5241
5351
5461
557228
5611
5721
5832
5941
6051
616X
627X
6311
6421
6531
664X
6751
6861
6971
7012
7121
7232
7341
745X
7561
767X
7711
7821
7931
8041
815X
8262
837X
841X
8521
8632
8741
8852
8962
907235
911X
922X
9331
9441
955X
9662
9771
981X
99
100
Sheet3


Thank you </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Last edited:
Upvote 0
Please someone could have any solution regarding post#7? </SPAN></SPAN>

Thank you
</SPAN></SPAN>

Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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