CODEWITHSUNDEEP
excelvba
Andrew Buchanan
Andrew Buchanan

Reputation: 81

De-duplicate VBA scripting dictionary

I've got three combo boxes in a workbook that I want to daisy chain together. Item lists for each combo box refresh when you hit the down arrow on the keyboard after clicking the drop down button on the combo box. The second combo box list is dependent on the selection made in the first combo box. I've built these using scripting dictionaries.

strCustComboBox is the value in the previous combo box that the current combo box should be dependent on.

rngProject is looking at a range with lots of quote IDs in it. I offset from this column to the column where the values for the previous combo box are held and if this value is equal to strCustComboBox then add rngCompany value to the scripting dictionary

I'm running into a problem in the loop where I am trying to de-duplicate the rngCompany values written into the scripting dictionary that is used to build the list to be shown in the combo box. My code is below.

Sub UpdateComboBox1FromDashData()
Dim strCustComboBox As MSForms.ComboBox
Dim strComboBox As MSForms.ComboBox
Dim rngCompany As Range
Dim rngProject As Range
Dim d As Object, c As Variant, i As Long

Worksheets("QuoteEditor").Unprotect "xxxx"

Application.ScreenUpdating = False

Set strCustComboBox = ThisWorkbook.Worksheets("QuoteEditor").ComboBox4
Set strComboBox = ThisWorkbook.Worksheets("QuoteEditor").ComboBox1

If strCustComboBox = "" Then

      MsgBox "Please select a project first", vbOKCancel

Else
End If

ThisWorkbook.Worksheets("DashboardData").Select

Call FindLastRow("A", "10")

Set d = CreateObject("Scripting.Dictionary")
c = Range("A10:A" & strLastRow)


Set rngProject = ThisWorkbook.Worksheets("DashboardData").Range("A10:A" & strLastRow)

i = 1

For Each rngCompany In rngProject

    If UCase(rngCompany.Offset(, 7).Value) = UCase(strCustComboBox) Then

         If d.exists(rngCompany) = True Then

         Else
             d.Add rngCompany, i
             i = i + 1

         End If

     Else

     End If

Next rngCompany

For Each Item In d

     strComboBox.AddItem (Item)

Next Item

I think where I am using d.exists(rngCompany) is wrong but I'm not sure. When the subroutine finishes I still get duplicate data return to the combo box list.

I've also tried the code below as per the suggested duplicate thread:

With d

    For Each rngCompany In rngProject

        If UCase(rngCompany.Offset(, 7).Value) = UCase(strCustComboBox) Then

            If Not .exists(rngCompany) Then

                d.Add rngCompany, Nothing

            Else
            End If
        End If
    Next rngCompany
End With

Can anyone see where either of these are going wrong?

Upvotes: 1

Views: 1285

Answers (1)

Comintern
Comintern

Reputation: 22195

You hid the answer to this in your own question (emphasis mine):

where I am trying to de-duplicate the rngCompany values

There is no way for d.Exists(rngCompany) to return true the way that you have this written, because you are keying the Dictionary on the range, not its contents. Since the items you are testing are part of the iteration For Each rngCompany In rngProject, you are guaranteed to have only distinct ranges.

The solution is trivial - you need to explicitly call the default member of rngCompany:

If Not d.Exists(rngCompany.Value) Then
   d.Add rngCompany.Value, i
   i = i + 1   
End If

Upvotes: 4

Related Questions