Combobox fill data = rngFind.Offset

[asyamonique]

[TABLE="width: 184"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]column Z[/TD]
[TD]column AA[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A01[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A02[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]A03[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]A04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A08[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A09[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B08[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B09[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D5[/TD]
[/TR]
</tbody>[/TABLE]


Good Day,
Column Z datas are unique values which listed column AA values
Is it possble to load combo02 when selecting the related values from combo01
If ı select A from combo01 A1 to A10 will load to combo02 on userform.
Many thanks

 

[MickG]

Try this:-
Place this code in your Userform Module

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] P [COLOR=navy]As[/COLOR] Variant
 ComboBox2.Clear
     For Each P In Dic(ComboBox1.Value) 
        ComboBox2.AddItem P
     [COLOR=navy]Next[/COLOR] P
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, Dn1 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng2 [COLOR=navy]As[/COLOR] Range, Dn2 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng1 = Range(Range("Z1"), Range("Z" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng2 = Range(Range("AA1"), Range("AA" & Rows.Count).End(xlUp))

[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn1 [COLOR=navy]In[/COLOR] Rng1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn2 [COLOR=navy]In[/COLOR] Rng2
        [COLOR=navy]If[/COLOR] Not Dic.Exists(Dn1.Value) [COLOR=navy]Then[/COLOR] _
        Dic.Add Dn1.Value, New Collection
        [COLOR=navy]If[/COLOR] Left(Dn2.Value, 1) = Dn1.Value [COLOR=navy]Then[/COLOR] _
        Dic.Item(CStr(Dn1.Value)).Add CStr(Dn2.Value)
    [COLOR=navy]Next[/COLOR] Dn2
[COLOR=navy]Next[/COLOR] Dn1
ComboBox1.List = Dic.keys
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[asyamonique]

Hi Mick,
Thanks for the code unfortunately combo02 empty, its not loaded with data

 

[MickG]

Thanks for the code unfortunately combo02 empty, its not loaded with data

The combobox names in my code are "ComboBox1 and Combobox2", Do your Comboboxes have the right names ??

 

[asyamonique]

Yes

 

[MickG]

Try this example:-
https://app.box.com/s/zukoz5ju94bo3xp6ru9rsjyceztq9ocb

 

[asyamonique]

Mick, that code only works for the sample I've attached, if there is more optional samples like below, it does gives error like Run-time error'380':


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]AA[/TD]
[TD="width: 64"]AA01[/TD]
[/TR]
[TR]
[TD="class: xl63"]BA[/TD]
[TD]AA02[/TD]
[/TR]
[TR]
[TD="class: xl63"]CA[/TD]
[TD]AA03[/TD]
[/TR]
[TR]
[TD="class: xl63"]DA[/TD]
[TD]AA04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA08[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA09[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AA10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA07[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA08[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA09[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BA10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA04[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DA01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DA02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DA03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DA04[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Change this line where shown in red:-

 If Left(Dn2.Value, [B][COLOR=#ff0000]Len(Dn1.Value)[/COLOR][/B]) = Dn1.Value Then

 

[asyamonique]

Compile error:
Next without for

 

[MickG]

Try:-
https://app.box.com/s/yumvmgg7nfdz2vavtornsqn5ujmdgw9q