Can you help with this code please?

S

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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
DanteAmor said:
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
Click to expand...
I tried and showed all the numbers that contain the number 1 or 2
 
Upvote 0
saftawy1 said:
but when I search in the first column in the list box
Click to expand...
What is the first column of the listbox in the sheet?

saftawy1 said:
x = Application.Match(salles_safty.ComboBox2.Value, ws.Rows(2), 0)
Click to expand...

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
DanteAmor said:
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
Click to expand...
thank you very much
 
Upvote 1
You must log in or register to reply here.

Similar threads

S
  • Question Question
Please help me with the code that integrates the data called for the listbox
Replies
0
Views
424
saftawy1
S
rjplante
  • Solved
sort numbers with letters
2 3
Replies
24
Views
1K
Akuini
Akuini
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
65
RoryA
RoryA
S
  • Solved
Diagonal Sum in excel table via VBA code
2
Replies
11
Views
492
myall_blues
myall_blues
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
115
Abdo
A

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

Share this page

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