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>

 

[SUSAN BAXTER]

I was looking for help on creating some code to find numbers 1 to 20.


The code would search for any numbers 1 to 20, in the ranges E3:E10, F10:F17, H3:H10 (I will need to be able to add additional ranges in the future). A positive result ones that have data in the cell to the right of it (see my example below). When the code has a positive result it would perform a count and write the total results to cell B18.


All cells have numbers in them but most do NOT have any data in the cell to the right of it. The set of number cells are formatted as general.


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




(Cell B18 below)
3


Cell ranges below:


E3
E4
E5 1, cell E6 has 1-1 in it
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 2, cell F16 has 9-9 in it
F16
F17


H3 20, cell H4 has 20-1 in it
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in the cells to the right of it. First in cell E5 (number 1 with a 1-1 in cell E6), second cell F15 (number 2 with a 9-9 in cell F16) and third H3 (20 with a 20-1 in cell H4). The code would then add up the 3 positive results and write the count 3 to cell B18.


Thanks so much.
<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>

 

[Fluff]

@SUSAN BAXTER
In future please do not quote PMs in public without the knowledge & permission of the sender.
Thank you

 

[SUSAN BAXTER]

Thank-you to both of you for your help. They both work great.
<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>

 

[SUSAN BAXTER]

Okay.

 

[DanteAmor]

Thank-you to both of you for your help. They both work great.
<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>

I'm glad to help you. Thanks for the feedback.

 

[SUSAN BAXTER]

Hi Dante do you have time with a small change to your code?

I was looking for help on creating some code to find numbers 1 to 20.


The code would search for any numbers 1 to 20, in the ranges E3:E10, F10:F17, H3:H10 (I will need to be able to add additional ranges in the future). A positive result ones that have data in the cell to the right of it (see my example below). When the code has a positive result it would perform a count and write the total results to cell B18.


All cells have numbers in them but most do NOT have any data in the cell to the right of it. The set of number cells are formatted as general.


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




(Cell B18 below)
3


Cell ranges below:


E3
E4
E5 1, cell E6 has 1-1 in it
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 2, cell F16 has 9-9 in it
F16
F17


H3 20, cell H4 has 20-1 in it
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in the cells to the right of it. First in cell E5 (number 1 with a 1-1 in cell E6), second cell F15 (number 2 with a 9-9 in cell F16) and third H3 (20 with a 20-1 in cell H4). The code would then add up the 3 positive results and write the count 3 to cell B18.


Thanks so much.

 

[DanteAmor]

Hi Dante do you have time with a small change to your code?

I was looking for help on creating some code to find numbers 1 to 20.


The code would search for any numbers 1 to 20, in the ranges E3:E10, F10:F17, H3:H10 (I will need to be able to add additional ranges in the future). A positive result ones that have data in the cell to the right of it (see my example below). When the code has a positive result it would perform a count and write the total results to cell B18.


All cells have numbers in them but most do NOT have any data in the cell to the right of it. The set of number cells are formatted as general.


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




(Cell B18 below)
3


Cell ranges below:


E3
E4
E5 1, cell E6 has 1-1 in it
E6
E7
E8
E9
E10


F10
F11
F12
F13
F14
F15 2, cell F16 has 9-9 in it
F16
F17


H3 20, cell H4 has 20-1 in it
H4
H5
H6
H7
H8
H9
H10


In my above sample data the code would find 3 cells with sets of numbers in the cells to the right of it. First in cell E5 (number 1 with a 1-1 in cell E6), second cell F15 (number 2 with a 9-9 in cell F16) and third H3 (20 with a 20-1 in cell H4). The code would then add up the 3 positive results and write the count 3 to cell B18.


Thanks so much.

I'm not sure what you need now.


What does this mean:

1, cell E6 has 1-1 in it

An image would help more.

The current code, what results return and what should return?

 

[SUSAN BAXTER]

This is difficult but I'll try. So cell E5 now has a 1 in it and the cell to the right of it (F5 has the 1-1 in it).

In your code, I changed the range cell to B18, the new cell to write to. Need to add that a positive result would only occur if a set of numbers (1-1, or 9-9 or 20-1 or any set of numbers) is beside the cell range E3:E10, F10:F17, H3:H10. <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>

Sub CountSetData()
On Error Resume Next
Range("B18").Value = 0
Range("B18").Value = Range("E3:E10,F10:F17,H3:H10").SpecialCells(xlConstants).Count
End Sub <style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }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]

This is difficult but I'll try. So cell E5 now has a 1 in it and the cell to the right of it (F5 has the 1-1 in it).

In your code, I changed the range cell to B18, the new cell to write to. Need to add that a positive result would only occur if a set of numbers (1-1, or 9-9 or 20-1 or any set of numbers) is beside the cell range E3:E10, F10:F17, H3:H10. <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>

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 <style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }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>

I'm still confused, instead of searching in E you should look in F?

 

[SUSAN BAXTER]

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.