Problème dans un code VBA

[SPEA]

Bonjour le forum,

j'ai un soucis dans une macro, j'ai créé un USF qui me sert à recueillir des données saisies par un opérateur puis les transfert vers une feuille sur la 1° ligne disponible dans les colonnes D à K, cet USF s'ouvre quand on clique sur le bouton "Ajout".

Une 2° macro me sert à effectuer des calcul sur ces données transférées (module n°1, sub copie_formule).

Je voudrait que la macro copie_formule s'execute quand l'opérateur valide les données saisies dans l'USF en cliquant sur le bouton OK.

Voici le code du USF:

Private Sub CommandButton1_Click()
Dim derligne As Single
derligne = Sheets(1).Range("A65000").End(xlUp).Row
Sheets(1).Cells(derligne + 1, 4).Value = TextBox1.Value
Sheets(1).Cells(derligne + 1, 5).Value = TextBox2.Value
Sheets(1).Cells(derligne + 1, 6).Value = TextBox3.Value
Sheets(1).Cells(derligne + 1, 7).Value = TextBox4.Value
Sheets(1).Cells(derligne + 1, 8).Value = TextBox5.Value
Sheets(1).Cells(derligne + 1, 9).Value = TextBox6.Value
Sheets(1).Cells(derligne + 1, 10).Value = TextBox7.Value
Sheets(1).Cells(derligne + 1, 11).Value = TextBox8.Value
UserForm1.Hide
End Sub

J'ai essayé de mettre Call copie_formule à la suite de Userform1.hide
mais ca ne fonctionne pas.

Je joint le fichier.

 

[wilfried_42]

Re : Problème dans un code VBA

Bonjour

si j'ai tout compris, tu desires tirer les formule à chaque ajout de ligne
les 2 lignes en rouge

Private Sub CommandButton1_Click()
Dim derligne As Single
derligne = Sheets(1).Range("A65000").End(xlUp).Row
With Sheets(1)
    .Cells(derligne + 1, 4).Value = TextBox1.Value
    .Cells(derligne + 1, 5).Value = TextBox2.Value
    .Cells(derligne + 1, 6).Value = TextBox3.Value
    .Cells(derligne + 1, 7).Value = TextBox4.Value
    .Cells(derligne + 1, 8).Value = TextBox5.Value
    .Cells(derligne + 1, 9).Value = TextBox6.Value
    .Cells(derligne + 1, 10).Value = TextBox7.Value
    .Cells(derligne + 1, 11).Value = TextBox8.Value
[COLOR="Red"]    .Range(.Cells(derligne, 1), .Cells(derligne + 1, 3)).FillDown
    .Range(.Cells(derligne, 12), .Cells(derligne + 1, 17)).FillDown[/COLOR]
End With
UserForm1.Hide
End Sub

j'ai fait un essai, tu as une petite erreur dans ton choix de procedure pour le controle de date

tu as utilisé Change au lieu de Exit tu as mis un Cancel = true or c'est dans l'iterruption Exit qu'il y a cancel, de plus quand tu entre la date, à chaque caractere il t'affiche le message.

Bonne journée

 

[SPEA]

Re : Problème dans un code VBA

Bonjour Wilfried,

les formules des calculs est integrée dans la macro copie_formule; je veux juste eviter d'avoir à créer un 2° bouton pour lancer l'execution de cette macro.

je voudrais profiter du bouton déjà existant et executer la macro à la fin du transfert des données saisies dans l'USF vers la feuille.

 

[wilfried_42]

Re : Problème dans un code VBA

re:

j'ai vu ta macro, et normalement en fonction de l'ensemble (avec la macro precedente), elle ne devrait pas fonctionner

j'explique : tu mets à jour les colonnes C à K, dans les colonnes A à C tu as des formules, ainsi que dans les colonnes L à Q
tu n'as aucune mise à jour de la colonne A donc :

  DerLig = Sht.Range("[B][COLOR="Red"]A65536[/COLOR][/B]").End(xlUp).Row

en recherchant la derniere ligne sur la colonne A tu ne trouveras pas la derniere ligne
que fais tu des formules an colonne A,B et C ? elles ne sont pas dupliquées

Question : as tu vraiement besoin de dupliquer les formules dans une autre procedure que celle de l'userform

sinon, la procedure que je t'ai donnée, est testée et fonctionne

si oui, ta macro se lancait surement mais ne faisait rien pour les causes definies ci-dessus

 

[SPEA]

Re : Problème dans un code VBA

Re:

Sub Copie_formule()
  
  Dim DerLig As Integer, Sht As Worksheet
  Dim L As Integer, P As Integer
  
  Set Sht = Sheets("relevés 2008")
  DerLig = Sht.Range("A65536").End(xlUp).Row
  
  Sht.Range("L3").FormulaLocal = "=SI(ESTERREUR(M3/G3);NA();M3/G3)"
  Sht.Range("M3").FormulaLocal = "=(F3/16/7,2)*1000/(EXP(0,0239*(E3-20)))"
  Sht.Range("N3").FormulaLocal = "=SI(ESTERREUR((I3-I2)/B3);NA();ARRONDI((I3-I2)/B3;0))"
  Sht.Range("O3").FormulaLocal = "=SI(ESTERREUR((H3-H2)/B3);NA();ARRONDI((H3-H2)/B3;0))"
  Sht.Range("P3").FormulaLocal = "=SI(ESTERREUR((J3-J2)/B3);NA();ARRONDI((J3-J2)/B3;0))"
  Sht.Range("Q3").FormulaLocal = "=SI(ESTERREUR(O3-P3);NA();O3-P3)"
    
  ' Recopie des formules vers le bas
  Sht.Range("L3:Q" & DerLig).FillDown

End Sub

