Find Value, Select all rows and result in ListBox

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Aug 30, 2009
Messages
48
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Let's forget the codes, better explain with examples what you need and what you want as a result.
 

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this
VBA Code:
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

New Member
Joined
Aug 30, 2009
Messages
48

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
change this

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Rich (BB code):
a = sh.Range("A2:J" & sh.Range("C" & Rows.Count).End(3).Row).Value
 

Watch MrExcel Video

Forum statistics

Threads
1,122,975
Messages
5,599,129
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top