{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["BaseDA","Feuil1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"BaseDA","index":0,"n_rows":15,"n_cols":13,"rows_kept":15,"cut":false,"hidden":false,"merged":[[0,0,1,7],[0,11,1,12]]}
["BDA : Adhérent(e)s avec formulaire de saisies et gestion des cotisations pour une association ",null,null,null,null,null,null,null,"Total cotisations :","Total         déjà payer : ","Total          restant dû :"]
[null,null,null,null,null,null,null,null,{"$f":" SUM($I$4:$I$64993)","v":2148},{"$f":" SUM($J$4:$J$64993)","v":482},{"$f":" I2 - J2","v":1666}]
["Date adhésion","Civilité","Nom","Prénom","Adresse complète","Téléphone","E-mail","Date naissance","Cotisation du","Déjà payer","Restant dû","Mois Dû","Mode de paiement"]
["27/01/2020","Mme","AZAZA","Momo","45 Boulevard Ssssssss 33000",202020202,"momoa@ret.com","25/04/1985",360,30,330,11,"Prélèvement"]
["27/01/2020","Mr","DERT","Paze","02 Passage Du Mmmmm 69000 Lyon",606060606,"pazedert@bgr.fr","07/06/1979",240,0,240,12]
["27/01/2020","Mme","FYFY","Bou","103 Quai De Ggggggggg 31000 Toulouse",707070707,"fyfybou@mos.org","05/05/1999",120,60,"75.00 €",6,"Espèces"]
["27/01/2020","Mr","GANGAN","Fers","14 Rue Vbnvbn 94000 Créteil","10 10 10 10 10","","07/06/1945",120,60,"60.00 €",6,"Chèque"]
["27/01/2020","Mr","KOLO","Juju","24 Route Ububu 91000","00 00 00 00 00","kolojuju@mlo.fr","12/02/1955",180,30,150,10,"Virement"]
["27/01/2020","Mr","LONLON","Tout","Chemin Des Hhhhhhhhhhh 45000",909090909,"tout.lonlon@cal.net","17/11/2005",120,20,100,11,"Espèces"]
["27/01/2020","Mme","MOMOL","Pala","45 Avenue Ttttttttttttttttttttt 48100 Bkbkbkbkbk","03 03 03 03 03","","14/12/1963",156,52,104,8,"Prélèvement"]
["01/02/2020","Mme","RARARA","Tutu","25 Rue De Borrrr 75000",505050505,"rararatutu@dcf.fr","12/06/1980",180,90,90,6,"Chèque"]
["14/02/2020","Mme","TCHOU","Lou","3 Rue De Wwwwwwww","01 01 01 01 01","tchoulou@cvb.fr","10/09/1975",120,0,"120.00 €",12,""]
["30/03/2020","Mr","WIWI","Dédé","65 Avenue Vvvvvvvvv 94000",808080808,"","19/11/1990",240,40,200,10,"Prélèvement"]
["27/09/2022","Mr","TACO","Tac","320 Rogneugneu 10000 Pie","01 02 03 04 05","tacotac@cerf.fr","20/08/1957",144,72,72,6,"Chèque"]
["28/09/2022","Mme","ROUROU","Zaza","3 Rue Des Turlutes","03 33 66 69 99","zazarourou@khv.com","01/12/2005",168,28,140,10,"Espèces"]

{"sheet":"Feuil1","index":1,"n_rows":124,"n_cols":19,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
[]
[null,"If WorksheetFunction.CountIf(Sheets(\"BaseDA\").Range(A!A), Me.txtNom1.Value) = 0 Then"]
[]
[null," With Me"]
[null,"    ",null,null,null,null,null,null,null,null,null,null,null,"slach"]
[null,"    .txtPrenom2 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 2, 0)",null,null,null,null,null,null,null,null,null,null,null,"Dim Valeur As Byte"]
[null,"    .txtDateN3 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 3, 0)",null,null,null,null,null,null,null,null,null,null,null,"        txtDateN3.MaxLength = 10"]
[null,"    .txtAdresse4 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 4, 0)",null,null,null,null,null,null,null,null,null,null,null,"        Valeur = Len(txtDateN3)"]
[null,"    .txtPhone5 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 5, 0)",null,null,null,null,null,null,null,null,null,null,null,"    If Valeur = 2 Or Valeur = 5 Then txtDateN3 = txtDateN3 & \"/\""]
[null,"    .txtEmail6 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 6, 0)",null,null,null,null,null,null,null,null,null,null,null,"    "]
[null,"    .txtDateA7 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 7, 0)",null,null,null,null,null,null,null,null,null,null,null,"    If Not IsDate(TextBox1) Then"]
[null,"    .txtCotisation8 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 8, 0)",null,null,null,null,null,null,null,null,null,null,null,"        MsgBox \"Format incorrect\""]
[null,"    .txtPayer9 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 9, 0)",null,null,null,null,null,null,null,null,null,null,null,"        TextBox1 = \"\""]
[null,"    .txtMode10 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), 11, 0)",null,null,null,null,null,null,null,null,null,null,null,"        "]
[null,"    ",null,null,null,null,null,null,null,null,null,null,null,"        Else"]
[null,"    End With",null,null,null,null,null,null,null,null,null,null,null,"        MsgBox \"Format correct\""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"        "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"    End If"]
[null,"Enregistrer un fichier excel a la fermeture d'un userform"]
[]
[null,"Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)",null,null,null,null,null,null,null,null,"If txtDateN3 Like \"##/##/####\" Then",null,null,null,null,null,null,null,"        .txtPrenom2 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), B, 0)"]
[null,"If CloseMode = 0 Then",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"        .txtDateN3 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), C, 0)"]
[null,"Application.DisplayAlerts = False",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"        .txtAdresse4 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), D, 0)"]
[null,"ActiveWorkbook.Save",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"        .txtPhone5 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), E, 0)"]
[null,"End If",null,null,null,null,null,null,null,null," With Sheets(\"Paramètres\")",null,null,null,null,null,null,null,"        .txtEmail6 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), F, 0)"]
[null,"End Sub",null,null,null,null,null,null,null,null,"        .Range(\"B4\") = Replace(TextBox2.Value, \".\", \"/\")",null,null,null,null,null,null,null,"        .txtDateA7 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), G, 0)"]
[null,null,null,null,null,null,null,null,null,null,"        .Range(\"B5\") = Replace(TextBox3.Value, \".\", \"/\")",null,null,null,null,null,null,null,"        .txtCotisation8 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), H, 0)"]
[null,null,null,null,null,null,null,null,null,null,"        .Range(\"B6\") = Replace(TextBox4.Value, \".\", \"/\")",null,null,null,null,null,null,null,"        .txtPayer9 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), i, 0)"]
[null,"MsgBox \"Nom non trouvé\", vbInformation + vbOKOnly, \"Non trouvé\"",null,null,null,null,null,null,null,null,"        .Range(\"B7\") = Replace(TextBox5.Value, \".\", \"/\")",null,null,null,null,null,null,null,"        .txtMode11 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), K, 0)"]
[null,null,null,null,null,null,null,null,null,null,"        .Range(\"B8\") = Replace(TextBox7.Value, \".\", \"/\")",null,null,null,null,null,null,null,"        .txtMode10 = Application.WorksheetFunction.VLookup(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), J, 0)"]
[null,null,null,null,null,null,null,null,null,null,"    End With"]
[]
[null,"Dim DL As Integer"]
[null,"Dim X As Integer",null,null,null,null,null,null,null,null,null,"Sub cherche()"]
[]
[null,"DL = Cells(Application.Rows.Count, \"A\").End(xlUp).Row",null,null,null,null,null,null,null,null,null,"On Error Resume Next"]
[null,"For X = 2 To DL",null,null,null,null,null,null,null,null,null,"With Sheets(2)","xxx = Sheets(1).Range(\"A3\")"]
[null,"    With Sheets(\"NOUVEAU\")",null,null,null,null,null,null,null,null,null,"    MsgBox .Columns(\"C\").Find(xxx, .Range(\"C9\"), xlValues).Offset(0, 1)"]
[null,"        VL = WorksheetFunction.VLookup(.Range(\"K\" & X).Value, Sheets(\"ANCIEN\").Range(\"K1:L1000\"), 2, False)",null,null,null,null,null,null,null,null,null,"End With"]
[null,"        If Not IsError(VL) Then     'traitement erreur"]
[null,"            .Range(\"L\" & X).Value = VL",null,null,null,null,null,null,null,null,null,"End Sub"]
[null,"        End If"]
[null,"    End With"]
[]
[]
[]
[null,null,"'déclaration des variables :"]
[null,null,"Dim Trouve As Range, PlageDeRecherche As Range",null,null,null,null,null,null,null,null,"Sub Principale()"]
[null,null,"Dim Valeur_Cherchee As String, AdresseTrouvee As String",null,null,null,null,null,null,null,null,"Dim Plage As Range"]
[null,null,null,null,null,null,null,null,null,null,null,"Dim Lignes(), i As Long"]
[null,null,"'********* à adapter ***********",null,null,null,null,null,null,null,null,"Dim Texte As String"]
[null,null,"'affectation de valeurs aux variables :",null,null,null,null,null,null,null,null,"Dim Flag As Boolean"]
[null,null,"    'on cherche le mot \"Trouve\""]
[null,null,"Valeur_Cherchee = \"Trouve\"",null,null,null,null,null,null,null,null,"Set Plage = Sheets(\"Feuil1\").Range(\"A1:A20\") 'plage de recherche"]
[null,null,"    'dans la première colonne de la feuille active",null,null,null,null,null,null,null,null,"Texte = \"mot\"   'expression cherchée"]
[null,null,"Set PlageDeRecherche = ActiveSheet.Columns(1)",null,null,null,null,null,null,null,null,"Flag = Find_Next(Plage, Texte, Lignes())  'appel de la fonction"]
[null,null,"'*******************************",null,null,null,null,null,null,null,null,"If Flag Then  'si fonction retourne Vrai = expression trouvée dans la plage"]
[null,null,null,null,null,null,null,null,null,null,null,"    For i = LBound(Lignes) To UBound(Lignes)   'restitution des lignes correspondantes"]
[null,null,"'méthode find, ici on cherche la valeur exacte (LookAt:=xlWhole)",null,null,null,null,null,null,null,null,"        Debug.Print Lignes(i)"]
[null,null,"Set Trouve = PlageDeRecherche.Cells.Find(what:=Valeur_Cherchee, LookAt:=xlWhole)",null,null,null,null,null,null,null,null,"    Next i"]
[null,null,null,null,null,null,null,null,null,null,null,"Else"]
[null,null,"'traitement de l'erreur possible : Si on ne trouve rien :",null,null,null,null,null,null,null,null,"    MsgBox \"L'expression : \" & Texte & \" n'a pas été trouvée dans la plage : \" & Plage.Address"]
[null,null,"If Trouve Is Nothing Then",null,null,null,null,null,null,null,null,"End If"]
[null,null,"    'ici, traitement pour le cas où la valeur n'est pas trouvée",null,null,null,null,null,null,null,null,"End Sub"]
[null,null,"    AdresseTrouvee = Valeur_Cherchee & \" n'est pas présent dans \" & PlageDeRecherche.Address"]
[null,null,"Else",null,null,null,null,null,null,null,null,"'Sources : Michel_m"]
[null,null,"    'ici, traitement pour le cas où la valeur est trouvée",null,null,null,null,null,null,null,null,"'http://www.commentcamarche.net/forum/affich-31432413-importation-de-donnees-sans-doublons#9"]
[null,null,"    AdresseTrouvee = Trouve.Address",null,null,null,null,null,null,null,null,"Function Find_Next(Rng As Range, Texte As String, Tbl()) As Boolean"]
[null,null,"End If",null,null,null,null,null,null,null,null,"Dim Nbre As Integer, Lig As Long, Cptr As Long"]
[null,null,"MsgBox AdresseTrouvee"]
[null,null,"'vidage des variables",null,null,null,null,null,null,null,null,"    Nbre = Application.CountIf(Rng, Texte)"]
[null,null,"Set PlageDeRecherche = Nothing",null,null,null,null,null,null,null,null,"    If Nbre > 0 Then"]
[null,null,"Set Trouve = Nothing",null,null,null,null,null,null,null,null,"        ReDim Tbl(Nbre - 1)"]
[null,null,null,null,null,null,null,null,null,null,null,"        Lig = 1"]
[null,null,null,null,null,null,null,null,null,null,null,"        For Cptr = 0 To Nbre - 1"]
[null,null,"With Me",null,null,null,null,null,null,null,null,"            Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row"]
[null,null,"     ",null,null,null,null,null,null,null,null,"            Tbl(Cptr) = Lig"]
[null,null,"        Range(\"B\" & L).Value = Me.txtPrenom2",null,null,null,null,null,null,null,null,"        Next"]
[null,null,"        Range(\"C\" & L).Value = CDate(Me.txtDateN3)",null,null,null,null,null,null,null,null,"    Else"]
[null,null,"        Range(\"D\" & L).Value = Me.txtAdresse4",null,null,null,null,null,null,null,null,"        GoTo Absent"]
[null,null,"        Range(\"E\" & L).Value = Me.txtPhone5",null,null,null,null,null,null,null,null,"    End If"]
[null,null,"        Range(\"F\" & L).Value = Me.txtEmail6",null,null,null,null,null,null,null,null,"    Find_Next = True"]
[null,null,"        Range(\"G\" & L).Value = CDate(Me.txtDateA7)",null,null,null,null,null,null,null,null,"    Exit Function"]
[null,null,"        Range(\"H\" & L).Value = CCur(Me.txtCotisation8)",null,null,null,null,null,null,null,null,"Absent:"]
[null,null,"        Range(\"I\" & L).Value = CCur(Me.txtPayer9)",null,null,null,null,null,null,null,null,"    Find_Next = False"]
[null,null,"        Range(\"K\" & L).Value = Me.txtModeP10",null,null,null,null,null,null,null,null,"End Function"]
[null,null,"        Range(\"J\" & L).Value = Me.txtRestant11"]
[null,null,"        "]
[null,null,"     End With",null,null,null,null,null,null,null,null,"Nota 1 : Pour une colonne entière, dans la procédure d'appel, remplacez :"]
[null,null,null,null,null,null,null,null,null,null,null,"Set Plage = Sheets(\"Feuil1\").Range(\"A1:A20\")"]
[]
[null,null," frmsaisie.c.Text = Source.Cells(I, 6).Value",null,null,null,null,null,null,null,null,"par :"]
[null,null,"            frmsaisie.txtSociété.Value = Source.Cells(I, 1).Value",null,null,null,null,null,null,null,null,"Set Plage = Sheets(\"Feuil1\").Columns(1)"]
[null,null,"            frmsaisie.txtAvisé.Value = Source.Cells(I, 2).Value"]
[null,null,"            frmsaisie.txtDate.Value = Source.Cells(I, 3).Value"]
[null,null,"            frmsaisie.txtCommentaire.Value = Source.Cells(I, 4).Value 'plus nécessaire",null,null,null,null,null,null,null,null,"Nota 2 : Pour retourner les adresses des cellules plutôt que leur numéro de ligne, dans la fonction Find_Next, remplacez :"]
[null,null,"            frmsaisie.txtContacte.Value = Source.Cells(I, 5).Value",null,null,null,null,null,null,null,null,"Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row"]
[null,null,"            frmsaisie.txtFonction.Value = Source.Cells(I, 7).Value",null,null,null,null,null,null,null,null,"Tbl(Cptr) = Lig"]
[null,null,"            frmsaisie.txtTéléphone.Value = Source.Cells(I, 8).Value"]
[null,null,"            frmsaisie.txtEmail.Value = Source.Cells(I, 9).Value",null,null,null,null,null,null,null,null,"par :"]
[null,null,null,null,null,null,null,null,null,null,null,"Lig = Rng.Find(Texte, Cells(Lig, Rng.Column), xlValues).Row"]
[null,null,null,null,null,null,null,null,null,null,null,"Tbl(Cptr) = Cells(Lig, Rng.Column).Address"]
[]
[null,null,null,"frmsaisie.ctrl.Caption = Range(\"B\" & L).Value "]
[]
[null,null,null,null,null,null,null,null,null,null,null," If WorksheetFunction.CountIf(Sheets(\"BaseDA\").Range(\"A:A\"), Me.txtNom1.Value) = 0 Then"]
[null,null,null,null,null,null,null,null,null,null,null,"     "]
[null,null,null,null,null,null,null,null,null,null,null,"        MsgBox \"Nom pas trouvé\", vbInformation + vbOKOnly, \"Adhérent non trouvé\""]
[null,null,null,null,null,null,null,null,null,null,null,"        "]
[null,null,null,null,null,null,null,null,null,null,null,"     End If"]
[]
[null,null,null,null,null,null,null,null,null,null,null,"     With Me"]
[null,null,null,null,null,null,null,null,null,null,null,"                                        "]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtPrenom2 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), B, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtDateN3 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), C, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtAdresse4 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), D, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtPhone5 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), E, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtEmail6 = Application.WorksheetFunction.counyif(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), F, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtDateA7 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), G, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtCotisation8 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), H, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtPayer9 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), i, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"    .txtMode10 = Application.WorksheetFunction.CountIf(CLng(Me.txtNom1), Sheets(\"BaseDA\").Range(\"source\"), K, 0)"]
[null,null,null,null,null,null,null,null,null,null,null,"                                        "]
[null,null,null,null,null,null,null,null,null,null,null,"    End With"]