Dim lig As Integer
Private Sub btnAjout_Click()

With Feuil1.ListObjects(1)

    With .DataBodyRange
        .Item(lig, 7) = cboTypeMarche.Value
        .Item(lig, 8) = cboChargeMission.Value
        .Item(lig, 9) = cboProcedure.Value
        .Item(lig, 11) = txtObjet.Value
        .Item(lig, 13) = txtAttributaire.Value
        .Item(lig, 14) = txtSiret.Value
        .Item(lig, 15) = txtlanceProcedure.Value
        .Item(lig, 16) = txtDateRemisePli.Value
        .Item(lig, 17) = txtNotifi.Value
        .Item(lig, 19) = cboNaturePrix.Value
        .Item(lig, 20) = txtEstimaBeoins.Value
        .Item(lig, 21) = txtMontantHT.Value
        .Item(lig, 23) = txtDataAvisCGEFI.Value
        .Item(lig, 24) = txtDateCAO.Value
        .Item(lig, 25) = txtNotifAR.Value
        .Item(lig, 26) = txtDateAvisAttrib.Value
        .Item(lig, 28) = cboCloture.Value
    End With

End Sub

Private Sub txtLot_Change()

With Feuil1.ListObjects(1)
    If txtLot = vbNullString Then
        txtAnnee = vbNullString
        txtNumero = vbNullString
        txtNumeroLot = vbNullString
        txtNumeroMarche = vbNullString
        Exit Sub
    End If
    
    If .ListRows.Count = 0 Then
        .ListRows.Add: lig = 1
    Else: .ListRows.Add: lig = .ListRows.Count
    End If
    With .DataBodyRange
        .Item(lig, 1) = Format(CDate(txtDate.Value), "mm/dd/yyyy")
        .Item(lig, 5) = txtLot.Value
        
        txtAnnee.Value = .Item(lig, 2).Value
        txtNumero = .Item(lig, 3).Value
        txtNumeroMarche = .Item(lig, 4).Value
        txtNumeroLot = .Item(lig, 6).Value
    End With
    
    Call Verification
    
End With
End Sub

Private Sub UserForm_Initialize()

txtDate.Value = Format(Now, "DD/MM/YYYY")

With Feuil3
    cboTypeMarche.List = .ListObjects("TTypeMarche").DataBodyRange.Value
    cboChargeMission.List = .ListObjects("TChargeMisssion").DataBodyRange.Value
    cboProcedure.List = .ListObjects("TProcedure").DataBodyRange.Value
    cboCodeNCMP.List = .ListObjects("TCodeNCMP").ListColumns(1).DataBodyRange.Value
    cboMode.List = .ListObjects("Tmode").DataBodyRange.Value
    cboNaturePrix.List = .ListObjects("Tprix").DataBodyRange.Value
    cboCloture.List = .ListObjects("TCloture").DataBodyRange.Value
End With
End Sub

Private Sub Verification() 'vérifier si pas deux fois le même lot pour le même numéro de marche
Dim c As Range
Dim i As Byte

With Feuil1.ListObjects(1)
    Set c = .ListColumns(5).DataBodyRange.Find(txtLot, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            
            If c.Offset(0, -1).Value = txtNumeroMarche And c.Value = CDbl(txtLot) Then i = i + 1
                
            If i = 2 Then
                MsgBox "Ce lot existe déjà pour le numéro de marché " & txtNumeroMarche, vbCritical, "Doublon de lot"
                .ListRows(lig).Delete
                txtLot = vbNullString
                Exit Do
            End If
            Set c = .ListColumns(5).DataBodyRange.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
    End If
End With
End Sub