Before or after the “X” if there is any number count them.

[Kishan]

Using Excel 2000

Hi,

Data range B23:AO36 each row has numbers and text “X”, I want before or after the “X” if there is any number keep count them 1, 2, 3, 4….and so on. And put the result in range B5:AO18 as shown in example sheet below.

Please suggest if possible 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	1	X	1	2	X	1	2	3	4	X	1	2	3	4	5	6	7	8	9
6	2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
7	3	1	2	3	4	X	X	1	2	3	4	5	6	7	8	X	1	X	1	2
8	4	1	2	3	4	5	X	X	1	2	3	4	X	1	2	3	X	X	1	X
9	5	1	2	3	4	5	6	7	8	X	1	2	3	X	X	X	X	1	2	3
10	6	1	2	3	4	X	1	X	1	2	3	4	5	X	1	2	3	4	5	6
11	7	X	1	2	X	1	2	3	4	5	6	X	X	1	2	X	1	2	3	4
12	8	X	1	2	3	4	5	6	X	1	2	3	4	5	6	7	8	9	X	1
13	9	1	2	3	4	5	6	7	8	9	X	1	X	1	2	3	4	5	6	X
14	10	1	X	1	2	X	1	2	3	4	X	X	1	2	X	1	2	3	X	1
15	11	X	1	2	X	X	1	2	3	4	5	6	7	8	9	X	X	1	2	3
16	12	X	X	1	2	X	X	1	2	3	4	5	6	X	1	2	X	X	1	2
17	13	X	1	2	3	X	1	2	X	1	2	X	X	X	1	X	1	X	1	X
18	14	1	X	1	2	3	X	1	X	X	X	1	X	X	X	1	X	1	2	3
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
38
39
Hoja5

Thank you in advance

Regards,
Kishan

 

[Kishan]

OK, try this instead.

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

Thank you, Peter sir. This one worked 100% perfect! With my actual layout

Have a good start of the week and Good Luck.

My Best Regards
Kishan

 

[Peter_SSs]

Great, glad it worked for you!