VBA Soucis avec les lignes vides
Tonio
-
Tonio -
Tonio -
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
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
A voir également:
- VBA Soucis avec les lignes vides
- Comment supprimer les pages vides sur word - Guide
- Excel trier par ordre alphabétique en gardant les lignes - Guide
- L'indice n'appartient pas à la sélection vba - Forum VB / VBA
- Supprimer lignes vides excel fin de tableau ✓ - Forum Excel
- Incompatibilité de type vba ✓ - Forum Programmation
29 réponses
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.
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.
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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
Le souci c'est que je ne sais pas comment résoudre mon problème
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
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
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.
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.
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)
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)
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é
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é
ok j'ai pigé le truc ^^
voila le code que j'ai fait, tu l'appel avec call calcdif
normalement ca devrai marcher
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
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
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
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
tu créé un bouton dans ta feuille et a l'évenement clic tu met call calcdif
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
