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

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1
2
3
4Serial Numbers12345678910111213141516171819202122232425262728293031323334353637383940
51N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X
62N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X
73N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X
84N.XN.XN.XN.XN.X12N.XN.XN.XN.X1N.XN.XN.X12N.X1
95N.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.X1234N.XN.XN.X
106N.XN.XN.XN.X1N.X1N.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.X
1171N.XN.X1N.XN.XN.XN.XN.XN.X12N.XN.X1N.XN.XN.XN.X
1281N.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X
139N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.XN.XN.XN.XN.X1
1410N.X1N.XN.X1N.XN.XN.XN.X12N.XN.X1N.XN.XN.X1N.X
15111N.XN.X12N.XN.XN.XN.XN.XN.XN.XN.XN.X12N.XN.XN.X
161212N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X
17131N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
1814N.X1N.XN.XN.X1N.X123N.X123N.X1N.XN.XN.X
19
20
21
22Serial Numbers12345678910111213141516171819202122232425262728293031323334353637383940
2311X11X1111X111111121
2421111111121111111212
2531222XX21221122X1X22
26412111XX1111X121XX1X
27512112111X122XXXX111
2862111X1X11121X221121
297X11X121212XX21X2121
308X121211X212211112X1
319112111221X2X121211X
32102X12X2112XX11X222X2
3311X11XX222122111XX111
3412XX11XX111222X22XX11
3513X222X12X21XXX1X1X1X
36142X121X1XXX2XXX1X221
37
Hoja6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B5:AW18Cell Value="N.X"textYES


Thank you in advance

Regards,
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1
2
3
4Serial Numbers123456789101112131415161718192021221412345678910111213
51
62
73
84
95
106
117
128
139
1410
1511
1612
1713
1814
19
20
21
22Serial Numbers123456789101112131415161718192021221412345678910111213
2311X11X1111X1111111211X11X1111X11
2421111111121111111212111111112111
2531222XX21221122X1X221222XX212211
26412111XX1111X121XX1X12111XX1111X
27512112111X122XXXX11112112111X122
2862111X1X11121X2211212111X1X11121
297X11X121212XX21X2121X11X121212XX
308X121211X212211112X1X121211X2122
319112111221X2X121211X112111221X2X
32102X12X2112XX11X222X22X12X2112XX1
3311X11XX222122111XX111X11XX2221221
3412XX11XX111222X22XX11XX11XX111222
3513X222X12X21XXX1X1X1XX222X12X21XX
36142X121X1XXX2XXX1X2212X121X1XXX2X
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
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 1
Solution
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,853
Messages
6,181,410
Members
453,037
Latest member
krim

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top