Recherche CP

Private Sub ComboBox_ville_Change()
longueurtexte = Len(ComboBox_ville.Value)
For i = 2 To Sheets("Feuil1").Range("A65535").End(xlUp).Row
If Left(Sheets("Feuil1").Cells(i, 1), longueurtexte) = ComboBox_ville.Value Then
TextBox_CP.Value = Sheets("Feuil1").Cells(i, 2).Value
TextBox_dept.Value = Sheets("Feuil1").Cells(i, 3).Value
TextBox1.Value = Sheets("Feuil1").Cells(i, 4).Value
TextBox2.Value = Sheets("Feuil1").Cells(i, 5).Value
TextBox3.Value = Sheets("Feuil1").Cells(i, 6).Value
End If
Next i
End Sub

Dim f
Private Sub UserForm_Initialize()
  Set f = Sheets("BD")
  temp = Range(f.[a2], f.[A65000].End(xlUp)).Value ' tableau 1 To n,1 To 1
  Call Tri(temp, 1, UBound(temp, 1))
  Me.ComboBox_ville.List = temp
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = f.Range("b2:b" & f.[b65000].End(xlUp).Row) ' tableau a(n,1) pour rapidité
  For i = LBound(a) To UBound(a)
    If a(i, 1) <> "" Then MonDico(a(i, 1)) = ""
  Next i
  Me.ComboBox_Cp.List = MonDico.keys
End Sub

Private Sub ComboBox_ville_click()
  Set c = f.[A:A].Find(what:=Me.ComboBox_ville)
  If Not c Is Nothing Then
    Me.TextBox_CP = c.Offset(, 1)
    Me.TextBox_dept = c.Offset(, 2)
  End If
End Sub

Private Sub ComboBox_Cp_click()
  Me.ComboBox_ville2.Clear
  Set c = f.[B:B].Find(what:=Me.ComboBox_Cp)
  If Not c Is Nothing Then
    premier = c.Address
    Do
      Me.ComboBox_ville2.AddItem c.Offset(, -1)
      Set c = f.[B:B].FindNext(c)
    Loop While Not c Is Nothing And c.Address <> premier
  End If
End Sub

Sub Tri(a, gauc, droi) ' Quick sort
  ref = a((gauc + droi) \ 2, 1)
  g = gauc: d = droi
  Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
    If g <= d Then
      temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
      g = g + 1: d = d - 1
    End If
  Loop While g <= d
  If g < droi Then Call Tri(a, g, droi)
  If gauc < d Then Call Tri(a, gauc, d)
End Sub

Dim f
Private Sub UserForm_Initialize()
  Set f = Sheets("BD")
  temp = Range(f.[a2], f.[A65000].End(xlUp)).Value ' tableau 1 To n,1 To 1
  Call Tri(temp, 1, UBound(temp, 1))
  Me.ComboBox_ville.List = temp
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = f.Range("b2:b" & f.[b65000].End(xlUp).Row) ' tableau a(n,1) pour rapidité
  For i = LBound(a) To UBound(a)
    If a(i, 1) <> "" Then MonDico(a(i, 1)) = ""
  Next i
  Me.ComboBox_Cp.List = MonDico.keys
End Sub

Private Sub ComboBox_ville_click()
  Set c = f.[A:A].Find(what:=Me.ComboBox_ville)
  If Not c Is Nothing Then
    Me.TextBox_CP = c.Offset(, 1)
    Me.TextBox_dept = c.Offset(, 2)
    Me.TextBox1 = c.Offset(, 3)
  End If
End Sub

Private Sub ComboBox_Cp_click()
  Me.ComboBox_ville2.Clear
  Set c = f.[B:B].Find(what:=Me.ComboBox_Cp)
  If Not c Is Nothing Then
    premier = c.Address
    Do
      Me.ComboBox_ville2.AddItem c.Offset(, -1)
      Set c = f.[B:B].FindNext(c)
    Loop While Not c Is Nothing And c.Address <> premier
  End If
End Sub

Private Sub ComboBox_ville2_click()
  Set c = f.[A:A].Find(what:=Me.ComboBox_ville2)
  If Not c Is Nothing Then
   ' Me.TextBox_CP2 = c.Offset(, 1)
    Me.TextBox_dept2 = c.Offset(, 2)
    Me.TextBox1 = c.Offset(, 3)
  End If
End Sub

Sub Tri(a, gauc, droi) ' Quick sort
  ref = a((gauc + droi) \ 2, 1)
  g = gauc: d = droi
  Do
    Do While a(g, 1) < ref: g = g + 1: Loop
    Do While ref < a(d, 1): d = d - 1: Loop
    If g <= d Then
      temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
      g = g + 1: d = d - 1
    End If
  Loop While g <= d
  If g < droi Then Call Tri(a, g, droi)
  If gauc < d Then Call Tri(a, gauc, d)
End Sub