Gros problème VBA / EXCEL Fermé

Question

Bonjour,

J'ai une macro sous VBA qui m'ouvre un classeur. Intègre des données d'un texte et me fait une série de calculs pour trouver 4 statistiques...
 
Dans ces données j'ai entre autre 2 colonnes qui sont les colonnes référence de l'opération qui est de type TXXXXXX et une référence client de type CXXXXXXX

Attention c'est parti : 

Je peux avoir plusieurs réf C pour une seule ref T par exemple je peux avoir ceci dans mon tableau :


ref T             ref C                Date de réception     Date statut 1100  Date statut 1650      Instruction correcte

T1000001      C130001          10/01/2010              10/01/2010        10/01/2010               OUI
T1000001      C130002          10/01/2010              10/01/2010        10/01/2010               OUI
T1000001      C130003          10/01/2010              10/01/2010        11/01/2020               OUI
T1000002      C140001          15/01/2010              15/01/2010        15/01/2010               OUI
T1000003      C150001          17/01/2010              17/01/2010        17/01/2010               NON

A partir d'un tableau de ce type qui peut comporter jusqu'à 4000 lignes, je calcule 4 stats

Un délai moyen de passage entre le statut 1100 et le statut 1650 
Le nombre d’instructions correctes
Le nombre d'opération réalisées en retard ( si le nombre de jours entre réception et statuts 1100 dépasse 2 jours)
Une dernière stat qui n'a pas d'intérêt ici :-)

Voici mon problème. 
Pour le calcul de la dernière stat et le délai moyen je n'ai pas de soucis je prends toutes les données.

Par contre pour le nombre d'instruction et le nombre d'opération en retard je ne dois faire mes calculs qu'en fonction des réf T. Il faut donc que lorsque je calcul mes 2 stats concernées, je demande à ma macro d'exclure les doublons en réf T. L'exemple est simple J'ai 3 ref T1000001 il faut qu'une seule soit prise en compte... sinon j'ai des doublons.

Merci d'avance pour votre aide !!

tompols · Answer

Salut
"Merci d'avance pour votre aide" bah de rien mais ce serait ptet plus simple si tu posais une question, là je ne vois aucun pb ????
"Il faut donc que lorsque je calcul mes 2 stats concernées, je demande à ma macro d'exclure les doublons en réf T" => j'imagine que c'est ici, mais peux-tu stp mettre le code que tu as déjà ou nous dire où tu coinces ?

Tonio · Answer

Trop pris dans mon explication j'avoue j'ai pas tellement marqué le pb....

Il me faut un système pour que lorsque je calcule 2 des 4 stats (tout est automatique) les doublons en réf T soit dégagés.

Voici mon code (un peu bordélique mais je m'y retrouve)

Sub KPI()
'
' MacroKPI Macro
' Macro enregistrée le 27/01/2010 par Tronc
'

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." & djnwemp & ".TXT", Destination:=Range( _
"A" & nbligne & ""))
.Name = "Extraction SIGMA"
.TextFileSemicolonDelimiter = True
.Refresh
End With

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

Range("KPI!F6").Formula = "=SUMPRODUCT(('Extraction SIGMA'!L2:L" & derlig & "-'Extraction SIGMA'!O2:O" & derlig & ">2)*1)"
Range("KPI!D6").Formula = "=SUMPRODUCT(('Extraction SIGMA'!L2:L" & derlig & "-'Extraction SIGMA'!O2:O" & derlig & "<2)*1)" Voila pour les instructions en retard ou pas

Range("B8").Select
Windows("KPI." & djnwemp & ".xls").Activate
Sheets("KPI").Select
Range("D34:E34").Select
ActiveCell.FormulaR1C1 = "=COUNTIF('Extraction SIGMA'!C[12],""O"")"
Range("F34:G34").Select Et ici c'est instruction correcte ou non
ActiveCell.FormulaR1C1 = "=COUNTIF('Extraction SIGMA'!C[10],""N"")"
Range("H34").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4],RC[-2])"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4],RC[-2])"
Range("H24").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4],RC[-2])"
Range("H7").Select

