VBA Soucis avec les lignes vides Fermé

Question

Bonjour,

Cette macro va me rendre fou...

J'ai un pb avec mes deux "sumproduct" dès qu'il y a une ligne qui est vide il me met #VALEUR si je remplis toute les lignes vides le calcul est effectué et si ensuite j'efface les informations il ne bronche plus et se met à jour...
EX: J'ai 4000 lignes à le 213 il y a un trou dans la colonne M si je change ma formule et que je prends 212 il calcule. Si je prends 213 #Valeur. Si je mets une valeur dans 213 il met et que je l'intègre il met à jour et si j'efface cette valeur, il met à jour et ne bug pas....

Merci d'avance pour votre aide

Sub KPI()

djnwemp = Cells(4, 3).Value

Workbooks.OpenText Filename:="C:\Documents and Settings\troncan1\Desktop\KPI." & djnwemp & ".XLS", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
TrailingMinusNumbers:=True

nbligne = Sheets(2).Range("A65536").End(xlUp).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\troncan1\Desktop\KPI.20100128.txt", Destination:=Range( _
"A" & nbligne & ""))
.Name = "Extraction SIGMA"
.TextFileSemicolonDelimiter = True
.Refresh
End With

derlig = Sheets(2).Range("A65536").End(xlUp).Row

Range("KPI!D17").Formula = "=Sumproduct(('Extraction SIGMA'!J2:J" & derlig & "<>1990)*('Extraction SIGMA'!M2:M" & derlig & "<>"""")*('Extraction SIGMA'!M2:M" & derlig & "-'Extraction SIGMA'!L2:L" & derlig & "))/Sumproduct(('Extraction SIGMA'!J2:J" & derlig & "<>1990)*('Extraction SIGMA'!M2:M" & derlig & "<>""""))"

Range("KPI!F6").Formula = "=SUMPRODUCT(('Extraction SIGMA'!O2:O" & derlig & "-'Extraction SIGMA'!L2:L" & derlig & ">2)*1)"

