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>
 
Hi Dante

Re reading my post I think I may see why it's not clear. The 3 ranges (E3:E10, F10:F17, H3:H10) all have numbers 1 to 20 in each cell. What they do not all have is a set of numbers in the cell to the right of them. So in my sample data cell E4 has number 7 in it but no set of numbers (2-2 or 99-1) in it so that would be a negative result but in cell E5 has 1 in it and a 1-1 in cell F5 that is a positive result and what needs to be counted and written to cell B18.

Thanks so much.

Did you try the change I sent you?

Code:
Sub CountSetData()
On Error Resume Next
Range("B18").Value = 0
Range("B18").Value = Range("F3:F10,G10:G17,I3:I10").SpecialCells(xlConstants).Count
End Sub

Note: You could check the ranges, E10 (your right is F10) but F10 is also a range of cells.


Note2: In truth, you could make an effort to create a dropbox account, it would be easier if you explain this with an image.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi Dante

I have to apologize to you for the mess I have made of this post and think it's best if I start over. I would like to thank-you for your help on the first part of this post.

Susan
 
Last edited:
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...7-small-change-to-code-to-locate-numbers.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi Dante

I have to apologize to you for the mess I have made of this post and think it's best if I start over. I would like to thank-you for your help on the first part of this post.

Susan


Do not worry, we are here to help you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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