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>

 

[DanteAmor]

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?

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.

 

[SUSAN BAXTER]

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

 

[Fluff]

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.

 

[DanteAmor]

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.