Count continuous “X’s” and covert the numbers into “N.X”

[Kishan]

Using Excel 2000

Hi,

Data range B23:AO36 each row has numbers and text “X”, I want to Count continuous “X’s” like shown 1, 2, 3, and 4…and so on, covert numbers 1 or 2 into “N.X” And put the result in range B5:AO18 as shown in example sheet below.

Please suggest a VBA solution.

NX.xls
	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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1
2
3
4	Serial Numbers	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40
5	1	N.X	1	N.X	N.X	1	N.X	N.X	N.X	N.X	1	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X
6	2	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X
7	3	N.X	N.X	N.X	N.X	1	2	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X	1	N.X	N.X
8	4	N.X	N.X	N.X	N.X	N.X	1	2	N.X	N.X	N.X	N.X	1	N.X	N.X	N.X	1	2	N.X	1
9	5	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X	N.X	N.X	1	2	3	4	N.X	N.X	N.X
10	6	N.X	N.X	N.X	N.X	1	N.X	1	N.X	N.X	N.X	N.X	N.X	1	N.X	N.X	N.X	N.X	N.X	N.X
11	7	1	N.X	N.X	1	N.X	N.X	N.X	N.X	N.X	N.X	1	2	N.X	N.X	1	N.X	N.X	N.X	N.X
12	8	1	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X
13	9	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X	1	N.X	N.X	N.X	N.X	N.X	N.X	1
14	10	N.X	1	N.X	N.X	1	N.X	N.X	N.X	N.X	1	2	N.X	N.X	1	N.X	N.X	N.X	1	N.X
15	11	1	N.X	N.X	1	2	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	N.X	1	2	N.X	N.X	N.X
16	12	1	2	N.X	N.X	1	2	N.X	N.X	N.X	N.X	N.X	N.X	1	N.X	N.X	1	2	N.X	N.X
17	13	1	N.X	N.X	N.X	1	N.X	N.X	1	N.X	N.X	1	2	3	N.X	1	N.X	1	N.X	1
18	14	N.X	1	N.X	N.X	N.X	1	N.X	1	2	3	N.X	1	2	3	N.X	1	N.X	N.X	N.X
19
20
21
22	Serial Numbers	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40
23	1	1	X	1	1	X	1	1	1	1	X	1	1	1	1	1	1	1	2	1
24	2	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	2	1	2
25	3	1	2	2	2	X	X	2	1	2	2	1	1	2	2	X	1	X	2	2
26	4	1	2	1	1	1	X	X	1	1	1	1	X	1	2	1	X	X	1	X
27	5	1	2	1	1	2	1	1	1	X	1	2	2	X	X	X	X	1	1	1
28	6	2	1	1	1	X	1	X	1	1	1	2	1	X	2	2	1	1	2	1
29	7	X	1	1	X	1	2	1	2	1	2	X	X	2	1	X	2	1	2	1
30	8	X	1	2	1	2	1	1	X	2	1	2	2	1	1	1	1	2	X	1
31	9	1	1	2	1	1	1	2	2	1	X	2	X	1	2	1	2	1	1	X
32	10	2	X	1	2	X	2	1	1	2	X	X	1	1	X	2	2	2	X	2
33	11	X	1	1	X	X	2	2	2	1	2	2	1	1	1	X	X	1	1	1
34	12	X	X	1	1	X	X	1	1	1	2	2	2	X	2	2	X	X	1	1
35	13	X	2	2	2	X	1	2	X	2	1	X	X	X	1	X	1	X	1	X
36	14	2	X	1	2	1	X	1	X	X	X	2	X	X	X	1	X	2	2	1
37
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:AW18	Cell Value	="N.X"	text	YES

Thank you in advance

Regards,
Kishan

 

[Kishan]

Sorry, I don't understand the problem or the resolution.

Hello Peter, sorry for replying you late due to my health I was off line since my last reply here I want to clarify I just added data sheet but without the results my 1st data range is in B23:T36 and it must give me a result in range B5:T18 it should not extend in the yellow area U5:X18 the macro you provide is into #post4 I have attached here when you run it populate the data in rage from B5:AK18. Please can you take a look so it restrict data in the range B5:T18.

