Private Sub CommandButton_Ajouter_Click()
Dim no_ligne As Long, civilite As String
Application.EnableEvents = False
With Sheets("Annuaire")
no_ligne = .Cells(Rows.Count, 1).End(xlUp).Row + 1
'Insertion des valeurs sur la feuille
.Cells(no_ligne, 3) = ComboBox1.Value
.Cells(no_ligne, 1) = ComboBox2.Value
.Cells(no_ligne, 5) = ComboBox3.Value
.Cells(no_ligne, 4) = ComboBox4.Value
.Cells(no_ligne, 2) = ComboBox5.Value
.Cells(no_ligne, 6) = ComboBox6.Value
.Cells(no_ligne, 7) = ComboBox7.Value
.Cells(no_ligne, 9) = CDate(DTPicker1.Value)
.Cells(no_ligne, 8) = ComboBox8.Value
.Cells(no_ligne, 10) = TextBox1.Value
.Cells(no_ligne, 11) = TextBox2.Value
.Cells(no_ligne, 12) = Val(Replace(TextBox1.Value, ",", ".")) * Val(Replace(TextBox2.Value, ",", ".")) 'Cells(no_ligne, 12) = CDbl(TextBox1.Value) * CDbl(TextBox2.Value) 'Cells(no_ligne, 12) = Val(Replace(TextBox1, ",", ".")) * Val(Replace(TextBox2, ",", "."))
.Cells(no_ligne, 12).Style = "Comma"
End With
With Sheets("Annuaire")
If .Cells(no_ligne, "H") = "Vente" Or .Cells(no_ligne, "H") = "Rachat" Then
.Cells(no_ligne, "M") = .Cells(no_ligne, "L")
Else
.Cells(no_ligne, "M") = ""
End If
End With
Application.EnableEvents = True
MsgBox ("la ligne a été ajoutée avec succés" & ". Le montant total est " & Format(Cells(no_ligne, 12).Value, "#,##0.00"))
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.TextBox1 = ""
Me.TextBox2 = ""
Sheets("Annuaire").Columns("A:M").AutoFit
End Sub
Bonsoir à tous
@adilprodigy
D'où tu sort ceci :
Cells(no_ligne, 13).Value = "=IF(OR(Cells(no_ligne, 8)=""Vente"", Cells(no_ligne, 8)=""Rachat""), Cout(Cells(no_ligne, 6)),"""")"
Si tu utilise la formule en anglais c'est:
Cells(no_ligne, 13).Formula ="=IF(OR(H3=""Vente"", H3=""Rachat""), F3,"""")"
With Cells(no_ligne, 13)
.Value = .Value
End With
Si j'ai bien compris la formule, en VBA ça donne
If Cells(no_ligne, "H") = "Vente" Or Cells(no_ligne, "H") = "Rachat" Then
Cells(no_ligne, "M") = Cells(no_ligne, "L")
Else
Cells(no_ligne, 13) = ""
End If
VB:Private Sub CommandButton_Ajouter_Click() Dim no_ligne As Long, civilite As String Application.EnableEvents = False With Sheets("Annuaire") no_ligne = .Cells(Rows.Count, 1).End(xlUp).Row + 1 'Insertion des valeurs sur la feuille .Cells(no_ligne, 3) = ComboBox1.Value .Cells(no_ligne, 1) = ComboBox2.Value .Cells(no_ligne, 5) = ComboBox3.Value .Cells(no_ligne, 4) = ComboBox4.Value .Cells(no_ligne, 2) = ComboBox5.Value .Cells(no_ligne, 6) = ComboBox6.Value .Cells(no_ligne, 7) = ComboBox7.Value .Cells(no_ligne, 9) = CDate(DTPicker1.Value) .Cells(no_ligne, 8) = ComboBox8.Value .Cells(no_ligne, 10) = TextBox1.Value .Cells(no_ligne, 11) = TextBox2.Value .Cells(no_ligne, 12) = Val(Replace(TextBox1.Value, ",", ".")) * Val(Replace(TextBox2.Value, ",", ".")) 'Cells(no_ligne, 12) = CDbl(TextBox1.Value) * CDbl(TextBox2.Value) 'Cells(no_ligne, 12) = Val(Replace(TextBox1, ",", ".")) * Val(Replace(TextBox2, ",", ".")) .Cells(no_ligne, 12).Style = "Comma" End With With Sheets("Annuaire") If .Cells(no_ligne, "H") = "Vente" Or .Cells(no_ligne, "H") = "Rachat" Then .Cells(no_ligne, "M") = .Cells(no_ligne, "L") Else .Cells(no_ligne, "M") = "" End If End With Application.EnableEvents = True MsgBox ("la ligne a été ajoutée avec succés" & ". Le montant total est " & Format(Cells(no_ligne, 12).Value, "#,##0.00")) Me.ComboBox1.Value = "" Me.ComboBox2.Value = "" Me.ComboBox3.Value = "" Me.ComboBox4.Value = "" Me.ComboBox5.Value = "" Me.ComboBox6.Value = "" Me.ComboBox7.Value = "" Me.TextBox1 = "" Me.TextBox2 = "" Sheets("Annuaire").Columns("A:M").AutoFit End Sub
With Sheets("Annuaire")
If .Cells(no_ligne, "H") = "Vente" Or .Cells(no_ligne, "H") = "Rachat" Then
.Cells(no_ligne, "M") = .Cells(no_ligne, "L")
[COLOR=#0080ff] 'Je veux insérer ici une nouvelle sous-condition après cette ligne[/COLOR]. [COLOR=#0080ff]Si la valeur de Cells(no_ligne, "M") = 10 alors un Vbcritical "Attention valeur égale à 10"[/COLOR]
Else
.Cells(no_ligne, "M") = ""
End If
End With