Can you help with this code please?

saftawy1

Board Regular
Joined
Oct 12, 2021
Messages
65
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I use this code to search in the list box and it works well, but when I search in the first column in the list box for a number, for example 1, 2 or 3, it gives me all the numbers that contain these numbers, so how can I make it show me in the list box only the specified number in the search
VBA Code:
    Dim x, ws As Worksheet, i As Long, j As Long, lastrow As Long
    With salles_safty.ListBox1
        .Clear
        .ColumnCount = 15
        .ColumnWidths = "55 pt;63 pt;55 pt;150 pt;63 pt;120 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt"
        Set ws = ThisWorkbook.Sheets("ledger")
        x = Application.Match(salles_safty.ComboBox2.Value, ws.Rows(2), 0)
        If Not IsError(x) Then
            lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
                        Dim a, myCols, ii As Long
            ReDim a(1 To 15, 1 To lastrow)
            myCols = Array(26, 8, 5, 7, 132, 20, 208, 46, 47, 48, 51, 52, 53, 49, 50)

            For i = 3 To lastrow
                If salles_safty.TextBox4 <> "" And InStr(ws.Cells(i, x), salles_safty.TextBox4) <> 0 Then
                    j = j + 1
                    For ii = 0 To UBound(myCols)
                        a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                    Next

                End If
            Next i
            '1111111111111111111111
                        For i = 3 To lastrow
                If salles_safty.TextBox4 = "" Then
                    j = j + 1
                    For ii = 0 To UBound(myCols)
                        a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                    Next
'                     salles_safty.t8.Value = Val(salles_safty.t54) / Val(salles_safty.t7)
'                     salles_safty.t8.Text = Format(salles_safty.t8.Text, "0.00")

'salles_safty.ListBox1.List(i, 16) = Val(salles_safty.ListBox1.List(i, 12)) + Val(salles_safty.ListBox1.List(i, 14)) '+ Val(salles_safty.ListBox1.List(i, 15))

                End If
            Next i
            
          

            '1111111111111111111111

            ReDim Preserve a(1 To UBound(a, 1), 1 To j)
            .Column = a
        End If
        End With
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try this:

VBA Code:
    Dim x, ws As Worksheet, i As Long, j As Long, lastrow As Long
    Dim bln As Boolean
    
    With salles_safty.ListBox1
        .Clear
        .ColumnCount = 15
        .ColumnWidths = "55 pt;63 pt;55 pt;150 pt;63 pt;120 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt"
        Set ws = ThisWorkbook.Sheets("ledger")
        x = Application.Match(salles_safty.ComboBox2.Value, ws.Rows(2), 0)
        If Not IsError(x) Then
            lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
                        Dim a, myCols, ii As Long
            ReDim a(1 To 15, 1 To lastrow)
            myCols = Array(26, 8, 5, 7, 132, 20, 208, 46, 47, 48, 51, 52, 53, 49, 50)

            For i = 3 To lastrow
              bln = False
              If salles_safty.TextBox4 <> "" Then
                Select Case x
                  Case 1    'Add other column numbers if the data is numeric.
                    If ws.Cells(i, x) = Val(salles_safty.TextBox4) Then
                      bln = True
                    End If
                  Case Else
                    If InStr(ws.Cells(i, x), salles_safty.TextBox4) <> 0 Then
                      bln = True
                    End If
                End Select
                
                If bln = True Then
                  j = j + 1
                  For ii = 0 To UBound(myCols)
                      a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                  Next
                End If
              End If
            Next i
            
            '1111111111111111111111
            For i = 3 To lastrow
                If salles_safty.TextBox4 = "" Then
                    j = j + 1
                    For ii = 0 To UBound(myCols)
                        a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                    Next
'                     salles_safty.t8.Value = Val(salles_safty.t54) / Val(salles_safty.t7)
'                     salles_safty.t8.Text = Format(salles_safty.t8.Text, "0.00")