Range("F24").Select
ActiveCell.FormulaR1C1 = _
"=SUM(COUNTIF('Extraction SIGMA'!C[-2],""CTI ""),COUNTIF('Extraction SIGMA'!C[-2],""RAP ""),COUNTIF('Extraction SIGMA'!C[-2],""RFP ""),COUNTIF('Extraction SIGMA'!C[-2],""RTV ""),COUNTIF('Extraction SIGMA'!C[-2],""TBC ""),COUNTIF('Extraction SIGMA'!C[-2],""CTI""),COUNTIF('Extraction SIGMA'!C[-2],""RAP""),COUNTIF('Extraction SIGMA'!C[-2],""RFP""),COUNTIF('Extraction SIGMA'!C[-2],""RTV""),COUNTIF('Extraction SIGMA'!C[-2],""TBC""))"

Range("D24").Select
ActiveCell.FormulaR1C1 = _
"=SUM(COUNTIF('Extraction SIGMA'!C,""DTI ""),COUNTIF('Extraction SIGMA'!C,""DAP ""),COUNTIF('Extraction SIGMA'!C,""DFP ""),COUNTIF('Extraction SIGMA'!C,""MDI ""),COUNTIF('Extraction SIGMA'!C,""TCB ""),COUNTIF('Extraction SIGMA'!C,""DTI""),COUNTIF('Extraction SIGMA'!C,""DAP""),COUNTIF('Extraction SIGMA'!C,""DFP""),COUNTIF('Extraction SIGMA'!C,""MDI""),COUNTIF('Extraction SIGMA'!C,""TCB""))"

Dim i As Long
Dim res As Long
Dim ct As Long

res = 0
ct = 0
i = 2
With Sheets("Extraction SIGMA")
While (.Range("J" & i).Value <> "")
If (.Range("M" & i).Value <> "" And .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
Sheets("KPI").Range("D17").Value = res / ct
End With
End Sub

Tonio · Answer

Pas d'idée

Tonio · Answer

Up

garion28 · Answer

tien comme on se retrouve :)

pour T1000001, laquel des 3 C... doit on choisir ?

Tonio · Answer

Salut  :-)

Il faut choisir un C au hasard pour les calculs concernés les informations dans les colonnes sont les mêmes.
Il faut juste enlever les doublons.

garion28 · Answer

donc pour un T donnée, les informations a choisir sont identique ?
si c'est le cas:

dim tab() as string
dim lig as long
dim t as long
dim vu as boolean
dim a as long
lig=2
a=0
'on redimenssionne le tableau
while .range("A" & lig).value<>""
lig=lig+1
wend
redim preserve tab(lig)

'on compare chaque ligne de la feuille avec le contenu du tableau
for i =2 to lig
t=0
vu=false
while tab(t)<>"" or vu=false
if tab(t)=.range("A" & i).value then vu=true
t=t+1
wend
if vu=false then tab(a)=.range("A" & i).value: a=a+1
next i

ca devrai le faire (j'ai fait la version raccourci pour les if, j'avai un peu la flemme ^^)

Tonio · Answer

Tu l'intègres ou car il me dit erreur de syntaxe

Tonio · Answer

En fait dans mes 4000 lignes dans mon tableau j'ai parfois plusieurs C pour 1 seul T.
J'ai donc plusieurs fois le même T dans mon tableau. Je veux que pour 2 de mes 4 calculs qu'il n'y ait pas de doublons car  si je fais ma stat après il va me dire par exemple qu'il y a 3800 instructions correctes alors qu'à certains endroits on a 6 fois le même T.

garion28 · Answer

j'ai oublié le with sheets("nom_de_te_feuille") au début et end with a la fin (pour utiliser le .range)

et tu peu mettre ca dans un module en tant que procédure et tu l'appel ou tu veu par call nom_de_ta_procedure

Tonio · Answer

Erreur de syntaxe sur le dim tab

Sub test()

dim tab() as string
Dim lig As Long
Dim t As Long
Dim vu As Boolean
Dim a As Long

lig = 2
a = 0

With Sheets("Extraction SIGMA")
'on redimenssionne le tableau
While .Range("A" & lig).Value <> ""
lig = lig +
Wend
redim preserve tab(lig)

'on compare chaque ligne de la feuille avec le contenu du tableau
For i = 2 To lig
t = 0
vu = False
while tab(t)<>"" or vu=false
if tab(t)=.range("A" & i).value then vu=true
t = t + 1
Wend
if vu=false then tab(a)=.range("A" & i).value: a=a+1
Next i
End With

