Number Search / return corresponding number strings

[dwrowe001]

Yes, I am in need of and respectfully request assistance with my project... ..
I need help developing a VBA macro which will search a huge list of numbers for a specified number, then return a specified set of numbers and continue down the list searching for the specified number until it reaches the bottom. The number array I need to search 9761 rows, C4 to F9763....

Each time the specified number is found the macro should return the numbers to the right of it and also the two rows of numbers Below it.

So for example:
if I'm searching for the number 038, which I put in cell J2. I then would like to click a button (in I1 in my example), the search will start at the top of the list. The first instance of 038 (in cell B4)is in the first line.. so return 83, 10, and 05, and the two lines below, B5:E6. Duplicates should not be returned, but they shoiuld be noted as to how many have been found as indicated by numbers in columns H, J, L and N. So the 10 in Cell E5 is the second ten recorded, so the 2nd ten shouldn't be included in the list of numbers returned. just increment J4 to 2.

The second 038 found is on the 3rd line (Cell C6), so record 46, 62, 41, 32, 90 and 08. the numbers 23, 36 and 64 were previously recorded when the first 38 was found. So their Qty numbers in H5, N5 and H6 should be incremented by 1 each.

For the third 038 found (Cell E14) since there are no numbers to the right of it on the same line, just return the 2 rows below it. number 46 was previously found, and 75 was found in line in same group, so only return 1, and increment its Qty count in J11.

The 4th 038 found in this example was found on B17, return numbers 45 and 56, all the other numbers found were previously found, so increment their Qty numbers, don't list them.

The Colors I used in the example aren't needed.. I just did that for clarity.

I this makes sense and isn't asking to much?... I don't know if regular XCEL formulas can do what I would like to do. Thank you in advance for taking the time to review my request and helping.
Thank you
Dave R.

 

[DanteAmor]

Try the following macro.

ATTENTION!!!​

You should consider the following:

1. Your image does not match the columns of your narrative. For example:​

​

2. So, the macro considers the data as it is in your image.​

​

3. The macro considers that columns C to F are texts. This is important, they must all be texts, I say this, because in your examples you have data like "00", "01", "05". So I assume they are texts.​

​

4. The macro considers that the data you put in cell J2 is text. In your example you have "038" but that number does not exist in the data range, the number that exists is "38", so you must be very careful with the data you write in cell J2. For example, you must write "05" and not just 5, neither "005", you must be very textual. The data must match exactly.​

​

5. In your example the number 64 must have on the counter 4 times.​

View attachment 94739​

6. The results in columns J through Q, starting in cell J4.​

Please, you should review the previous points, I am trying in the macro to minimize the problem of numbers vs texts, but it would help if you verify your data in the sheet.

You must put the data to look for in cell J2.

Please, check again the indications that I put in post #2

 

[dwrowe001]

Tks Dante for the help... I have check and done what you said... all data is text.. no numbers for the format. I'll admit, I didn't put a search number in J2 to search on when I first tried.. I guess I was go excited to see how it worked.... I feel kinda dumb about that.... any way, I get an error "Can't execute code in break mode"... not sure what that means....

 

[DanteAmor]

Can't execute code in break mode

You almost made it, don't despair.
You are trying to run the two codes at the same time.

Close everything, close your book, open your book again.
Select one of the 2 macros and run it, just as I described:

To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Number_Search) from the list that appears and click the Run button.

Check this
How to run macro

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Peter_SSs]

I get an error "Can't execute code in break mode"..

What about when you run my code once you have the text in J2?

 

[dwrowe001]

Peter/Dante,
ok, got it working.. Both of your scripts appear to work the exact same way. Only issues I find are:
1. I just happened to pick the number 25 to search on. Dante's code returned "run time error '9': subject out of range". Peter's code worked better for this number, only thing was in cell K85.. it only returns
a 4 in this cell, and nothing in J85.
2. neither code will work searching on 00.. Nothing happens, nothing gets returned. I also tried just searching on 0..

I've tried a bunch of other numbers which all seem to work.

Dave.

 

[DanteAmor]

1. I just happened to pick the number 25 to search on. Dante's code returned "run time error '9': subject out of range".

I adjusted my code. If 25 does not exist, it sends you a message.

2. neither code will work searching on 00.

My code works for the number "00" (remember it must be text in cell J2 and text in columns D to F)