Range("B8").Select
Windows("KPI." & djnwemp & ".xls").Activate
Sheets("KPI").Select
Range("D34:E34").Select
ActiveCell.FormulaR1C1 = "=COUNTIF('Extraction SIGMA'!C[12],""OUI"")"
Range("F34:G34").Select
ActiveCell.FormulaR1C1 = "=COUNTIF('Extraction SIGMA'!C[10],""NON"")"
Range("H34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4],RC[-2])"
Range("H35").Select

End Sub

lami20j · Answer

Salut,

Tu ne peux pas effacer les lignes vides?

Tonio · Answer

Non je ne peux pas. 
Mes données viennent d'un TXT tout est automatique. Je ne peux pas contrôler les trous il faudrait que je puisse les ignorer.

Tonio · Answer

Quelqu'un sait ou je dois modifier mes formules ?

garion28 · Answer

pourquoi ne ferai tu pas tout en vba au lieu d'utiliser lse formules d'excel ? ca te permettrai d'etre plus libre et pouvoir faire exactement ce que tu veux faire

Tonio · Answer

Mes formules sont déjà commandées en VBA tu peux tout voir dans mon code.

Le souci c'est que je ne sais pas comment résoudre mon problème

garion28 · Answer

ca reste mon avis, mais ces formules tiré d'excel et utilisé en vba, je ne trouve pas ca très pratique puisque comme tu as pus le voir, ca plante en mettant un#VALEURS ou un autre truc comme ca, en fesant tout le traitement a partir de vba et en utilisant les feuilles et cellules uniquement pour l'affichage ca pourrai etre plus pratique (et pas plus gourmand en ressources si tu n'as pas énormément de lignes

Tonio · Answer

Je veux bien, mais la je ne sais pas faire pouvez vous m'aider ?

garion28 · Answer

aucun problème, tu me dis ce qu'est sensé faire les formules que tu as utilisé et je te dis comment y remédier en vba

Tonio · Answer

Tu vois mes deux formules Sommeprod dans mon code ?

Pour la 1ere elle concerne 3 colonnes de mon tableau. 

Une colonne statut qui me dit ou en est un ordre que j'ai passé (statut 1100 1150 1200 1650 1990....)
Une colonne qui me dit à quelle date je suis passé en statut 1100.
Une colonne qui me dit à quelle date je suis passé en 1650.
Je dois calculer sur toutes mes dates le délai moyen de passage de 1100 à 1650. (Attention j'ai un nouveau fichier tous les jours c'est pour ça que ma formule s'adapte chaque jours car je peux autant avoir 1000lignes le lundi et en avoir 1600 le mardi)
Enfin j'ai 2 contraintes (c'est la ou ma 1ere colonne statut est obligatoire à prendre en compte) Quand je suis dans un statut intermédiaire entre 1100 et 1650 je n'ai pas de date de 1650 (mais j'en ai toujours en 1100 c'est obligatoire) il faut donc ignorer cette ligne, puis quand j'ai des dates pour 1100 et 1650 quelques fois je peux être en statut 1990. Si je suis en 1990 je ne veux pas que la ligne non plus soit prise en compte.



La 2ème formule est plus simple j'ai une colonne date de réception et une colonne date de passage en statut 1100. Si le différence entre la date de réception et la date de passage en statut 1100 dépasse 2 jours il y a un retard. Je veux calculer tous les retards qu'il y a (il faut toujours prendre en compte le fait que je vais avoir un nombre de ligne qui varie de jour en jour donc prendre en compte une colonne de la ligne 2 car la ligne 1 c'est le titre de la colonne à la ligne 65536 qui est la ligne ultime d'excel)

Je ne sais pas si je suis assez clair.

garion28 · Answer

j'avou n'avoir rien compris a ton explication :s

le but c'est de compter le nombre de fois que les valeurs sont vu dans une plage de cellule ? (si c'est bien ce que fait sumproduct)

Tonio · Answer

Pour la formule 1 déjà voila un tableau type.
https://www.cjoint.com/?cbpOUV8N7l

Les 3 colonnes sont en rouge.
Le calcul se fait avec les colonnes 1100 et 1650 
Je veux savoir combien de jours y a t-il en moyenne pour passer du statut 1100 au statut 1650.
En faisant attention au fait que certains ordres ne sont pas arrivés en 1650 il y a donc des trous et il ne faut pas prendre en compte dans le calcul et d'autres ordres sont arrivés en 1650 mais leur statut a évolué en 1990 et la non plus ils ne doivent pas être pris en compte pour calculer le délai moyen. 

Les ex de lignes à exclure sont en jaune.


Attention ici j'ai jusqu'à la ligne 40. Mais ce calcul sera automatiquement fait chaque jour avec des nombres de lignes qui vont varier. Il faut donc que les 3 colonnes soient considérées dans leur intégralité

Tonio · Answer

https://www.cjoint.com/?cbpZkH6H5y

Je m'étais trompé de colonne...

Tonio · Answer

https://www.cjoint.com/?cbpZkH6H5y

Je m'étais trompé de colonne...

garion28 · Answer

ok j'ai pigé le truc ^^

voila le code que j'ai fait, tu l'appel avec call calcdif
normalement ca devrai marcher

sub calcdif
dim i as long
dim res as long
dim ct as long

res=0
ct=0

with sheets("Extraction SIGMA Janv 2010")
while (.range("J" & i).value<>"")
if (.range("M" & i).value<>"")then
res=res+((.range("M" & i).value)-(.range("L" & i).value))
ct=ct+1
end if
res=
wend
calcdif = res/ct
end with
end sub

Tonio · Answer

j'ai une erreur avec ton dernier res=

garion28 · Answer

ah c'est une mauvaise frappe, enleve le res= qui est tout seul (il est inutile)

Tonio · Answer

pour le dernier calcdif = res / ct il me met fonction ou variable attendue

garion28 · Answer

bizare, remplace le calcdif = par return (ce qui fait return res/ct)

Tonio · Answer

Sub calcdif()
Dim i As Long
Dim res As Long
Dim ct As Long

res = 0
ct = 0

With Sheets("Extraction SIGMA Janv 2010")
While (.Range("J" & i).Value <> "")
If (.Range("M" & i).Value <> "") Then
res = res + ((.Range("M" & i).Value) - (.Range("L" & i).Value))
ct = ct + 1
End If

Wend
return res / ct
End With
End Sub

en incluant le code comme si dessus il me dit attendu fin d'instruction
Si je l'enlève il me souligne la ligne du while en disant erreur définie par l'appli ou l'objet

garion28 · Answer

j'ai fait quelques erreurs toute bête et je n'ai pas testé le code avant de te le donner :)
voila le bon: (j'espere que la partie .range("J" & i).value<>"1650" est correct puisque je suppose qu'ils n'y a qu'au statut 1650 ou le calcul doit etre fait

Public Sub calcdif()
Dim i As Long
Dim res As Long
Dim ct As Long

res = 0
ct = 0
i = 4
With Sheets("Extraction SIGMA Janv 2010")
While (.Range("J" & i).Value <> "")
If (.Range("M" & i).Value <> "" or .range("J" & i).value<>"1650") Then
res = res + ((.Range("M" & i).Value) - (.Range("L" & i).Value))
ct = ct + 1
End If
i = i + 1
Wend
End With
MsgBox "la moyenne de jours de passage du statut 1100 à 1650 est de: " & res / ct
End Sub

tu créé un bouton dans ta feuille et a l'évenement clic tu met call calcdif

Tonio · Answer

C'est parfait Merci ! 

Par contre dernière demande est il possible de mettre le résultat dans une cellule plutôt qu'en msgbox?

garion28 · Answer

tu remplace msgbox par .range("ta cellule genre A1").value=
et surtout tu met cette partie juste au dessus du end with pour qu'il n'y ai pas de plantage sur le .range (c'est le mode feignant pour éviter la répétition du nom de la feuille ^^)

Tonio · Answer

Parfait Merci pour tout ;)

garion28 · Answer

si tu as besoin d'autre chose n'hésite pas :)

