Object required error in dictionary

[Abdo]

Hi experts
I search for way to fixing my problem .
I know from the beginning my question is so complicated . the userform contains multiple rows , each row contains( 4 comboboxes & 1 textbox) . so when see the lables for all of comboboxes should match with the headers in sheet and when select item from combobox1 automatically fill combobox2 and when select item from combobox2 automatically fill combobox3 and when select item from combobox3 automatically fill combobox4 and when select item from combobox4 will fill the textbox 19 . so the columns 1,2,3,4,5 on userform link with columns B,C,D,E,F . every combobox depends on each other of them , when select one of them and depends on adjacent cell when match the columns inside sheet for each combobox when selected item for combobox separately .
so far it gives error object required in this line
If Not dic(a(i, 3)).exists(a(i, 4))
this is the whole code

Option ExplicitPrivate dic As Object

Private Sub UserForm_Initialize()
Dim a, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("PRICES").Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
For ii = 1 To UBound(a, 2)
a(i, ii) = a(i, ii) & ""
Next
If Not dic.exists(a(i, 2)) Then
Set dic(a(i, 2)) = CreateObject("Scripting.Dictionary")
End If
If Not dic(a(i, 2)).exists(a(i, 3)) Then Set dic(a(i, 2))(a(i, 3)) = CreateObject("Scripting.Dictionary")

If Not dic(a(i, 3)).exists(a(i, 4)) Then Set dic(a(i, 3))(a(i, 4)) = CreateObject("Scripting.Dictionary")


dic(a(i, 2))(a(i, 3))(a(i, 4))(a(i, 5)) = a(i, 6)
Next
a = mySort(dic.keys)
For i = 1 To 10 Step 4
Me("combobox" & i).List = a
Next
End Sub


Private Sub ComboBox1_Change()
GetList 1
End Sub


Private Sub ComboBox2_Change()
GetList 2
End Sub


Private Sub ComboBox3_Change()
GetList 3
End Sub


Private Sub ComboBox4_Change()
GetList 4
End Sub


Private Sub ComboBox5_Change()
GetList 5
End Sub


Private Sub ComboBox6_Change()
GetList 6
End Sub


Private Sub ComboBox7_Change()
GetList 7
End Sub


Private Sub ComboBox8_Change()
GetList 8
End Sub


Private Sub ComboBox9_Change()
GetList 9
End Sub


Private Sub ComboBox10_Change()
GetList 10
End Sub


Private Sub ComboBox11_Change()
GetList 11
End Sub


Private Sub ComboBox12_Change()
GetList 12
End Sub


Private Sub GetList(CB)
Dim CB1, i As Long, x
CB1 = Application.Lookup(CB, Array(1, 5, 9, 13), Array(1, 5, 9, 13))
Me("textbox" & Application.RoundUp(CB1 / 4, 0)) = ""
For i = CB1 To CB1 + 3
If CB < i Then Me("combobox" & i).Clear
Next
If Me("combobox" & CB).ListIndex = -1 Then Exit Sub
x = CB1 + 3 - CB
If x = 2 Then
Me("combobox" & CB + 1).List = mySort(dic(Me("combobox" & CB1).Value).keys)
ElseIf x = 1 Then
Me("combobox" & CB + 1).List = mySort(dic(Me("combobox" & CB1).Value)(Me("combobox" & CB).Value).keys)
End If
If CB = CB1 + 3 Then Me("textbox" & Application.RoundUp(CB1 / 4, 0)) = _
dic(Me("combobox" & CB1).Value)(Me("combobox" & CB1 + 1).Value)(Me("combobox" & CB1 + 3).Value)
End Sub


Function mySort(a)
Dim i As Long, ii As Long, temp
For i = LBound(a) To UBound(a) - 1
For ii = i + 1 To UBound(a)
If a(i) > a(ii) Then
temp = a(i): a(i) = a(ii): a(ii) = temp
End If
Next
Next
mySort = a
End Function



I hope from expert helps me to complete this project .

[snb]

See

[Abdo]

thanks . I appreciate for your help, but unfortunately this doesn't what I want . the orginal code each combobox depends on each other of them . if you make a selection in one box (say ComboBox1), can be more than one option (e.g. ComboBox2) and I want filling all of the comboboxes at once instead of depend on just four comboboxes and no need the QTY by combobox . I don't want select just populate into textbox . your way I have to search the item for all combobxes , but the orginal code is flexable just select first combobox and will populate the others comboboxes when select next combobox for each row on userform where contains four comboboxes .
I hope to understand what I want . sorry this is my first thread if it's not clear !!

[Aflatoon]

Your original code appears to have skipped a level. You'd want:


If Not dic(a(i, 2))(a(i, 3)).exists(a(i, 4)) Then Set dic(a(i, 2))(a(i, 3))(a(i, 4)) = CreateObject("Scripting.Dictionary")