La macro effectue des calculs sur les colonnes L à Q et fonctionne trés bien.

J'ai juste ajouté un USF pour faciliter la saisie des données par l'opérateur et eviter des erreurs de saisie dans la mauvaise colonne.

Par contre il est vrai qu'il y a également le problème des colonnes A,B et C à voir mais avec mon niveau en VBA je préfère prendre les problèmes les uns apres les autres.

 

[wilfried_42]

Re : Problème dans un code VBA

re:

je viens de faire une essai manuellement, sans passer dans ton userform
j'ai donc mis une date et j'ai recopié les valeurs precedente de la colonne E à K

ensuite j'ai lancé manuellement, ta macro copie formule et comme prevu, elle ne copie pas les formules sur la derniere ligne (celle que j'ai saisi manuellement)

j'ai refait le test avec ton USF, en mettant Call Copie_formule devant ta ligne .Hide
j'y ai mis un point d'arret pour voir le deroulement de ta macro,
et ligne apres ligne, j'ai suivi le code, tout c'est bien deroulé, conformemment à mes previsions.

il a bien fait la duplication des formules, mais c'est arreté à la ligne precedente toujours à cause de la colonne A si tu mets D65536 à la place de A65536 dant ta recherche de derniere ligne c'est Ok !!!!

 

[SPEA]

Re : Problème dans un code VBA

Autant pour moi Wilfried tu avais raison sur toute la ligne, je cherchais le probleme dans le code de l'USF alors qu'il venait de la référence à la colonne A pour la derniere ligne.

J'ai modifié le code de copie_formule avec

DerLig = Sht.Range("D65536").End(xlUp).Row

ainsi que

Call Copie_formule

je j'ai ajouté avant

UserForm1.Hide

dans le code USF.

As tu une idee pour integrer les colonnes ABC dans la macro recopie_formules car elles ne sont pas à coté des colonnes L à Q ?

 

[wilfried_42]

Re : Problème dans un code VBA

re:


ton copie_formule, comme tu y tiens

il n'est pas souhaitable que tu fasses comme cela, mettre tes formule le ligne 3 et faire un filldown sur toute la colonne, deja avec une vingtaine de ligne ca se voit, alors quand tu en auras 300

300 * 9 colonnes, 2700 copies + 2700 calculs c'est un peut lourd et la tu attendras la fin de la macro
Comme à chaque enregistrement, tu recopie vers le bas, la ligne precedente contient donc la formule

derligne =   DerLig = Sht.Range("A65536").End(xlUp).Row
sht.range("A" & derligne & ":C" & derligne +1).filldown
sht.range("L" & derligne & ":Q" & derligne +1).filldown

dans cette configuration, il faut garder la reference A65536 si tu as modifié en D65536 voici les 2 lignes

sht.range("A" & derligne-1 & ":C" & derligne).filldown
sht.range("L" & derligne-1 & ":Q" & derligne).filldown

ces 2 lignes etirent la ligne precedente sur la ligne suivante

 

[SPEA]

Re : Problème dans un code VBA

Re:

Ce n'est pas que je tienne à cette macro à tout prix mais elle a l'avantage de contenir les formules des calculs et evite ainsi que l'operateur ne les modifie.

Ce fichier ne contiendra jamais plus de 52 saisies (1 par semaines) donc le probleme de lenteur est à exclure (de plus il est possible de mettre un boucle pour ne pas recalculer une valeur qui l'a déjà été, mais là ca dépasse mon niveau...)

 

[wilfried_42]

Re : Problème dans un code VBA

re:

ok voici la macro testée

Sub Copie_formule()
  
  Dim DerLig As Integer, Sht As Worksheet
  Dim L As Integer, P As Integer
  
  Set Sht = Sheets("relevés 2008")
  DerLig = Sht.Range("D65536").End(xlUp).Row
  Sht.Range("A3").FormulaLocal = "=A2+1"
  Sht.Range("B3").FormulaLocal = "=D3-D2"
  Sht.Range("C3").FormulaLocal = "=C2+B3"
  Sht.Range("L3").FormulaLocal = "=SI(ESTERREUR(M3/G3);NA();M3/G3)"
  Sht.Range("M3").FormulaLocal = "=(F3/16/7,2)*1000/(EXP(0,0239*(E3-20)))"
  Sht.Range("N3").FormulaLocal = "=SI(ESTERREUR((I3-I2)/B3);NA();ARRONDI((I3-I2)/B3;0))"
  Sht.Range("O3").FormulaLocal = "=SI(ESTERREUR((H3-H2)/B3);NA();ARRONDI((H3-H2)/B3;0))"
  Sht.Range("P3").FormulaLocal = "=SI(ESTERREUR((J3-J2)/B3);NA();ARRONDI((J3-J2)/B3;0))"
  Sht.Range("Q3").FormulaLocal = "=SI(ESTERREUR(O3-P3);NA();O3-P3)"
    
  ' Recopie des formules vers le bas
  Sht.Range("A3:C" & DerLig).FillDown
  Sht.Range("L3:Q" & DerLig).FillDown
End Sub

bonne journée

 

[SPEA]

Re : Problème dans un code VBA

Merci Wilfried pour ton aide.

Bonne journée

 

[SPEA]

Re : Problème dans un code VBA

Juste un détail: pourquoi ne peut on pas modifier le format de la cellule date
qui se recopie de l'USF vers la feuille ?

Quand je met une date jj/mm/aa il me genere une erreur car il ne sait pas si c'est 19xx ou 20xx.

 

[wilfried_42]

Re : Problème dans un code VBA

re:

dans ta textbox tu modifies la propriete maxlength et tu la mets à 10

ensuite dans la macro liée au bouton :

Sheets(1).Cells(derligne + 1, 4).Value = CDate(TextBox1)