Add Unique items

[Digitborn.com]

Hello,

I have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.

I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).

    ElseIf ComboBox5.ListIndex <> 0 Then
        ComboBox2.Style = fmStyleDropDownList
        ComboBox2.Enabled = True
        ComboBox2.SetFocus
        For Each r In Worksheets("PartsData").Range("A2:A59")
            If r.Offset(, 1).Value = Trim(ComboBox5.Value) Then
                Dim dic As Object, e, d, a
                    Set dic = CreateObject("Scripting.Dictionary")
                    dic.CompareMode = vbTextCompare
                    With Sheets("PartsData")
                        With .Range("A2:AD" & .Range("A" & Rows.Count).End(xlUp).Row)
                            .Sort .Range("A2"), xlAscending
                            a = .Offset(0, 0).Resize(, 1).Value
                        End With
                    End With
                    For Each e In a
                        If Not IsEmpty(e) Then
                            d = Format(e, "dd-mmm-yy")
                        If Not dic.exists(d) Then dic.Add d, Nothing
                        End If
                    Next
                        Me.ComboBox2.List = dic.keys
                    Set dic = Nothing: Erase a
            End If
        Next
    End If

Normaly I know how to do it -> ComboBox2.AddItem = r.Offset(, 1).Value but in this case Me.ComboBox2.List = dic.keys is unknown for me. All code for ComboBox2 is written because of the dates format and sort.

 

[Digitborn.com]

Hi again, I see you have a look at this topic and if you're interested I post a new one concerning mainly your code with another question:

http://www.mrexcel.com/board2/viewtopic.php?p=1327537#1327537

I'm really much satisfied with your code, just wonder of some technics for making code work faster....

 

[Digitborn.com]

Kris helP,


I've changed my PartsData worksheet's structure, adding as Column A - ID. And now I can't fix your code to work properly. The problem is with the Sorting. The sorting criteria is Column B (where are the dates), It should sort A2:BM & .Range("B" & Rows.Count).End(xlUp).Row).

The code below, sorts the dates by Column B, but sorts B2:BM & .Range("B" & Rows.Count).End(xlUp).Row) and don't sort Column A (IDs). Whatever I try to change it doesn't work, don't know why .

Here's the full code for the ComboBox control:

Private Sub ComboBox2P2_Change()
If ComboBox2P2.ListIndex <> 0 And ComboBox2P2.ListIndex <> -1 Then
        With Worksheets("PartsData")
            If Len(Me.ComboBox2P2) > 0 Then
                Dim dic As Object, i As Long, d, a, z, Y
                z = Me.ComboBox2P2.Value
                Set dic = CreateObject("Scripting.Dictionary")
                dic.CompareMode = vbTextCompare
                    With .Range("B2:BM" & .Range("B" & Rows.Count).End(xlUp).Row)
                        .Sort .Range("A2"), xlAscending
                        a = .Offset(0, 0).Resize(, 2).Value
                    End With
                For i = 1 To UBound(a, 1)
                    If Not IsEmpty(a(i, 1)) Then
                        d = Format(a(i, 1), "dd-mmm-yy")
                        If Not dic.exists(d) And a(i, 2) = z Then dic.Add d, Nothing
                    End If
                Next: Y = dic.keys: Set dic = Nothing: Erase a
            End If
        End With
    End If
End Sub

p.s. If I change this line:

With .Range("B2:BM" & .Range("B" & Rows.Count).End(xlUp).Row)

to

With .Range("A2:BM" & .Range("B" & Rows.Count).End(xlUp).Row)

and trigger Event ComboBox2_Change, nothing's happening on the UserForm.

 

[Krishnakumar]

Hi,

Replace

With .Range("B2:BM" & .Range("B" & Rows.Count).End(xlUp).Row)
    .Sort .Range("A2"), xlAscending
    a = .Offset(0, 0).Resize(, 2).Value
End With

with

With .Range("A2:BM" & .Range("B" & Rows.Count).End(xlUp).Row)
    .Sort .Range("B2"), xlAscending
    a = .Offset(0, 1).Resize(, 2).Value
End With

HTH

 

[Digitborn.com]

Thanx, perfect!