Probleme avec SOMMEPROD

[KIM]

Bonjour le Forum,
Bonjour le forum,
J'ai passé toute la journée d'hier et la soirée sans résultat. Je bloque sur une formule simple SOMMEPROD qui me donne le resultat #VALEUR!
Je fais appel comme d'habitude au forum et vous en remercie d'avance.

Je recupère un fichier de plus de 25000 lignes. Dans ce fichier de plusieurs colonnes j''en utilise 3:
1ere col: Code_du_DEP (normalement du Texte)
2° col: montant commande (normalement numérique)
3° col Code du projet (normalement TEXTE)

Je dois faire un récapitulatif selon une liste de projet qu'on me donne:
Ce tableau recap possède :
1° col: Liste des codes projets
2° col: Récap dépense globale par projet et les autres colonnes : Dépenses globale par projet et par DEP

Je me suis rendu compte que dans le fichier Origine il existe des cellules Texte dans la 2° col des montants commande.
Dans mon tableau recap j'utilise:
=SOMMEPROD(($B5:$B1153)*($C5:$C1153=$F16)*($A5:$A1153=J$4))
j'ai essayé aussi
=SOMMEPROD(CNUM($B5:$B115)*TEXTE($C5:$C115;'')=TEXTE($F16;''))

Je copie une centaine de lignes de mon fichier, je supprime les cellules contenant du TEXTE dans la 2° COl
et les formules sont correctes,
Je fais la meme chose sur le fichier origine et j'applique à tout mon fichier les formules
et là j'ai toujours l'erreur #VALEUR!
ci-joint une extraction du fichier d'origine
MERCI DE VOTRE AIDE
Amicalement
KIM [file name=prKIM1.zip size=23794]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM1.zip[/file]

 

[KIM]

Bonjour Dan et le forum,
Puisque par decoupage d'execution, la macro se deroule malgre le temps long, est-il possible de faire une recopie ligne par ligne et non pas l'ensemble. Je m'explique :
à la place de faire une copy par un seul bloc

Range('K2:BS2').Copy
ActiveSheet.Paste Destination:=Range('K8:K' & n_line)

With Selection
.Copy
.PasteSpecial Paste:=xlFormats
.PasteSpecial Paste:=xlValues
End With

faire une execution ligne par ligne c-a-d:
Range('K2:BS2').Copy
pour i = 8 à n_line
faire
ActiveSheet.Paste Destination:=Range('Ki:BSi')

With Selection
.Copy
.PasteSpecial Paste:=xlFormats
.PasteSpecial Paste:=xlValues
End With
Next


comment ecrire cette boucle en vba et rajouter si possible un calcul du temps d'execution avec
T1=lancement
T2=fin d'exec
T2-T1= temps d'exec

Merci d'avance
Bien amicalement
KIM

 

[Dan]

Bonsoir,

As-tu essayé ce que je te proposais dans mon post précédent ?

Au vu de ton dernier post, je ne vois pas que tu as changé ta macro comme proposé. Merci de faire cet essai.

 

[KIM]

Bonsoir Dan et le forum,
J'ai modifié ma macro en remplacant
Range('K2:BS2').Copy
ActiveSheet.Paste Destination:=Range('K8:BS217')

par
Range('K2:BS2').Copy Destination:=Range('K8:BS217')

1- Balayage horizontal croissant:
J'ai testé:
a) Range('K2:AA2').Copy Destination:=Range('K8:AA217')
OK temps d'exec 5mn
b) Range('K2:BA2').Copy Destination:=Range('K8:BA217')
OK temps d'exec 15mn
c) Range('K2:BS2').Copy Destination:=Range('K8:BS217')
OK temps d'exec 30mn

2-Balayage vertical croissant :
a) Range('K2:BS2').Copy Destination:=Range('K8:BS50')
OK temps d'exec 5mn
b) Range('K2:BS2').Copy Destination:=Range('K8:BS150')
OK temps d'exec 15mn
c) Range('K2:BS2').Copy Destination:=Range('K8:BS217')
OK temps d'exec 30mn

pour des feuilles CDE et FAC de 21 000 lignes
et aucune application ne tourne sur le PC sauf Excel.
Je n'ai plus eu de blocage d'excel ('Application Excel ne repond plus') mais l'attente devient de plus en plus longue.

J'ai eu l'impression que la ligne
Range('K2:BS2').Copy Destination:=Range('K8:BS217')
a amélioré le temps d'exécution

Merci d'avance si tu as d'autres idees pour ameliorer le temps d'execution.
Bien amicalement
KIM

 

[Dan]

Bonsoir,

Essaie aussi de mettre cette instruction en début de macro :

Application.ScreenUpdating = True

A la fin de ta macro, mets :

Application.ScreenUpdating = False

Refais un essai

 

[KIM]

Re,
Selon ton dernier messsage, j'ai rajouté les instructions demandés.
Quelque soit le type de balayage horizontal ou vertical,
le temps d'execution etait un peu plus long pour les tests a et b parcontre pour le tect global c le temps d'execution est bien plus long,
il calcule et remplis les cellules, mais à la fin il refait un recalul , l'ecran est figé, et 15 min plus tard il rend à l'ecran le message 'C'est fini'.
Au debut de la macro, les cellules K8:BS217 sont vidées,
ensuite un recalcul commence et les cellules sont remplies au fur et à mesure de l'avancement du recalcul, et à la fin
Je n'ai pas compris pourquoi excel refait un recalcull de nouveau quand toutes les cellules de K8:BS217 sont remplies.
QU'en penses-tu? est-ce normal?
Merci d'avance
KIM