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

Kishan · May 15, 2023

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

1

N.X

1

N.X

6

2

N.X

7

3

N.X

1

2

N.X

1

N.X

1

N.X

8

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

9

5

N.X

1

N.X

1

2

3

4

N.X

10

6

N.X

1

N.X

1

N.X

1

N.X

11

7

1

N.X

1

N.X

1

2

N.X

1

N.X

12

8

1

N.X

1

N.X

1

N.X

13

9

N.X

1

N.X

1

N.X

1

14

10

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

15

11

1

N.X

1

2

N.X

1

2

N.X

16

12

1

2

N.X

1

2

N.X

1

N.X

1

2

N.X

17

13

1

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

N.X

1

18

14

N.X

1

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

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

X

1

X

1

X

1

2

1

24

2

1

2

1

2

1

2

25

3

1

2

X

2

1

2

1

2

X

1

X

2

26

4

1

2

1

X

1

X

1

2

1

X

1

X

27

5

1

2

1

2

1

X

1

2

X

1

28

6

2

1

X

1

X

1

2

1

X

2

1

2

1

29

7

X

1

X

1

2

1

2

1

2

X

2

1

X

2

1

2

1

30

8

X

1

2

1

2

1

X

2

1

2

1

2

X

1

31

9

1

2

1

2

1

X

2

X

1

2

1

2

1

X

32

10

2

X

1

2

X

2

1

2

X

1

X

2

X

2

33

11

X

1

X

2

1

2

1

X

1

34

12

X

1

X

1

2

X

2

X

1

35

13

X

2

X

1

2

X

2

1

X

1

X

1

X

1

X

36

14

2

X

1

2

1

X

1

X

2

X

1

X

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 · Jul 4, 2023

Peter_SSs said:
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

X

1

X

1

X

1

2

1

X

1

X

1

X

1

24

2

1

2

1

2

1

2

1

2

1

25

3

1

2

X

2

1

2

1

2

X

1

X

2

1

2

X

2

1

2

1

26

4

1

2

1

X

1

X

1

2

1

X

1

X

1

2

1

X

1

X

27

5

1

2

1

2

1

X

1

2

X

1

2

1

2

1

X

1

2

28

6

2

1

X

1

X

1

2

1

X

2

1

2

1

2

1

X

1

X

1

2

1

29

7

X

1

X

1

2

1

2

1

2

X

2

1

X

2

1

2

1

X

1

X

1

2

1

2

1

2

X

30

8

X

1

2

1

2

1

X

2

1

2

1

2

X

1

X

1

2

1

2

1

X

2

1

2

31

9

1

2

1

2

1

X

2

X

1

2

1

2

1

X

1

2

1

2

1

X

2

X

32

10

2

X

1

2

X

2

1

2

X

1

X

2

X

2

X

1

2

X

2

1

2

X

1

33

11

X

1

X

2

1

2

1

X

1

X

1

X

2

1

2

1

34

12

X

1

X

1

2

X

2

X

1

X

1

X

1

2

35

13

X

2

X

1

2

X

2

1

X

1

X

1

X

1

X

2

X

1

2

X

2

1

X

36

14

2

X

1

2

1

X

1

X

2

X

1

X

2

1

2

X

1

2

1

X

1

X

2

X

Hoja16

VBA Code:

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 · Jul 4, 2023

Try this instead

VBA Code:

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 · Jul 4, 2023

Peter_SSs said:

Try this instead

VBA Code:

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 · Jul 5, 2023

No problem.

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

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock