VBA : Optimisation + Modification d'une formule

Bonjour,

tu utilises une boucle pour remplir les formules

Code:

---

1 2 3

For i = 3 To finColDist .Cells(i, 46).FormulaR1C1 = "=IF(COUNTIF(R3C51:R" & finColOt & "C51,R" & i & "C[-31])>0,""O"",""N"")" Next i

---

chose que je n'ai pas fais, et c'était justement le but de l'utilisation de FormulaR1C1
cela n'a donc rien d'étonnant que ta procédure tourne 5 minutes :

- placer 35 000 formules gourmandes, cellules par cellule
- ne pas désactiver le recalcul automatique
==> à chaque cellule traitée, tout le classeur est recalculé ... donc 35 000 recalculs qui au fur et à mesure sont de plus en plus long.

cf mes différentes intervention à ce sujet

Citation:

---

là je t'ai proposé ce qu'il me semble être le plus rapide pour manipuler/écrire une formule Excel (gourmande) sur 35 000 lignes. Y'a aucune boucle et aucun ralentisseur (select, activate etc...).
Le seul angle optimissable c'est de neutraliser le recalcul de la feuille et le rafraichissement d'écran.

---

Citation:

---

Les boucles sont inutiles pour la formule

---

Citation:

---

Astuce : si tu passes par une formule relative (.FormulaR1C1), tu auras la possibilité, sans aucune boucle, d'appliquer ta formule sur l'ensemble de ta plage.

---

Ah ... Autant pour moi pour la boucle! Mais je crois que ca me mettait une erreur quand je voulais appliquer la formule sur l'ensemble de la plage sans le For, je vais rééssayer avec ta solution.
Je viens de désactiver le recalcul automatique à l'instant :)

Et .. désolé pour lire un peu de travers, j'ai pas mal de choses à faire ne même temps ^^' je ferai plus attention dès à présent!

Donc, voici le code :

Code:

---

1 2 3

With Worksheets("ExportDelais").Cells(3, 38).Resize(Cells(1, 1).End(xlDown).Row, 1) .FormulaR1C1= "=IF(COUNTIF(R3C51:R" & finColOt & "C51,R" & i & "C[-31])>0,""O"",""N"")" End With

---

Citation:

---

Erreur 1004, erreur définie par l'application ou par l'objet.

---

Ca pointe xlDown et met qu'il est égale a -4121

ton Cells(1, 1).End(xlDown).Row n'est pas rattaché à la feuille ExportDelais
Donc si cette feuille n'est pas la feuille active, ça plante

de plus, tu commences à écrire tes formules en ligne 3, et tu calcules le nombre de lignes à écrire en utilisant le numéro de la dernière ligne écrite en colonne A.
Tu dois donc soustraire les lignes supplémentaires sinon tu vas écrire tes formules sur des lignes vides

Code:

---

1 2 3 4 5

With Worksheets("ExportDelais") With .Cells(3, 38).Resize(.Cells(1, 1).End(xlDown).Row - 2, 1) .FormulaR1C1 = "=IF(COUNTIF(R3C51:R" & finColOt & "C51,R" & i & "C[-31])>0,""O"",""N"")" End With End With

---

Voilà qui lève l'erreur de renseignement de la feuille, et qui corrige de 2 lignes la plage où appliquer les formules

ça ne veut pas dire que tu es au bout de tes surprises ... moi je ne peux pas savoir si ta formule est bonne :zoubi: et/ou si finColOt est OK

Pour le finColOT, je suis sûr et certain qu'il est juste, j'ai déjà fais des tests ect et j'ai déjà corrigé les erreurs.

J'ai réessayé en corrigeant, et apparemment, ca serait la formule qui serait fausse :

Code:

---

1 2 3 4 5

With Worksheets("ExportDelais") With .Cells(3, 38).Resize(.Cells(1, 1).End(xlDown).Row - 2, 1) .FormulaR1C1 = "=IF(COUNTIF(R3C51:R" & finColOt & "C51,R" & i & "C[-31])>0,""O"",""N"")" End With End With

---

Petit récap, la formule doit vérifier si la référence de la colonne "G" (col 7) est présente parmi les références de la colonne "AY" (col 51). Si oui, mettre dans la colonne "AL" un "O" à la ligne correspondante, sinon, mettre "N".
Quand j'éxecutais ce code avec la boucle For, la formule marchait très bien pourtant, je ne comprends pas mon erreur :(

ton i n'a plus lieu d'être, puisqu'il n'y a plus de boucle

il te suffit de reprendre ma formule initiale et tu sauras par quel chiffre remplacer ton i :zoubi:

Je pense avoir trouvé l'erreur! En fait, la colonne "AY" est de la forme : 0001349157
Alors que la colonne "G" est comme ceci : 1349157
Y'en a un qui est en String et l'autre en Integer je pense

Mais pour éviter cette erreur, j'avais tenté de convertir en appliquant le code suivant (que j'avais trouvé sur le net) :

Code:

---

1 2 3 4

With Worksheets("ExportDelais").Range("AY3:AY" & finColOT) .numberFormat = "0" .Value = .Value End With

---

Je crois que ce code ne fait pas ce que je souhaite :/

Si tes nombres sont des entiers qui ne dépassent pas 2 milliards, tu peux supprimer les 0 en convertissant la valeur de ta cellule au format Long :

Code:

---

Cells(1, 2).Value = CLng(Cells(1, 2).Value)

---

si les nombres ne sont pas entiers ou dépassent 2 milliards, donne des exemples pertinent pour se faire une idée du bon type de conversion à appliquer

Bah à la base, les références sont toujours de ce type : 0001349157 , elles ne dépassent jamais les 10 chiffres (en comptant les 0)
Il faut les transformer en 1349157, donc mes données de dépassent jamais le milliard :)

D'ailleurs ... Ca serait pas plutôt le "i" dans la formule qui ferait bug le programme plutot?

Code:

---

.FormulaR1C1 = "=IF(COUNTIF(R3C51:R" & finColOt & "C51,R" & i & "C[-31])>0,""O"",""N"")"

---

Je l'avais mis a cause du For, mais maintenant, je fais comment?

Citation:

---

Envoyé par joe.levrai

ton i n'a plus lieu d'être, puisqu'il n'y a plus de boucle

il te suffit de reprendre ma formule initiale et tu sauras par quel chiffre remplacer ton i :zoubi:

---

J'suis bête, je l'avais déjà modifié sur mon vrai code! Enfin bref, ça marche parfaitement maintenant!
Merci à tous pour votre aide précieuse! :ptdr: ;)

A une prochaine fois peut-être :)