End Sub

Tonio · Answer

j'avais oublié le call dans mon code je test

Tonio · Answer

toujours l'erreur de syntaxe 1ere ligne

garion28 · Answer

je vien de voir qu'il n'acceptai pas d'utiliser le "tab" en variable, du coup en remplacant tab par untab ou tableau si tu veu, ca marche

Tonio · Answer

Très bien.

Dernière questions.

Lors de l'execution de ma macro, j'ouvre automatiquement un fichier texte dans ma feuille extraction SIGMA. J'aimerai que ce fichier s'ouvre 2 fois. 
Une fois dans 'Extraction SIGMA' et une autre dans 'Extraction SIGMA2'

Tonio · Answer

up

garion28 · Answer

désolé, je me suis soudainement mis a bosser durant 2 heures pour faire mon projet de stage xD
tu utilise un truc genre openfilename pour traiter ton fichier texte ? j'ai bien une solution toute bête mais ca dépend de ce que tu a fait pour copier ton fichier texte, tu pourrai mettre le code de ton importation du fichier texte ?

Tonio · Answer

Pas de soucis  :-)

Ouai je suis en open file. C'est au début de mon code. Il faudrait qu'il s'intègre dans la 1ere feuille Extraction SIGMA mais aussi dans la 2ème Extraction SIGMA2.

garion28 · Answer

arf un léger écart dans un mot et ca change tout.
je parlai de la fonction getopenfilename qui renvoi un objet contenant le fichier et qui peu ainsi etre traité facilement (selon moi)

le filename dont tu parle s'applique pour le fichier xls et par pour le texte. je ne connais pas du tout la méthode que tu as utilisé pour le fichier texte mais voici un lien qui m'as aidé:
http://www.info-3000.com/vbvba/fichiertexte.php

Tonio · Answer

Ouf c'est pas moi qui ai trouvé la méthode.
Je suis vraiment novice en VBA. 
On m'a demandé de faire quelque chose j'essaie de me débrouiller :-)

T'aurais une méthode pour ouvrir un TXT dans 2 feuilles en simultané ?

garion28 · Answer

avec le lien que je tes fourni tu peu écrire la valeur lu dans le fichier txt dans toutes les feuilles que tu veu
dans quel formes sont tes données dans ton fichier txt ?

Tonio · Answer

Mon fichier TXT est préconfiguré pour que quand tu l'ouvres dans excel il se range direct en colonnes.
(j'ai les titres dans le document puis les données)

garion28 · Answer

d'accord, donc ma méthode n'est pas utilisable

tu peu peut etre recopier le même code, mais s'il y a beaucoup de ligne ca doublera le temps de travail du pc

Tonio · Answer

J'ai essayé de recopier le code mais je comprends pas pourquoi ça m'ouvre une 2eme fois dans la même feuille...

garion28 · Answer

il faut changer le .name 
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings	roncan1\Desktop\KPI." & djnwemp & ".TXT", Destination:=Range( _
"A" & nbligne & ""))
.Name = "Extraction SIGMA"  < - - ICI
.TextFileSemicolonDelimiter = True
.Refresh
End With

Tonio · Answer

Je comprends pas le fichier s'ouvre automatiquement sur la 1ere feuille de mon classeur et ne fais pas de reconnaissance

garion28 · Answer

essaye:
With sheets("ton nom de feuille ou écrire")
.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings	roncan1\Desktop\KPI." & djnwemp & ".TXT", Destination:=Range( _
"A" & nbligne & ""))
.Name = "Extraction SIGMA"  < - - ICI
.TextFileSemicolonDelimiter = True
.Refresh
End With 

mais bon je ne suis vraiment pas sur de ca, il faudrai que quelqu'un qui utilise cette methode vienne t'aider

Tonio · Answer

ça me dit que la plage de destination n'est pas dans la même feuille de calcul que celle dans laquelle la table de requette a été créee

garion28 · Answer

tu pourrai mettre le fichier sur cijoint ? parce que là je seche

Tonio · Answer

J'ai retrouvé celui qui m'avait donné le code.
Je pense qu'il y a une petite erreur au niveau de la reconnaissance de table je lui ai demandé.