Count 1-X-2 Before "X" 11th Pos

[Kishan]

Hi,

I need to count in the each row 1, X & 2 before the X is finding in the 11th position (and stop counting if breaks with other sign is found). Example is attached

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Year	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	Empty	Before "X" 11th Position	Before "X" 11th Position	Before "X" 11th Position
2	Year	Date	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	Empty	Count 1	Count X	Count 2
3	2013	01/12/2013	2	X	2	1	1	1	X	1	1	1	2	X	1	1
4	2013	02/12/2013	2	2	X	1	2	1	X	X	2	1	2	1	2	X
5	2013	03/12/2013	1	1	1	1	1	X	1	X	1	1	X	X	1	2		2
6	2013	04/12/2013	1	1	1	2	1	1	1	2	X	1	1	1	X	1
7	2013	05/12/2013	1	X	1	1	X	1	2	X	1	1	1	1	1	X
8	2013	06/12/2013	1	1	1	2	2	X	1	1	1	X	1	1	1	X
9	2013	07/12/2013	1	1	X	1	1	1	1	2	1	X	1	1	X	1
10	2013	08/12/2013	1	2	1	1	1	1	2	1	1	X	1	1	1	2
11	2013	09/12/2013	2	2	X	2	1	1	2	X	2	1	1	X	1	2
12	2013	10/12/2013	1	1	1	X	1	2	2	1	1	2	1	X	1	1
13	2013	11/12/2013	1	2	1	1	X	1	1	1	1	X	X	1	X	X			1
14	2013	12/12/2013	1	1	2	X	2	2	1	2	X	1	1	X	2	1
15	2013	13/12/2013	1	X	1	X	1	2	2	2	1	1	1	2	2	2
16	2013	14/12/2013	X	X	1	1	2	1	1	2	2	2	X	1	1	1				3
17	2013	15/12/2013	2	2	2	2	2	X	1	1	1	2	X	2	X	1				1
18	2013	16/12/2013	X	1	1	1	X	X	X	1	1	1	X	1	2	2		3
19	2013	17/12/2013	2	2	X	1	2	1	2	1	2	1	X	1	1	X		1
20	2013	18/12/2013	X	1	1	2	1	1	2	1	X	2	X	2	2	1				1
21	2013	19/12/2013	1	1	1	2	1	2	X	2	2	X	1	2	1	1
22	2013	20/12/2013	2	1	1	1	X	2	1	1	X	1	1	1	2	2
Count 1-X-2 Before "X" 11th Pos

Need VBA solution if possible

Please help!

Thanks

Regards,
Kishan

 

[Kishan]

Oh, okay, I see, you want to be able to specify the character before which to count. The following macro will ask you two questions... the first is the position number (same question as my original code asked) and the second question will ask you for the character in that column to do the analysis for.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CountsBeforeCharacters()
  Dim X As Long, Pos As Long, Count As Long, Cell As Range, Item As String, Char As String
  Pos = Application.InputBox("Which position number (2 through 14)?", Type:=1)
  If Pos >= 2 And Pos <= 14 Then
    Char = InputBox("What character do you want to find prior to?")
    If Application.CountIf(Range("C3:P" & Cells(Rows.Count, "A").End(xlUp)), Char) Then
      With Range("C3:T" & Cells(Rows.Count, "A").End(xlUp))
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = vbBlack
        Intersect(.Rows, Columns("R:T")).ClearContents
      End With
      Columns(2 + Pos).Replace Char, "#N/A", xlWhole
      For Each Cell In Columns(2 + Pos).SpecialCells(xlConstants, xlErrors)
        Cell.Interior.Color = vbYellow
        Item = Cell.Offset(, -1).Value
        Count = 1
        For X = 2 To Pos - 1
          If Cell.Offset(, -X) = Item Then
            Count = Count + 1
          Else
            Exit For
          End If
        Next
        With Cell.Offset(, 1 - X).Resize(, X - 1)
          .Interior.ColorIndex = InStr("  1 X 2", Item)
          .Font.Color = vbWhite
        End With
        With Intersect(Cell.EntireRow, Columns("Q").Offset(, InStr("1X2", Item)))
          .Value = Count
          .Interior.ColorIndex = Cell.Offset(, -1).Interior.ColorIndex
          .Font.Color = vbWhite
        End With
      Next
      Columns(2 + Pos).Replace "#N/A", Char, xlWhole
    End If
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Rick Rothstein, I cannot believe it is done. You are amazing I am very happy really I was not expecting such a nice solution

Thank you very much for your kind help and making life easier

Regards,
Kishan

 

[Kishan]

