Find Value, Select all rows and result in ListBox

[chiabgigi]

In discussions I found this code. Is it possible to change it so that the value of a text box is the parameter to search for and that all items are redirected to a listbox? Example in column C there are pages of a catalog. There are 10 lines on page 7. In the textbox, type 7 and lines A to J should appear

Mr.
Rick Rothstein
Oct/28/2014 wrote:

Sub SelectEntireRow()
  Dim FindMe As String, Rng As Range
  FindMe = InputBox("What did you want to find?")
  If Len(FindMe) Then
    On Error GoTo RangeNotSelected
    Set Rng = Application.InputBox("Select the range to look in...", Type:=8)
    If TypeOf Rng Is Range Then
      Rng.Replace FindMe, "#N/A", xlWhole
      With Rng.SpecialCells(xlConstants, xlErrors)
        .EntireRow.Select
        .Value = FindMe
      End With
    End If
  End If
RangeNotSelected:
End Sub

 

[DanteAmor]

Your query is little different from the result of the macro.
I assume you want to search column C of the active sheet. If it is another sheet to the active one, a small adjustment would have to be made, then you tell me.

Private Sub CommandButton1_Click()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
 
  ListBox1.Clear
  If TextBox1.Value = "" Then
    MsgBox "Enter text"
    TextBox1.SetFocus
    Exit Sub
  End If
  a = Range("A2:J" & Range("C" & Rows.Count).End(3)).Value
  n = WorksheetFunction.CountIf(Range("C:C"), TextBox1.Value)
 
  If n > 0 Then
    ReDim b(1 To n, 1 To UBound(a, 2))
   
    For i = 1 To UBound(a, 1)
      If LCase(a(i, 3)) = LCase(TextBox1.Value) Then
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
      End If
    Next
   
    ListBox1.List = b
  Else
    MsgBox "No match"
  End If
End Sub

 

[chiabgigi]

Thanks for the help Dante. Just two pieces of information: 1. how can I associate the 'RowSource' to a specific sheet 2. the listboxes must necessarily contain a contiguous range or you can put specific columns (eg, A, B, D, F, G, ..... etc) anyway thanks

 

[DanteAmor]

Let's forget the codes, better explain with examples what you need and what you want as a result.

 

[chiabgigi]

Ok Dante
according to the code you kindly gave me, the rowsource must be associated with Sheet1, currently it only works with the active sheet.
The Columns that must be included in the listbox are:
A, C, D, F, G, H

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, n As Long
  Dim sh As Worksheet
  
  Set sh = Sheets("Sheet1")
  ListBox1.Clear
  If TextBox1.Value = "" Then
    MsgBox "Enter text"
    TextBox1.SetFocus
    Exit Sub
  End If
  a = sh.Range("A2:J" & sh.Range("C" & Rows.Count).End(3)).Value
  n = WorksheetFunction.CountIf(sh.Range("C:C"), TextBox1.Value)
  
  If n > 0 Then
    ReDim b(1 To n, 1 To 6)
    
    For i = 1 To UBound(a, 1)
      If LCase(a(i, 3)) = LCase(TextBox1.Value) Then
        k = k + 1
        'columns A, C, D, F, G, H
        b(k, 1) = a(i, 1)
        b(k, 2) = a(i, 3)
        b(k, 3) = a(i, 4)
        b(k, 4) = a(i, 6)
        b(k, 5) = a(i, 7)
        b(k, 6) = a(i, 8)
      End If
    Next
    
    ListBox1.List = b
  Else
    MsgBox "No match"
  End If
End Sub

 

[chiabgigi]

Hi Dante I noticed a problem. I state that both the first and the second sub work very well, but I realized that they do not go beyond a certain range. The sheet contains 1440 rows beyond line 235 it displays nothing.
file

 

[DanteAmor]

change this

a = sh.Range("A2:J" & sh.Range("C" & Rows.Count).End(3)[B][COLOR=rgb(41, 105, 176)].Row[/COLOR][/B]).Value

 

[DanteAmor]

a = sh.Range("A2:J" & sh.Range("C" & Rows.Count).End(3).Row).Value

 

[chiabgigi]

perfect.

thanks Dante for the help, good evening