Tonio · Answer

Je viens de faire mes test tout marche j'ai juste un pb. C'est assez lourd je dois te l'avouer mais dans la colonne statuts je peux avoir des statuts 1990 (pas 1950 je m'étais trompé dans mon fichier). Le soucis est que quand j'ai ces statuts j'ai aussi les dates pour 1100 et 1650. Dans mon calcul je dois ignorer les statuts 1990 même si j'ai les 2 dates. J'ai testé en remplaçant des 1650 par des 1990 et ça me prend toujours en compte.

garion28 · Answer

je vien de voir que j'avai encore fait une erreur d'inattention ^^' (le .range(xx).value<>"1650" est plutot un ="1650") donc remplace If (.Range("M" & i).Value <> "" or .range("J" & i).value<>"1650") Then par If (.Range("M" & i).Value <> "" And .Range("J" & i).Value = "1650") Then le but de cette ligne c'est: si la deuxième colonne de date n'est pas vide ET que la premiere colonne de statut (en rouge) est égal a 1650 (et seulement 1650) alors on compte cette ligne ce calcul est effectif dans le seul cas ou l'on veu compter les lignes avec 1650 après tu a juste a modifier le format de la cellule de destination pour afficher que 2 chiffre après la virgule j'espère ne pas avoir fait d'autres erreurs xD

Tonio · Answer

Parfait merci beaucoup ! J'ai plus qu'à intégrer le tout dans ma macro globale et c'est gagné ! 

Bravo !

garion28 · Answer

si tu veu intégrer la différence de jours pour chaque ligne, c'est a faire dans le if de la boucle (ya juste a faire un .range("ta colonne" & i).value= la formule de différence 1650-1100)

Tonio · Answer

Ok parfait ! 
Merci !

VBA Soucis avec les lignes vides

29 réponses

Discussions similaires