Oh, okay, I see, you want to be able to specify the character before which to count. The following macro will ask you two questions... the first is the position number (same question as my original code asked) and the second question will ask you for the character in that column to do the analysis for.[/CODE]

Hello Rick Rothstein, "VBA CountsBeforeCharacters" is working perfectly.

May I ask you one more favour please could you make one another VBA that can "Count After Characters"

For example in the #post19 "2^nd image Sure count Result Before2" count 1 X or 2 before the "character 2" in this case count after the "character 2" will be great help of you.

Thank you

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

http://www.mrexcel.com/forum/excel-questions/890049-count-1-x-2-after-selected-character.html

Hi sanrv1f, you are correct I should not have started above thread, I post BUMP message but get confused as it was solved the first request I thought may be no will take interest to solve when it is answered and solved

My apologize to you will keep your advice in my mind and promise will not occur in future.

Thank you

Regards,
Kishan

 

[sanrv1f]

Since this is an international forum, help will come from different time zones, those who helped you earlier might be in sleep now,
It is always better to bump up the existing thread, since those who helped already, know the background of the requirement and help further

Thank you for understanding

 

[sanrv1f]

Hello Rick Rothstein, "VBA CountsBeforeCharacters" is working perfectly.

May I ask you one more favour please could you make one another VBA that can "Count After Characters"

For example in the #post19 "2^nd image Sure count Result Before2" count 1 X or 2 before the "character 2" in this case count after the "character 2" will be great help of you.

Thank you

Regards,
Kishan

Do you want Count after the 11th position? (ie) only in P12-P14?

 

[Kishan]

Since this is an international forum, help will come from different time zones, those who helped you earlier might be in sleep now,

Hi sanrv1f, you are correct I never thought about time zones good point to know.

Thank you for your help and I feel really sorry all about.

Regards,
Kishan

 

[Kishan]

Do you want Count after the 11th position? (ie) only in P12-P14?

Hi sanrv1f,

No I want to count from P1 To P13 (I have chosen 11^th position just for example but it can be any P1 to P13) first step VBA give an input option to select the position, and than character to choose.

Regards,
Kishan

 

[Kishan]

Do you want Count after the 11th position? (ie) only in P12-P14?

Hi sanrv1f,

Reading your question again I realized, yes if 11^th position is selected than as you say is correct “(ie) only in P12-P14?” and If for Example 4^th position is selected than P5-P14 is correct

Regards,
Kishan

 

[Rick Rothstein]

Hello Rick Rothstein, "VBA CountsBeforeCharacters" is working perfectly.

May I ask you one more favour please could you make one another VBA that can "Count After Characters"

For example in the #post19 "2^nd image Sure count Result Before2" count 1 X or 2 before the "character 2" in this case count after the "character 2" will be great help of you.

I think this does what you want (it is a modification of the code I gave you earlier where it counts and highlights after the position number and character you specify)...

[table="width: 500"]
[tr]
	[td]Sub CountsBeforeCharacters()
  Dim X As Long, Pos As Long, Count As Long, Cell As Range, Item As String, Char As String
  Pos = Application.InputBox("Which position number (2 through 14)?", Type:=1)
  If Pos >= 2 And Pos <= 14 Then
    Char = InputBox("What character do you want to find prior to?")
    If Application.CountIf(Range("C3:P" & Cells(Rows.Count, "A").End(xlUp)), Char) Then
      With Range("C3:T" & Cells(Rows.Count, "A").End(xlUp))
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = vbBlack
        Intersect(.Rows, Columns("R:T")).ClearContents
      End With
      Columns(2 + Pos).Replace Char, "#N/A", xlWhole
      For Each Cell In Columns(2 + Pos).SpecialCells(xlConstants, xlErrors)
        Cell.Interior.Color = vbYellow
        Item = Cell.Offset(, 1).Value
        Count = 1
        For X = 2 To Pos + 1
          If Cell.Offset(, X) = Item Then
            Count = Count + 1
          Else
            Exit For
          End If
        Next
        With Cell.Offset(, 1).Resize(, X - 1)
          .Interior.ColorIndex = InStr("  1 X 2", Item)
          .Font.Color = vbWhite
        End With
        With Intersect(Cell.EntireRow, Columns("Q").Offset(, InStr("1X2", Item)))
          .Value = Count
          .Interior.ColorIndex = Cell.Offset(, 1).Interior.ColorIndex
          .Font.Color = vbWhite
        End With
      Next
      Columns(2 + Pos).Replace "#N/A", Char, xlWhole
    End If
  End If
End Sub[/td]
[/tr]
[/table]