Thank you and sorry for the trouble.

NX.xls
	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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1
2
3
4	Serial Numbers	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	14		1	2	3	4	5	6	7	8	9	10	11	12	13
5	1
6	2
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19
20
21
22	Serial Numbers	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	14		1	2	3	4	5	6	7	8	9	10	11	12	13
23	1	1	X	1	1	X	1	1	1	1	X	1	1	1	1	1	1	1	2	1						1	X	1	1	X	1	1	1	1	X	1	1
24	2	1	1	1	1	1	1	1	1	2	1	1	1	1	1	1	1	2	1	2						1	1	1	1	1	1	1	1	2	1	1	1
25	3	1	2	2	2	X	X	2	1	2	2	1	1	2	2	X	1	X	2	2						1	2	2	2	X	X	2	1	2	2	1	1
26	4	1	2	1	1	1	X	X	1	1	1	1	X	1	2	1	X	X	1	X						1	2	1	1	1	X	X	1	1	1	1	X
27	5	1	2	1	1	2	1	1	1	X	1	2	2	X	X	X	X	1	1	1						1	2	1	1	2	1	1	1	X	1	2	2
28	6	2	1	1	1	X	1	X	1	1	1	2	1	X	2	2	1	1	2	1						2	1	1	1	X	1	X	1	1	1	2	1
29	7	X	1	1	X	1	2	1	2	1	2	X	X	2	1	X	2	1	2	1						X	1	1	X	1	2	1	2	1	2	X	X
30	8	X	1	2	1	2	1	1	X	2	1	2	2	1	1	1	1	2	X	1						X	1	2	1	2	1	1	X	2	1	2	2
31	9	1	1	2	1	1	1	2	2	1	X	2	X	1	2	1	2	1	1	X						1	1	2	1	1	1	2	2	1	X	2	X
32	10	2	X	1	2	X	2	1	1	2	X	X	1	1	X	2	2	2	X	2						2	X	1	2	X	2	1	1	2	X	X	1
33	11	X	1	1	X	X	2	2	2	1	2	2	1	1	1	X	X	1	1	1						X	1	1	X	X	2	2	2	1	2	2	1
34	12	X	X	1	1	X	X	1	1	1	2	2	2	X	2	2	X	X	1	1						X	X	1	1	X	X	1	1	1	2	2	2
35	13	X	2	2	2	X	1	2	X	2	1	X	X	X	1	X	1	X	1	X						X	2	2	2	X	1	2	X	2	1	X	X
36	14	2	X	1	2	1	X	1	X	X	X	2	X	X	X	1	X	2	2	1						2	X	1	2	1	X	1	X	X	X	2	X
Hoja16

Sub Count_X()
  With Range("B5").Resize(14, Rows("23:36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    .Value = .Offset(18).Value
    .SpecialCells(xlConstants, xlTextValues).ClearContents
    .SpecialCells(xlConstants, xlNumbers).Value = "N.X"
    .SpecialCells(xlBlanks).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

Regards,
Kishan

 

[Peter_SSs]

Try this instead

Sub Count_X_v2()
  With Range("B5").Resize(14, Range("B23:U36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    .Value = .Offset(18).Value
    .SpecialCells(xlConstants, xlTextValues).ClearContents
    .SpecialCells(xlConstants, xlNumbers).Value = "N.X"
    .SpecialCells(xlBlanks).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

 

[Kishan]

Try this instead

Sub Count_X_v2()
  With Range("B5").Resize(14, Range("B23:U36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    .Value = .Offset(18).Value
    .SpecialCells(xlConstants, xlTextValues).ClearContents
    .SpecialCells(xlConstants, xlNumbers).Value = "N.X"
    .SpecialCells(xlBlanks).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

Hello Peter, just tried with my original data this did the trick it worked like a magic

I am grateful to you for taking a time solving this issue.

Have a good day and great time.

Regards,
Kishan

 

[Peter_SSs]

No problem.