Sub Number_Search()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i&, j&, n&, jj&, ii&, y&, k&, m&, nRow&, nCol&
  Dim num As String, myNum As String
  Dim f As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  Set f = Range("C:F").Find(Range("J2").Value, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Number does not exists"
    Exit Sub
  End If
 
  a = Range("C4:F" & Range("C" & Rows.Count).End(3).Row).Value
  num = Range("J2").Text
  n = WorksheetFunction.CountIf(Range("C:F"), num)
  ReDim b(1 To n * 4, 1 To 8)
  y = -1
 
  Range("J4:Q" & Rows.Count).ClearContents
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      myNum = "" & a(i, j)
      If myNum = num Then
        y = y + 2
        m = j + 1
        k = -1
        For ii = i To i + 2
          For jj = m To UBound(a, 2)
            myNum = "" & a(ii, jj)
            If Not dic.exists(myNum) Then
              k = k + 2
              If k = 9 Then
                k = 1
                y = y + 1
              End If
              dic(myNum) = y & "|" & k
            End If
            nRow = Split(dic(myNum), "|")(0)
            nCol = Split(dic(myNum), "|")(1)
            b(nRow, nCol) = myNum
            b(nRow, nCol + 1) = b(nRow, nCol + 1) + 1
          Next jj
          m = 1
        Next ii
      End If
    Next j
  Next i
 
  Range("J4").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Try again with this code.
You are already becoming an expert at executing macros!

 

[DanteAmor]

I try to check your data, for example, that you have not entered a data in cell J2 and other validations... but other validations may be missing.

Please try this code:

Sub Number_Search()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i&, j&, n&, jj&, ii&, y&, k&, m&, nRow&, nCol&
  Dim num As String, myNum As String
  Dim f As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  num = Range("J2").Text
 
  If num = "" Then
    MsgBox "Enter value in J2"
    Exit Sub
  End If
 
  Set f = Range("C:F").Find(num, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Number does not exists"
    Exit Sub
  End If
 
  a = Range("C4:F" & Range("C" & Rows.Count).End(3).Row + 4).Value
  n = WorksheetFunction.CountIf(Range("C:F"), num)
  ReDim b(1 To n * 4, 1 To 8)
  y = -1
 
  Range("J4:Q" & Rows.Count).ClearContents
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      myNum = "" & a(i, j)
      If myNum = num Then
        y = y + 2
        m = j + 1
        k = -1
        For ii = i To i + 2
          For jj = m To UBound(a, 2)
            myNum = "" & a(ii, jj)
            If myNum <> "" Then
              If Not dic.exists(myNum) Then
                k = k + 2
                If k = 9 Then
                  k = 1
                  y = y + 1
                End If
                dic(myNum) = y & "|" & k
              End If
              nRow = Split(dic(myNum), "|")(0)
              nCol = Split(dic(myNum), "|")(1)
              b(nRow, nCol) = myNum
              b(nRow, nCol + 1) = b(nRow, nCol + 1) + 1
            End If
          Next jj
          m = 1
        Next ii
      End If
    Next j
  Next i
 
  Range("J4").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[dwrowe001]

I try to check your data, for example, that you have not entered a data in cell J2 and other validations... but other validations may be missing.

Please try this code:

Sub Number_Search()
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim i&, j&, n&, jj&, ii&, y&, k&, m&, nRow&, nCol&
  Dim num As String, myNum As String
  Dim f As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  num = Range("J2").Text
 
  If num = "" Then
    MsgBox "Enter value in J2"
    Exit Sub
  End If
 
  Set f = Range("C:F").Find(num, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox "Number does not exists"
    Exit Sub
  End If
 
  a = Range("C4:F" & Range("C" & Rows.Count).End(3).Row + 4).Value
  n = WorksheetFunction.CountIf(Range("C:F"), num)
  ReDim b(1 To n * 4, 1 To 8)
  y = -1
 
  Range("J4:Q" & Rows.Count).ClearContents
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      myNum = "" & a(i, j)
      If myNum = num Then
        y = y + 2
        m = j + 1
        k = -1
        For ii = i To i + 2
          For jj = m To UBound(a, 2)
            myNum = "" & a(ii, jj)
            If myNum <> "" Then
              If Not dic.exists(myNum) Then
                k = k + 2
                If k = 9 Then
                  k = 1
                  y = y + 1
                End If
                dic(myNum) = y & "|" & k
              End If
              nRow = Split(dic(myNum), "|")(0)
              nCol = Split(dic(myNum), "|")(1)
              b(nRow, nCol) = myNum
              b(nRow, nCol + 1) = b(nRow, nCol + 1) + 1
            End If
          Next jj
          m = 1
        Next ii
      End If
    Next j
  Next i
 
  Range("J4").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

That seems to have resolved the issue...

Thank you you and Peter both for your help with is.. I really appreciate your help!!!

Dave

 

[dwrowe001]

Dante,

Getting back to the code you just helped me with.. looking at the returned data, it's all there but now I'm wondering on having it returned in a different way.. I mean right now it's all listed according to position and order found... I need it to be in order of quantity. Meaning in sequential order from most hits down to the number with the least hits. If there are numbers with same quantities, then those within that quantity range should be in numerical order like the numbers 00, 31, 46, 79, 81 which all had 8 hits each.

Also, I keep trying to change the font of the returned numbers (Columns J, L, N, P and also S) to a bigger font, like font size 16 bold. They keep changing back to a smaller font size after each time I run the code. The quantity numbers (Columns K, M, O, and Q) can remain small.

Dave

 

[DanteAmor]

I will gladly help you, you could create a new thread in MrExcel, explain in detail and with examples what you have and what you want as a result.

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.