Locate Numbers and Write Count

[SUSAN BAXTER]

I was looking for help on creating some code to find sets of numbers. I can’t use a formula because their is problems with other VBA code I run in the same cell range when I do that.


The code would search for any set of numbers (1-1, 9-9, 20-1 etc.) in the ranges E3:E25, F10:F45, H3:H20 (I will need to be able to add additional ranges in the future). When the code locates a set of numbers it would perform a count and write the total results to cell A18.


The set of number cells are formatted as text.


That would do it. Thanks so much for all your help.


<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[Rick Rothstein]

I presume each number set is in the cell all by itself, correct? If so, what could be in the cells when there is no number set?

 

[SUSAN BAXTER]

Hi

Thanks so much for your reply. Each set of numbers would be in the cell by itself. If there are no sets of numbers the cell would be blank. Thanks again.

 

[Rick Rothstein]

Thanks so much for your reply. Each set of numbers would be in the cell by itself. If there are no sets of numbers the cell would be blank. Thanks again.

So then basically, you want to count the number of cells with values in them. Execute this in a macro by itself or within your own code if that is how you need to use it...

SetCount = Range("E3:E25,F10:F45,H3:H20").SpecialCells(xlConstants).Count

 

[SUSAN BAXTER]

Thanks Rick for the formula but unfortunately I can't use it. I running other code and this would be added to that run all set. Also, I've tried to run formulas with VBA and had problem and so I really need to stick to VBA code if that's possible.

Thank-you

 

[Rick Rothstein]

Thanks Rick for the formula but unfortunately I can't use it. I running other code and this would be added to that run all set. Also, I've tried to run formulas with VBA and had problem and so I really need to stick to VBA code if that's possible.

That is VBA code... try it. I was not sure where you wanted the count outputted to, so I assigned it to a variable named SetCount, but you can show it in a MessageBox if that is your wish...

MsgBox Range("E3:E25,F10:F45,H3:H20").SpecialCells(xlConstants).Count

 

[DanteAmor]

Hi Susan:

You help us with a file of examples, that explains what data you have, what results you expect and where you want the results.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[SUSAN BAXTER]

@DanteAmor


Hi Dante, it’s just wonderful that you can help me.


Below is my post:


The code would search for any set of numbers (1-1, 9-9, 20-1 etc.) in the ranges E3:E10, F10:F17, H3:H10 (I will need to be able to add additional ranges in the future). The cells either have a set of numbers in them or they are blank. When the code locates a set of numbers it would perform a count and write the total results to cell A18.

The set of number cells are formatted as text. I actually do not have a Dropbox account but I can lay out the data easily below.


(Cell A18 below)
3


Cell ranges below:


E3
E4
E5 1-1
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 9-9
F16
F17


H3 20-1
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in them. First in cell E5 (1-1), second cell F15 (9-9) and third H3 (20-1). The code would then add up the 3 positive results and write 3 to cell A18.


I hope this is clear, if not please advise and I will clarify. I wish you were here I would give you a big hug.


<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[Rick Rothstein]

Cell ranges below:


E3
E4
E5 1-1
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 9-9
F16
F17


H3 20-1
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in them. First in cell E5 (1-1), second cell F15 (9-9) and third H3 (20-1). The code would then add up the 3 positive results and write 3 to cell A18.

I still believe the code I gave you will do what you want, it is just that you seem to have changed the ranges to apply it to. For the above example...

Range("A18").Value = Range("E3:E10,F10:F17,H3:H10").SpecialCells(xlConstants).Count

The problem is I do not know where you want to use this at. If within a macro or function of your own, just include the above line of code within it. If, instead, you need it in a standalone macro, then try this one...

Sub CountSetData()
  Range("A18").Value = Range("E3:E10,F10:F17,H3:H10").SpecialCells(xlConstants).Count
End Sub

 

[DanteAmor]

@DanteAmor


Hi Dante, it’s just wonderful that you can help me.


Below is my post:


The code would search for any set of numbers (1-1, 9-9, 20-1 etc.) in the ranges E3:E10, F10:F17, H3:H10 (I will need to be able to add additional ranges in the future). The cells either have a set of numbers in them or they are blank. When the code locates a set of numbers it would perform a count and write the total results to cell A18.

The set of number cells are formatted as text. I actually do not have a Dropbox account but I can lay out the data easily below.


(Cell A18 below)
3


Cell ranges below:


E3
E4
E5 1-1
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 9-9
F16
F17


H3 20-1
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in them. First in cell E5 (1-1), second cell F15 (9-9) and third H3 (20-1). The code would then add up the 3 positive results and write 3 to cell A18.


I hope this is clear, if not please advise and I will clarify. I wish you were here I would give you a big hug.


<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

Rick macro works fine, I would only add a 0 to the cell in case there are no set-numbers ​​in your ranges

Sub CountSetData()
    On Error Resume Next
    Range("A18").Value = 0
    Range("A18").Value = Range("E3:E10,F10:F17,H3:H10").SpecialCells(xlConstants).Count
End Sub