[Abdo]

aflatoon thanks but still gives the same error .actually I add this

If Not dic.exists(a(i, 3)) Then
Set dic(a(i, 3)) = CreateObject("Scripting.Dictionary")
End If
If Not dic(a(i, 3)).exists(a(i, 4)) Then Set dic(a(i, 3))(a(i, 4)) = CreateObject("Scripting.Dictionary")
this work up to this line
dic(a(i, 2))(a(i, 3))(a(i, 4))(a(i, 5)) = a(i, 6)
gives error mismatch. my knowladge in vba is not good but I try learning . cany you guide me please?

[Aflatoon]

Since you appear to have changed your code from what you posted, please post the current version. It's very hard to help if I'm looking at the wrong code. ;)

[Abdo]

here is the current version code

Option ExplicitPrivate dic As Object




Private Sub UserForm_Initialize()
Dim a, i As Long, ii As Long
Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("PRICES").Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
For ii = 1 To UBound(a, 2)
a(i, ii) = a(i, ii) & ""
Next
If Not dic.exists(a(i, 2)) Then
Set dic(a(i, 2)) = CreateObject("Scripting.Dictionary")
End If

If Not dic(a(i, 2)).exists(a(i, 3)) Then Set dic(a(i, 2))(a(i, 3)) = CreateObject("Scripting.Dictionary")
If Not dic.exists(a(i, 3)) Then
Set dic(a(i, 3)) = CreateObject("Scripting.Dictionary")
End If
If Not dic(a(i, 3)).exists(a(i, 4)) Then Set dic(a(i, 3))(a(i, 4)) = CreateObject("Scripting.Dictionary")




dic(a(i, 2))(a(i, 3))(a(i, 4))(a(i, 5)) = a(i, 6)

Next
a = mySort(dic.keys)
For i = 1 To 13 Step 4
Me("combobox" & i).List = a
Next
End Sub






Private Sub ComboBox1_Change()
GetList 1
End Sub


Private Sub ComboBox2_Change()
GetList 2
End Sub


Private Sub ComboBox3_Change()
GetList 3
End Sub


Private Sub ComboBox4_Change()
GetList 4
End Sub


Private Sub ComboBox5_Change()
GetList 5
End Sub


Private Sub ComboBox6_Change()
GetList 6
End Sub


Private Sub ComboBox7_Change()
GetList 7
End Sub


Private Sub ComboBox8_Change()
GetList 8
End Sub


Private Sub ComboBox9_Change()
GetList 9
End Sub


Private Sub ComboBox10_Change()
GetList 10
End Sub


Private Sub ComboBox11_Change()
GetList 11
End Sub


Private Sub ComboBox12_Change()
GetList 12
End Sub
Private Sub ComboBox13_Change()
GetList 13
End Sub


Private Sub GetList(CB)
Dim CB1, i As Long, x
CB1 = Application.Lookup(CB, Array(1, 5, 9, 13), Array(1, 5, 9, 13))
Me("textbox" & Application.RoundUp(CB1 / 4, 0)) = ""
For i = CB1 To CB1 + 3
If CB < i Then Me("combobox" & i).Clear
Next
If Me("combobox" & CB).ListIndex = -1 Then Exit Sub
x = CB1 + 3 - CB
If x = 2 Then
Me("combobox" & CB + 1).List = mySort(dic(Me("combobox" & CB1).Value).keys)
ElseIf x = 1 Then
Me("combobox" & CB + 1).List = mySort(dic(Me("combobox" & CB1).Value)(Me("combobox" & CB).Value).keys)
End If
If CB = CB1 + 3 Then Me("textbox" & Application.RoundUp(CB1 / 4, 0)) = _
dic(Me("combobox" & CB1).Value)(Me("combobox" & CB1 + 1).Value)(Me("combobox" & CB1 + 3).Value)
End Sub


Function mySort(a)
Dim i As Long, ii As Long, temp
For i = LBound(a) To UBound(a) - 1
For ii = i + 1 To UBound(a)
If a(i) > a(ii) Then
temp = a(i): a(i) = a(ii): a(ii) = temp
End If
Next
Next
mySort = a
End Function

[Aflatoon]

Did you intend to have an item with key a(i, 3) in both dic and dic(A(i, 2))?

As posted, your code doesn't make a lot of sense to me as you have a lot of nested dictionaries for no apparent reason.

[Abdo]

As posted, your code doesn't make a lot of sense to me as you have a lot of nested dictionaries for no apparent reason.
I thougt can fixing my problems but seems more complicated than I thought. to be honest this code is not mine. just I search too much in the internet to fit my project and found it .
the orginal code depends on column B,C,D but in my project should add column E .dealing with dictionary is too hard for me
the idea of this code each combobox depends on each other of them when select one by one especially if repeat the same item with different choices . I accept any alternitave code but I don't find it so far , also snb's code Doesn't meet my needs .