Identifying number strings from Target numbers

[dwrowe001]

Hi,

This is going to be a multi stage question… this is stage one… Going about it this way, breaking it up into small chunks like this I hope will make it easier for me to learn it. I hope this is ok?

Looking to identify 3 number groups from numbers surrounding a target number.

How it work is, I paste a search group in the Search Group box B3:S12, and then enter a Search number in O2. I’m hoping to get help formulating a VBA script that will search the search group I enter in B3:S12.

The script will then Identify (Highlight) all instances of the search number I specify in O2

So for example, If I want to search on the number 5, I enter it in cell O2 and run the script.

The script should go and identify all instances of 5 in the search area.

Taking the first Target #5 located at B3, the numbers 812 form a half ring around the target #5 in this cell.

I want to be able to identify all 3 number groups coming off of each of those 3 digits horizontally,
vertically and diagonally. If a 3 digit string can’t be formed, then ignore it.. only continuous 3 digit strings.

for below example the numbers 812 surround the target #5. The 3 number strings coming off of each of the 8, 1, 2 digits would be:

5​ 2​ 0​ 5​ 8​ 1​ 9​ 1​ 2​ 2​ 2​ 7​ 7​ 9​ 4​ 9​

8: 819, 824 and 827. 1: 191, 129 and 129 again. (129 is a duplicate.) 2: 205, 297 and 212

Do this same process for each of the Highlighted Target numbers found within the Search Group array of B3:S12. Sheet 1 in the attached file will show some details.

The results should be listed in columns B14 thru however many target numbers were found below the Search Group. Sheet 2 shows what the layout should look like.

I hope this project isn’t too complicated.. if you decide to help me with this, please provide details as to what each line does so I can learn this… I would really like to learn VBA so I can do this stuff on my own and only come to you when I need extra help.

Thank you for your consideration, time and efforts.

Dave

 

[vletm]

dwrowe001​

Do Sheet3 give Your expected results?
Usage: apply Your value to cell O2 and press <ENT>
Your named 'details' are missing - You can read that code row-by-row and learn.

 

[dwrowe001]

dwrowe001​

Do Sheet3 give Your expected results?
Usage: apply Your value to cell O2 and press <ENT>
Your named 'details' are missing - You can read that code row-by-row and learn.

Val,
Yes, indeed it does give expected results and does exactly what I want. I like the way you have listed the returned values... Thank you!!!!

just a couple minor questions:
1. how do I change the colors? I would like to change the colors of the highlights around the target numbers.
and also the highlights surrounding numbers around the target numbers, and finally the txt colors of the target numbers.

2. Would it be ok if I picked your brain on how this vba works?

Again... thank you so much for your fast reply...

Dave

 

[dwrowe001]

Val,
Yes, indeed it does give expected results and does exactly what I want. I like the way you have listed the returned values... Thank you!!!!

just a couple minor questions:
1. how do I change the colors? I would like to change the colors of the highlights around the target numbers.
and also the highlights surrounding numbers around the target numbers, and finally the txt colors of the target numbers.

2. Would it be ok if I picked your brain on how this vba works?

Again... thank you so much for your fast reply...

Dave

oh, forgot... can I copy and paste this code into another spreadsheet?

 

[vletm]

dwrowe001​

#1 minor ( You'll learn soon, what could 'minor' means ... )
You can change color by changing value after .ColorIndex -text in the code.
Please, save those original values somewhere before - okay.
#2 That would be painful for You.
# You mean ... You just remember
Of course, You can copy and paste that code as You need.