CODEWITHSUNDEEP
vbaexcel
Frozen Blood
Frozen Blood

Reputation: 31

Getting their name based on 2 columns in Excel

Excel 2013 with VBA. I have a ComboBox that allows me to choose RestDays Mon, Tue, Wed, and so on.
If I choose Mon, I want all agents that has RD on Monday to display in the ListBox. In the example I can display it but it only refers to single column, I want to be able to also use the other column.

Screenshot

Here's my code:

Private Sub cmbRestDay_Change()
    Dim x, dict
    Dim i As Long
    Dim cnt As Long

    Set mySheet = Sheets("Dashboard")
    ListBox1.Clear
    x = mySheet.Range("A1").CurrentRegion.Value
    Set dict = CreateObject("Scripting.Dictionary")

    If Application.CountIf(mySheet.Columns(2), cmbRestDay.Value) > 0 Then
        For i = 2 To UBound(x, 1)
            If x(i, 2) = cmbRestDay.Value Then
                dict.Item(x(i, 1)) = ""
            End If
        Next i
        ListBox1.List = dict.keys
    Else
        ListBox1.AddItem "Match not found"
    End If
End Sub


Private Sub UserForm_Initialize()
    cmbRestDay.Clear
    With cmbRestDay
        .AddItem ("Mon")
        .AddItem ("Tue")
        .AddItem ("Wed")
        .AddItem ("Thu")
        .AddItem ("Fri")
        .AddItem ("Sat")
        .AddItem ("Sun")
    End With

    cmbMyRD.Clear
    With cmbMyRD
        .AddItem ("Mon")
        .AddItem ("Tue")
        .AddItem ("Wed")
        .AddItem ("Thu")
        .AddItem ("Fri")
        .AddItem ("Sat")
        .AddItem ("Sun")
    End With
End Sub

Upvotes: 0

Views: 55

Answers (2)

Akami Kurai
Akami Kurai

Reputation: 152

Private Sub cmbRestDay_Change()
    Dim x As Integer

    Set mySheet = Sheets("Dashboard")
    ListBox1.Clear

    If Application.CountIf(mySheet.Range("A:B"), "*" & cmbRestDay.Value & "*") > 0 Then
        For x = 2 To Application.CountA(mySheet.Columns(1))
            If mySheet.Cells(x, 2) = cmbRestDay.Value Or mySheet.Cells(x, 3) = cmbRestDay.Value Then
                ListBox1.AddItem (mySheet.Cells(x, 1))
            End If
        Next
    Else
        ListBox1.AddItem ("Match not found")
    End If
End Sub

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

Use the Or logical operator:

If Application.CountIf(mySheet.Columns("B:C"), cmbRestDay.Value) > 0 Then
    For i = 2 To UBound(x, 1)
        If x(i, 2) = cmbRestDay.Value Or x(i, 3) = cmbRestDay.Value Then

Upvotes: 1

Related Questions