Want to search for up to18 numbers across

[Jack-P-Winner]

I made this sheet as small as I could (KB size) so sorry for having to use Box.net

I have this Macro that ill search for a group of numbers vertically. I can place numbers in for example C3 C4 C5 and then click on the Gray box HIJ 6789 and then highlight the numbers in C3 C4 C5 and hit next and it will bring me to the next group of numbers vertically. In this example I am looking for all the 426's in row C.

What I am wanting to do is search for a group of numbers horizontally instead now. In the spreadsheet labeled Horizontal search lets say I want to find all the 6222 in the columns C:AE (highlight in purple) or I may want to look for all the 24244's in all the rows (highlighted in green) or I may want to find all the ones in row 11 (highlighted in yellow)

I want to be able to hit a button and find all the ones that are similar. Hopefully this makes sense

https://app.box.com/s/nlx3hhhl8duunfr5zie7

 

[Smallman]

Hi Jack

Give the following a try.

Option Compare Text
Option Explicit
 
Function CountIt(Rng As Range, txt As String) As Double
    Dim r As Range
    Dim i As Integer
  
    For Each r In Rng
        For i = 1 To Len(r)
            If Mid(r, i, Len(txt)) = txt Then CountIt = CountIt + 1
        Next i
    Next r
End Function

It involves you concatenating your data.

If it does not work for you because of the two digit numbers in your dataset then change the concatenation to include a comma or some other special character, then add this to your criteria. It should cover off that problem.

The cols in Orange I think you should remove. Data should be tabular and sequential in a spreadsheet IMO.

Take care

Smallman

 

[Jack-P-Winner]

Hi Jack

Give the following a try.

Option Compare Text
Option Explicit

Function CountIt(Rng As Range, txt As String) As Double
    Dim r As Range
    Dim i As Integer

    For Each r In Rng
        For i = 1 To Len(r)
            If Mid(r, i, Len(txt)) = txt Then CountIt = CountIt + 1
        Next i
    Next r
End Function

It involves you concatenating your data.

If it does not work for you because of the two digit numbers in your dataset then change the concatenation to include a comma or some other special character, then add this to your criteria. It should cover off that problem.

The cols in Orange I think you should remove. Data should be tabular and sequential in a spreadsheet IMO.

Take care

Smallman

Sry for the delay, its been a hectic week. I am sry but I don't know how to run this Macro and what to do. Can you please explain a little more detail how I would search for a series of numbers? I see the FIND under AG and AH but when I put different numbers in there nothing happens. Where would I place the digits to search for? Lets say I want to look for all the ones that are high lighted in yellow which is 2424 in 8MOQS

Please remove the columns you suggest and see if it runs as I could not get it to )-:

I appreciate your help so much!!!

 

[Jack-P-Winner]

This is kind of what I am trying to do

https://app.box.com/s/gahxslo4gvb3i82o4wic

Thank you!!

 

[Smallman]

Jack

The answers are right there in AG and AH. What part of it can't you see? You said you wanted to trap the sequence of 6222 there it is in AG4 first hit is value 1.

You will need to elaborate further with examples because either i read your post all wrong or you changed what you were looking for.

EDIT - I replied a bit too quickly. Your last post has a file to view. Will have a look at that and see if I can do something meaningful.

OK I have viewed your file. That is not what I see when I open the file. I have highlighted in Yellow the HITS. You can see the function nails the ones that meet your criteria.

Are you opening the above file?

Take care

Smallman

 

[Jack-P-Winner]

ah, I see it now Smallman, so sorry. I did not know how to read the spreadsheet but I now see the "1"s in the columns AG and AH with the corresponding answers. Is it possible for the answer to be highlighted to make it easier to view?

Do I just type in what I want to search for in either AG or AH?

Again, so sorry for not seeing this last night

 

[Jack-P-Winner]

This is a new request just thinking ahead Is it possible to pull the answers it finds and copy them to a new sheet in the same book like the picture? Have them with the columns the answers were found in like BB EO1 EO2 EO3 in this example. I will end up copying the answers to another sheet and thought this would save me a lot of time. I need the row I am asking for plus the row the answer was in for this example I am looking for 2424 and the answer is 6422 so I would want to see both. Again THANK YOU Smallman!!!!

 

[Smallman]

Hi Jack

You are correct, this is a whole new request and a lot more complex than the original one. It is not straight forward from what I can see.

Take care

Smallman

 

[Jack-P-Winner]

I am playing around with it and almost have it, will post when complete. Thanks for the attempt