'salles_safty.ListBox1.List(i, 16) = Val(salles_safty.ListBox1.List(i, 12)) + Val(salles_safty.ListBox1.List(i, 14)) '+ Val(salles_safty.ListBox1.List(i, 15))

                End If
            Next i
            
          

            '1111111111111111111111

            ReDim Preserve a(1 To UBound(a, 1), 1 To j)
            .Column = a
        End If
        End With
 
Upvote 1
Solution
Try this:

VBA Code:
    Dim x, ws As Worksheet, i As Long, j As Long, lastrow As Long
    Dim bln As Boolean
   
    With salles_safty.ListBox1
        .Clear
        .ColumnCount = 15
        .ColumnWidths = "55 pt;63 pt;55 pt;150 pt;63 pt;120 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt;55 pt"
        Set ws = ThisWorkbook.Sheets("ledger")
        x = Application.Match(salles_safty.ComboBox2.Value, ws.Rows(2), 0)
        If Not IsError(x) Then
            lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
                        Dim a, myCols, ii As Long
            ReDim a(1 To 15, 1 To lastrow)
            myCols = Array(26, 8, 5, 7, 132, 20, 208, 46, 47, 48, 51, 52, 53, 49, 50)

            For i = 3 To lastrow
              bln = False
              If salles_safty.TextBox4 <> "" Then
                Select Case x
                  Case 1    'Add other column numbers if the data is numeric.
                    If ws.Cells(i, x) = Val(salles_safty.TextBox4) Then
                      bln = True
                    End If
                  Case Else
                    If InStr(ws.Cells(i, x), salles_safty.TextBox4) <> 0 Then
                      bln = True
                    End If
                End Select
               
                If bln = True Then
                  j = j + 1
                  For ii = 0 To UBound(myCols)
                      a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                  Next
                End If
              End If
            Next i
           
            '1111111111111111111111
            For i = 3 To lastrow
                If salles_safty.TextBox4 = "" Then
                    j = j + 1
                    For ii = 0 To UBound(myCols)
                        a(ii + 1, j) = ws.Cells(i, myCols(ii)).Value
                    Next
'                     salles_safty.t8.Value = Val(salles_safty.t54) / Val(salles_safty.t7)
'                     salles_safty.t8.Text = Format(salles_safty.t8.Text, "0.00")

'salles_safty.ListBox1.List(i, 16) = Val(salles_safty.ListBox1.List(i, 12)) + Val(salles_safty.ListBox1.List(i, 14)) '+ Val(salles_safty.ListBox1.List(i, 15))

                End If
            Next i
           
         

            '1111111111111111111111

            ReDim Preserve a(1 To UBound(a, 1), 1 To j)
            .Column = a
        End If
        End With
I tried and showed all the numbers that contain the number 1 or 2
 
Upvote 0
but when I search in the first column in the list box
What is the first column of the listbox in the sheet?

x = Application.Match(salles_safty.ComboBox2.Value, ws.Rows(2), 0)

x contains the column number found in the sheet.
I don't know which column of the sheet has the numbers, since it is based on the result of a search.

So if the column with numbers is column A, it represents column 1, if the column with numbers is column E, it represents column 5, and so on.
So, in this line change the number 1 to the number of the column that contains the numbers.


Rich (BB code):
Case 1 
 
Upvote 1
What is the first column of the listbox in the sheet?



x contains the column number found in the sheet.
I don't know which column of the sheet has the numbers, since it is based on the result of a search.

So if the column with numbers is column A, it represents column 1, if the column with numbers is column E, it represents column 5, and so on.
So, in this line change the number 1 to the number of the column that contains the numbers.


Rich (BB code):
Case 1 
thank you very much
 
Upvote 1

Forum statistics

Threads
1,224,813
Messages
6,181,115
Members
453,021
Latest member
Justyna P

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
Back
Top