Locate Numbers and Write Count

SUSAN BAXTER

New Member
Joined
Apr 1, 2019
Messages
47
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>
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
@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>
 
Upvote 0
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...
Code:
Sub CountSetData()
  Range("A18").Value = Range("E3:E10,F10:F17,H3:H10").SpecialCells(xlConstants).Count
End Sub
 
Last edited:
Upvote 0
@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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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