VBA Looping a value in table range and display multiple rows

nam911

New Member
Joined
Aug 30, 2019
Messages
4
Dears,
I'm currently working on a data table set. I would like to search a string in table range and get multiple rows in listbox.
However, I've achieved without targeting header table name as below

VBA Code:
 Private Sub cmdSSearch_Click()

    Me.cbxASearch.Value = ""
    Me.Tcountry4search.Value = ""

    Me.Width = 800
    Me.LbxSearch.Width = 660
    Me.LSearch2.Width = 20



    Dim h As Long

    Me.LbxSearch.Clear
    Me.LSearch2.Clear

    'for column header
    With Me.LbxSearch
        .AddItem

        .List(0, 0) = Sheets("Data entry").Cells(10, 27)
        .List(0, 1) = Sheets("Data entry").Cells(10, 28)
        .List(0, 2) = Sheets("Data entry").Cells(10, 32)
        .List(0, 3) = Sheets("Data entry").Cells(10, 33)
        .List(0, 4) = Sheets("Data entry").Cells(10, 34)
        .List(0, 5) = Sheets("Data entry").Cells(10, 35)
        .List(0, 6) = Sheets("Data entry").Cells(10, 36)
        .List(0, 7) = Sheets("Data entry").Cells(10, 37)
        .List(0, 8) = Sheets("Data entry").Cells(10, 44)
        .List(0, 9) = Sheets("Data entry").Cells(10, 46)

        .ColumnWidths = "50,100,100,100,70,70,30,30,50,0"
        .ColumnCount = 10
        .Selected(0) = True
End With

'for listbox fill
On Error Resume Next
For h = 2 To Sheets("Data entry").Range("AA10000").End(xlUp).Offset(1, 0).Row
For i = 1 To 100
s = Application.WorksheetFunction.CountIf(Sheets("Data entry").Range("AA" & h, "AT" & h), Sheets("Data entry").Cells(h, i))
If s = 1 And Sheets("Data entry").Cells(h, i) = Me.cbxSSearch.Value Then
Me.LbxSearch.AddItem

Me.LbxSearch.List(LbxSearch.ListCount - 1, 0) = Sheets("Data entry").Cells(h, 27)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 1) = Sheets("Data entry").Cells(h, 28)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 2) = Sheets("Data entry").Cells(h, 32)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 3) = Sheets("Data entry").Cells(h, 33)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 4) = Format(Sheets("Data entry").Cells(h, 34), "dd-mmm-yy")
Me.LbxSearch.List(LbxSearch.ListCount - 1, 5) = Format(Sheets("Data entry").Cells(h, 35), "dd-mmm-yy")
Me.LbxSearch.List(LbxSearch.ListCount - 1, 6) = Sheets("Data entry").Cells(h, 36)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 7) = Sheets("Data entry").Cells(h, 37)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 8) = Sheets("Data entry").Cells(h, 44)
Me.LbxSearch.List(LbxSearch.ListCount - 1, 9) = Sheets("Data entry").Cells(h, 46)
End If

Next i
Next h

End Sub

Unfortunately, when columns table is moved or adding more columns which create frustration work. Then I try to target header table name but it's unsuccessful. Could someone help, please?

Code:
Private Sub cmdSSearch_Click()



'clear other text search

Me.cbxASearch.Value = ""

Me.Tcountry4search.Value = ""



Me.Width = 800

Me.LbxSearch.Width = 700

Me.LSearch2.Width = 20

Me.LbxSearch.Clear

Me.LSearch2.Clear




'for column header

With Me.LbxSearch

    .AddItem


    .List(0, 0) = "Region"

    .List(0, 1) = "Host country"

    .List(0, 2) = "Scope"

    .List(0, 3) = "Participating countries"

    .List(0, 4) = "Type of participants"

    .List(0, 5) = "Language"

    .List(0, 6) = "Type of Training"

    .List(0, 7) = "Start date"

    .List(0, 8) = "End date"

    .List(0, 9) = "sr"



    .ColumnWidths = "50,100,50,100,100,50,70,70,70,0"

    .ColumnCount = 10

    .Selected(0) = True


End With



'for listbox fill

On Error Resume Next

For h = 1 To Sheets("Data entry").Range("Training_tracker[Region]").Rows.Count

For i = 1 To Sheets("Data entry").Range("Training_tracker").Columns.Count

s = Application.WorksheetFunction.CountIf(Sheets("Data entry").ListObjects("Training_tracker").ListRows(h).Range, Sheets("Data entry").ListObjects("Training_tracker").DataBodyRange(h, i))  'if not work change DataBodyRange  instead of"Cells" '

If s = 1 And Sheets("Data entry").ListObjects("Training_tracker").Cells(h, i) = Me.cbxSSearch.Value Then

Me.LbxSearch.AddItem


Me.LbxSearch.List(LbxSearch.ListCount - 1, 0) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Region"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 1) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Host country "))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 2) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Scope"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 3) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Participating countries"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 4) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Type of participants"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 5) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Language"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 6) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Type of Training"))

Me.LbxSearch.List(LbxSearch.ListCount - 1, 7) = Format(Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("Start date")).Text, "dd-mmm-yy")

Me.LbxSearch.List(LbxSearch.ListCount - 1, 8) = Format(Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("End date")).Text, "dd-mmm-yy")

Me.LbxSearch.List(LbxSearch.ListCount - 1, 9) = Sheets("Data entry").ListObjects("Training_tracker").Cells(h, Sheets("Data entry").ListObjects("Training_tracker").ListColumns("sr"))



End If


Next i

Next h


End Sub

Thank you very much. Appreciate if there's anyone could help.
Nam
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Do you want to add more columns?
Not possible with the .AddItem method
If you need more columns you can do it using the .RowSource or .List properties of the Listbox.

If you explain a little what you want to do with pleasure I help you.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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