Search value in textbox1 in the listbox1 with array doesn't work

[david2005wang]

Dear guys, I have codes as followings:
1. draw a userform with listbox1, and textbox1 which is used for input to search like-text in the listbox1 which shows the list of manufacturers in the sheets("manufacture").Range ("A1:A6"), the last row of the sheet , and 2 show the research result in the listbox1, but doens't work, it shows subscribe our of range in the line "brr(j, 1) = arr(i, 1)".
colud any expert to go through, check the code, and provide how to revise the code? thank you guys!

Private Sub UserForm_Initialize()
Dim sht As Worksheet, n As Long
Dim arr()
Set sht = Sheets("Manufacture")
n = sht.Range("A" & Rows.Count).End(xlUp).Row
arr = sht.Range("A1:A" & n)
ListBox1.List = arr
End Sub

Private Sub TextBox1_Change()
Dim sht As Worksheet, n As Long, arr(), C As Boolean
Set sht = Sheets("Manufacture")
n = sht.Range("A" & Rows.Count).End(xlUp).Row
arr = sht.Range("A1:A" & n)
Dim brr()
Dim i, j As Integer
For i = 1 To UBound(arr)
C = arr(i, 1) Like "*" & TextBox1.Text & "*"
If C Or i = 1 Then
j = j + 1
brr(j, 1) = arr(i, 1)
End If
Next
ListBox1.List = brr
End Sub

 

[DanteAmor]

Try this:

Private Sub UserForm_Initialize()
  Dim sht As Worksheet, n As Long
  Dim arr()
  
  Set sht = Sheets("Manufacture")
  n = sht.Range("A" & Rows.Count).End(xlUp).Row
  arr = sht.Range("A1:A" & n)
  ListBox1.List = arr
End Sub

Private Sub TextBox1_Change()
  Dim sht As Worksheet
  Dim brr As Variant, arr As Variant
  Dim i As Long, j As Long, n As Long, m As Long
  
  Set sht = Sheets("Manufacture")
  n = sht.Range("A" & Rows.Count).End(xlUp).Row
  arr = sht.Range("A1:A" & n)
  
  m = WorksheetFunction.CountIf(sht.Range("A1:A" & n), "*" & TextBox1.Text & "*")
  ReDim brr(1 To m + 1, 1 To 1)
  For i = 1 To UBound(arr)
    If arr(i, 1) Like "*" & TextBox1.Text & "*" Or i = 1 Then
      j = j + 1
      brr(j, 1) = arr(i, 1)
    End If
  Next
  ListBox1.List = brr
End Sub

 

[dmt32]

Another way maybe

Dim arr             As Variant
Private Sub UserForm_Initialize()
    Dim sht         As Worksheet
    Dim n           As Long
  
    Set sht = ThisWorkbook.Worksheets("Manufacture")
    n = sht.Range("A" & sht.Rows.Count).End(xlUp).Row
    arr = sht.Range("A2:A" & n)
    Me.ListBox1.List = arr
  
End Sub

Private Sub TextBox1_Change()
    Dim Search      As String
    Dim r           As Long
  
    Search = Me.TextBox1.Value
  
    With Me.ListBox1
        .Clear
        If Len(Search) > 0 Then
            For r = 1 To UBound(arr, 1)
                If UCase(arr(r, 1)) Like "*" & UCase(Search) & "*" Then
                    .AddItem arr(r, 1)
                End If
            Next
        Else
            .List = arr
        End If
    End With
  
End Sub

note variable arr this MUST sit at the very TOP of your userform code page OUTSIDE any procedure

Solution may not be as quick as the array version you were attempting but might be sufficient for your requirement.
Also, solution is not case sensitive & the list will reset when Textbox is cleared

Dave

 

[david2005wang]

Another way maybe

Dim arr             As Variant
Private Sub UserForm_Initialize()
    Dim sht         As Worksheet
    Dim n           As Long
 
    Set sht = ThisWorkbook.Worksheets("Manufacture")
    n = sht.Range("A" & sht.Rows.Count).End(xlUp).Row
    arr = sht.Range("A2:A" & n)
    Me.ListBox1.List = arr
 
End Sub

Private Sub TextBox1_Change()
    Dim Search      As String
    Dim r           As Long
 
    Search = Me.TextBox1.Value
 
    With Me.ListBox1
        .Clear
        If Len(Search) > 0 Then
            For r = 1 To UBound(arr, 1)
                If UCase(arr(r, 1)) Like "*" & UCase(Search) & "*" Then
                    .AddItem arr(r, 1)
                End If
            Next
        Else
            .List = arr
        End If
    End With
 
End Sub

note variable arr this MUST sit at the very TOP of your userform code page OUTSIDE any procedure

Solution may not be as quick as the array version you were attempting but might be sufficient for your requirement.
Also, solution is not case sensitive & the list will reset when Textbox is cleared

Dave

Another way maybe

Dim arr             As Variant
Private Sub UserForm_Initialize()
    Dim sht         As Worksheet
    Dim n           As Long
 
    Set sht = ThisWorkbook.Worksheets("Manufacture")
    n = sht.Range("A" & sht.Rows.Count).End(xlUp).Row
    arr = sht.Range("A2:A" & n)
    Me.ListBox1.List = arr
 
End Sub

Private Sub TextBox1_Change()
    Dim Search      As String
    Dim r           As Long
 
    Search = Me.TextBox1.Value
 
    With Me.ListBox1
        .Clear
        If Len(Search) > 0 Then
            For r = 1 To UBound(arr, 1)
                If UCase(arr(r, 1)) Like "*" & UCase(Search) & "*" Then
                    .AddItem arr(r, 1)
                End If
            Next
        Else
            .List = arr
        End If
    End With
 
End Sub

note variable arr this MUST sit at the very TOP of your userform code page OUTSIDE any procedure

Solution may not be as quick as the array version you were attempting but might be sufficient for your requirement.
Also, solution is not case sensitive & the list will reset when Textbox is cleared

Dave

Dear dmt32, you are 100% correct, and your codes work well. one of my mistake probably is that the value to give to brr() is not right, I guess. Your codes also improve a lot technically from step to step in case of searching and showing in the listbox.

 

[david2005wang]

ve to brr() is not

Try this:

Private Sub UserForm_Initialize()
  Dim sht As Worksheet, n As Long
  Dim arr()
 
  Set sht = Sheets("Manufacture")
  n = sht.Range("A" & Rows.Count).End(xlUp).Row
  arr = sht.Range("A1:A" & n)
  ListBox1.List = arr
End Sub

Private Sub TextBox1_Change()
  Dim sht As Worksheet
  Dim brr As Variant, arr As Variant
  Dim i As Long, j As Long, n As Long, m As Long
 
  Set sht = Sheets("Manufacture")
  n = sht.Range("A" & Rows.Count).End(xlUp).Row
  arr = sht.Range("A1:A" & n)
 
  m = WorksheetFunction.CountIf(sht.Range("A1:A" & n), "*" & TextBox1.Text & "*")
  ReDim brr(1 To m + 1, 1 To 1)
  For i = 1 To UBound(arr)
    If arr(i, 1) Like "*" & TextBox1.Text & "*" Or i = 1 Then
      j = j + 1
      brr(j, 1) = arr(i, 1)
    End If
  Next
  ListBox1.List = brr
End Sub

Dear DanteAmor, your code is work, and correct my mistakes. thanking you!