How to collect the values of a cell in a worksheet that satisfy a condition and add them to a listbox?

ChrisH 0
Viewed 35

im fairly new to VBA and excel (some experience in Python & R but a little rusty) and would like to know how i could code an IF ELSE statement to add data values of a cell to a listbox that i have displayed in the worksheet itself if they satisfy a condition? This is the code i have below with regards to the listbox i have displayed.

Private Sub Workbook_Open()
    
    With Sheet1.ListBox1
        .ColumnHeads = True
        .ColumnCount = 1
        .ListFillRange = Sheet2.ListObjects("Table2").DataBodyRange.Address(False, False, xlA1, True)
    End With
    
    End Sub
    
    Sub loaddata()
    
        Dim listdata As Object
        Set listdata = Sheet1.ListBox1
        Dim tabeldata As Range
        Set tabeldata = Sheet2.Range("Table2")
    
            With listdata
                .AutoLoad = True
                .ColumnHeads = True
                .ColumnCount = 1
                .List = tabeldata.CurrentRegion.Value
            End With
    
    End Sub

Listbox displayed on the worksheet with serial numbers

Basically, i would like to put all the serial numbers (column A data) into the list if they are pending review - which i denote with a blank in a 'Completed' column i have in Table2. I use a userform to fill in data required and if a case is still pending review i leave that 'Completed' segment blank. Serial numbers are in column A and completed status is in column P.

Do let me know if any information is required but i would greatly appreciate any help from the community.

listbox if-statement vba excel
1 Answers

You can do it smth like

Private Sub Workbook_Open()
    Call loaddata
End Sub

Sub loaddata()
    Dim listdata As MSForms.ListBox, tabeldata As ListObject, cl As Range
    
    Set listdata = Sheet1.ListBox1
    Set tabeldata = Sheet1.ListObjects("Table1")

    With listdata
        .Clear
        .ColumnHeads = False
    End With
    
    For Each cl In tabeldata.ListColumns("Completed").DataBodyRange
        If cl.Value = "" Then
            listdata.AddItem cl.Offset(, -1).Value
        End If
    Next
End Sub

enter image description here

Алексей Р0
Related