Sub ExtraireNumClient() 'pour éviter la formule dans la colonne A de la feuille Etablissement Sage
'permet d'extraire le numéro de client (quand il existe)
Dim TabEta() As Variant
With Sheets("Etablissements Sage")
TabEta = .UsedRange.Value
For i = LBound(TabEta, 1) + 1 To UBound(TabEta, 1)
If InStr(1, TabEta(i, 3), "-") <> 0 Then
'NumClientDansC = (Split(TabEta(i, 3), "-")(1))
'On Error Resume Next
TabEta(i, 1) = IIf(IsNumeric(Split(TabEta(i, 3), "-")(1)), Split(TabEta(i, 3), "-")(1), IIf(IsNumeric(Split(TabEta(i, 4), "-")(1)), Split(TabEta(i, 4), "-")(1), "Pas de Num Client"))
Else
TabEta(i, 1) = IIf(IsNumeric(Split(TabEta(i, 4), "-")(1)), Split(TabEta(i, 4), "-")(1), "Pas de Num Client")
End If
Next i
.UsedRange = TabEta
End With
End Sub
Sub SociétéImmos()
Dim TabEta() As Variant
Dim TabImmos() As Variant
Set dico = CreateObject("scripting.dictionary")
With Sheets("Etablissements Sage")
TabEta = .UsedRange.Value
End With
With Sheets("Sociétés Immos")
fin = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("F2:G" & fin).ClearContents
TabImmos = .Range("A2:G" & fin).Value
End With
For i = LBound(TabEta, 1) + 1 To UBound(TabEta, 1)
If Not dico.exists(TabEta(i, 1)) Then
dico.Add TabEta(i, 1), TabEta(i, 2) & "***" & TabEta(i, 4)
End If
Next i
For i = LBound(TabImmos, 1) To UBound(TabImmos, 1)
If dico.exists(TabImmos(i, 4)) Then
TabImmos(i, 6) = Split(dico(TabImmos(i, 4)), "***")(0)
TabImmos(i, 7) = Split(dico(TabImmos(i, 4)), "***")(1)
End If
Next i
With Sheets("Sociétés Immos")
.Range("A2:G" & fin) = TabImmos
End With
End Sub