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

 

[Peter_SSs]

Try

Sub Count_Numbers()
  With Range("B5").Resize(14, Rows("23:36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    Range("B23:B36").Resize(, .Columns.Count).Copy Destination:=.Cells(1, 1)
    .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

 

[Kishan]

Try

Sub Count_Numbers()
  With Range("B5").Resize(14, Rows("23:36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    Range("B23:B36").Resize(, .Columns.Count).Copy Destination:=.Cells(1, 1)
    .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

Hello Peter, I want conform that your macro has result as request perfectly ok.

Thank you for you kind help and time.

Have a good weekend, Good Luck!

Kind Regards,
Kishan

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[Kishan]

You're welcome. Thanks for the confirmation.

Hello Peter_SSs, I need your help again as I did not mention B5:AO18 cell have conditional format if it “X” highlight in blue with font white.

When I applied code with original data it erased the CF. Please can you help with this issue so it remain the CF. Sorry for the trouble.

Kind Regards,
Kishan

 

[Peter_SSs]

B5:AO18 cell have conditional format if it “X” highlight in blue with font white.

Perhaps include that information with XL2BB in future?

How were the 'X' values formatted with blue background and white font in rows 23:36?

 

[Kishan]

Perhaps include that information with XL2BB in future?

View attachment 91599

How were the 'X' values formatted with blue background and white font in rows 23:36?

Hello, Peter, I did not new it thank you for letting me know I will follow-up in the feature. And also going to try now to check how it works.
CF...it is in the range B5:AW18

NX.xls
	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	AP	AQ	AR	AS	AT	AU	AV	AW
5	1	X	1	2	X	1	2	3	4	X	1	2	3	4	5	6	7	8	9
6	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
7	1	2	3	4	X	X	1	2	3	4	5	6	7	8	X	1	X	1	2
8	1	2	3	4	5	X	X	1	2	3	4	X	1	2	3	X	X	1	X
9	1	2	3	4	5	6	7	8	X	1	2	3	X	X	X	X	1	2	3
10	1	2	3	4	X	1	X	1	2	3	4	5	X	1	2	3	4	5	6
11	X	1	2	X	1	2	3	4	5	6	X	X	1	2	X	1	2	3	4
12	X	1	2	3	4	5	6	X	1	2	3	4	5	6	7	8	9	X	1
13	1	2	3	4	5	6	7	8	9	X	1	X	1	2	3	4	5	6	X
14	1	X	1	2	X	1	2	3	4	X	X	1	2	X	1	2	3	X	1
15	X	1	2	X	X	1	2	3	4	5	6	7	8	9	X	X	1	2	3
16	X	X	1	2	X	X	1	2	3	4	5	6	X	1	2	X	X	1	2
17	X	1	2	3	X	1	2	X	1	2	X	X	X	1	X	1	X	1	X
18	1	X	1	2	3	X	1	X	X	X	1	X	X	X	1	X	1	2	3
Hoja5

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

Kind Regards,
Kishan

 

[Peter_SSs]

CF...it is in the range B5:AW18

Yes, you indicated before that there was CF in rows 5:18. My question was

How were the 'X' values formatted with blue background and white font in rows 23:36?

I was asking because in your image they are coloured blue with white font and my code copied that to rows 5:18 so I was wondering if you really needed that CF in rows 5:18?

 

[Kishan]

Yes, you indicated before that there was CF in rows 5:18. My question was

I was asking because in your image they are coloured blue with white font and my code copied that to rows 5:18 so I was wondering if you really needed that CF in rows 5:18?

Hello, Peter, Oh, I see that you are right. In my opening post I made X colour manually to show the example clearer.

I'm sorry anyone can get confused as it wasn't clarified in the question about it. Here is the actual layout. Please can you give me a VBA solution which can work on this original layout?

Data range is in cells B23:AW36 and I need the result in the range A5:AW18 in this range the “X” has CF as shown below.

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
Hoja5

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

Kind Regards,
Kishan

 

[Peter_SSs]

In my opening post I made X colour manually to show the example clearer.

.... Here is the actual layout.

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