vba - determiner un fautif selon des règles de déduction (2 fichiers)

[osishame]

Bonjour,

Débutante en Vba, je sollicite votre aide pour m'aider à résoudre le problème suivant :

j’ai deux fichiers comprenant une feuille chacun :

Feuil 1 du fichier 1 est composé comme suit :
Colonne 4 : des données texte de la forme : XX0000000000 catégorie article
Colonne 10 : des données date jj/mm/AAAA date de réception théorique du colis
Colonne 11 : des données date jj/mm/AAAA date de réception réelle du colis
Colonne 19 : des données texte de la forme : ZZZ code couleur
Colonne 20 : des données date de la forme jj/mm/AAAA date de confirmation de commande
Colonne 21 : des données Heure de la forme 00 :00 :00 heure de confirmation de commande
Colonne 22 : des données texte soit « equitie » soit « bond »

Feuil 1 du fichier 2 (nomenclature) est composé comme suit
Colonne 1 : des données texte de la forme XX id. catégorie article
Colonne 3 : des données texte de la forme : ZZZ code couleur
Colonne 4 : des données date de la forme jj/mm/AAAA [equitie] nb jour nécessaire à la livraison
Colonne 5 : des données Heure de la forme 00 :00 :00 heure de confirmation limite de la commande [bond]
Colonne 6 : des données date de la forme jj/mm/AAAA [equitie] nb jour nécessaire à la livraison
Colonne 7 : des données Heure de la forme 00 :00 :00 heure de confirmation limite de la commande [bond]


Je dois analyser chaque ligne du fichier 1 avec les règles de déduction présentes dans le fichier 2 pour déterminer LE FAUTIF (qui sera à indiquer en colonne 23)

Pour chaque ligne du fichier 1
Si la date de réception théorique du colis >= date de réception réelle du colis (si le colis a été livré à temps) il n’a pas de fautif (rien à renseigné dans la cellule 23)

Dans le cas contraire, il y a un fautif et les règles de déductions sont les suivantes :
Dans un premier temps, il s’agit de retrouver quelle ligne du fichier 2 correspond à l’article en question. Ceci s’effectue par comparaison des deux premières lettres de la colonne 4 du fichier 1 avec la colonne 1 du fichier 2 ET par comparaison du code couleur des 2 fichiers (respectivement colonne 19 et colonne 3).

Si aucune ligne ne correspond à l’article (ce qui normalement n’est pas possible) on renvoie « code introuvable » en colonne 23

Une fois la ligne trouvée : selon si c’est un equitie ou un bond
Si la date de confirmation de la commande (colonne 20 du fichier 1) est < [date de réception théorique – nb de jour nécessaire pour la livraison] * (colonne 10 du fichier 1 - colonne 4 du fichier 2 pour les equitie ou colonne 6 du fichier 2 pour les bonds)

Le fautif est S

Si les deux dates sont égales *
Si l’heure de confirmation de la commande (colonne 21 du fichier 1) > heure limite de confirmation de la commande (colonne 5 (equitie) et 7 (bond) du fichier 2)
Le fautif est le Client
Sinon c’est S

Si la date de confirmation de la commande (colonne 20 du fichier 1) est > [date de réception théorique – nb de jour nécessaire pour la livraison] * (colonne 10 du fichier 1 - colonne 4 du fichier 2 pour les equitie ou colonne 6 du fichier 2 pour les bonds)
Le fautif est le Client

Ci-joint, mon essai vba :

--------------------------------------------------------
Public Sub NewSubFautif()

Dim Arr1 As Range
Dim Arr2 As Range

Worksheets("Extract").Range("A1").Select 'selectionner la plage 1
Set Arr1 = Selection.CurrentRegion.Select

Workbooks("Cutoff.xls").Worksheets("Feul1").Range("A1").Select 'selectionner la plage 2
Set Arr2 = Selection.CurrentRegion.Select

Dim iArr1 As Integer
Dim iArr2 As Integer
Dim iArr2_trouve As Integer

For iArr1 = 2 To Arr1.Rows.Count Step 1
If (Arr1.Cells(iArr1, 10).Value >= Arr1.Cells(iArr1, 11).Value) Then

With ActiveSheet
Cells(iArr1, 23) = ""
End With

Else

If Arr1.Cells(iArr1, 22).Value = "Equities" Then

a_chercher = Left(Arr1.Cells(iArr1, 10), 2)
trouve = False
iArr2_trouve = 0

For iArr2 = 2 To Arr2.Rows.Count Step 1
If (a_chercher = Arr2.Cells(iArr2, 1).Value) And (Arr1.Cells(iArr1, 19).Value = Arr2.Cells(iArr2, 3)) Then
trouve = True
iArr2_trouve = iArr2
End If
Next iArr2

If (trouve = True) Then

If Arr1.Cells(iArr1, 20).Value < (Arr1.Cells(iArr1, 10).Value - Arr2.Cells(iArr2_trouve, 4).Value) Then

With ActiveSheet
Cells(iArr1, 23) = "sgss"
End With

Else

If Arr1.Cells(iArr1, 20).Value = (Arr1.Cells(iArr1, 10).Value - Arr2.Cells(iArr2_trouve, 4).Value) Then

If Arr1.Cells(iArr1, 21).Value >= Arr2.Cells(iArr2_trouve, 5).Value Then

With ActiveSheet
Cells(iArr1, 23) = "client"
End With

Else

With ActiveSheet
Cells(iArr1, 23) = "sgss"
End With

End If

Else

With ActiveSheet
Cells(iArr1, 23) = "client"
End With

End If

End If

End If


Else

a_chercher = Left(Arr1.Cells(iArr1, 10), 2)
trouve = False
iArr2_trouve = 0

For iArr2 = 2 To Arr2.Rows.Count Step 1
If (a_chercher = Arr2.Cells(iArr2, 1).Value) And (Arr1.Cells(iArr1, 19).Value = Arr2.Cells(iArr2, 3)) Then
trouve = True
iArr2_trouve = iArr2
End If
Next iArr2

End If

If (trouve = True) Then

If Arr1.Cells(iArr1, 20).Value < (Arr1.Cells(iArr1, 10).Value - Arr2.Cells(iArr2_trouve, 6).Value) Then

With ActiveSheet
Cells(iArr1, 23) = "sgss"
End With

Else

If Arr1.Cells(iArr1, 20).Value = (Arr1.Cells(iArr1, 10).Value - Arr2.Cells(iArr2_trouve, 6).Value) Then

If Arr1.Cells(iArr1, 21).Value >= Arr2.Cells(iArr2_trouve, 7).Value Then

With ActiveSheet
Cells(iArr1, 23) = "client"
End With

Else

With ActiveSheet
Cells(iArr1, 23) = "sgss"
End With

End If

Else

With ActiveSheet
Cells(iArr1, 23) = "client"
End With

End If

End If

End If

End If


Next iArr1

End Sub

----------------------------------------------

N'hésitez pas pour toutes précisions supplémentaires.
Merci de votre aide.

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour

Un fichier avec les données (non confidentielles) et les valeurs que l'on désire obtenir simplifierait le travail et de ce fait la réponse.

JP

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour Jp14,

voici quelque lignes du fichier 1 avec le résultat à obtenir dans la dernière colonne.
Le fichier 2 étant trop lourd à charger, je n'ai pas pu charger toutes les lignes concernées.
Merci de votre aide.

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour

Il me faudrait des explications concernant cette phrase.
"des données date de la forme jj/mm/AAAA [equitie] nb jour nécessaire à la livraison "

Jp

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour jp14,

Tout d'abord, merci de ton aide.

"Colonne 6 : des données date de la forme jj/mm/AAAA [equitie] nb jour nécessaire à la livraison"

en fait ces données coorespondent au nombre de jours nécessaires à la livraison à partir de la date de la commande. Cela peut prendre 1, 2 ou 3 jours. J'ai directement mis en format date les cellules de la colonne 6 de la feuille 2 ainsi, on obtient :

0=00/01/1900
1= 01/01/1900
2= 02/01/1900
3= 03/01/1900

si tu as besoin de plus de renseignements, n'hésites pas !

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour jp14,

"Colonne 6 : des données date de la forme jj/mm/AAAA [equitie] nb jour nécessaire à la livraison"

en fait ces données coorespondent au nombre de jours nécessaires à la livraison à partir de la date de la commande. Cela peut prendre 1, 2 ou 3 jours. J'ai directement mis en format date les cellules de la colonne 6 de la feuille 2 ainsi, on obtient :

0=00/01/1900
1= 01/01/1900

Une valeur numérique serait largement suffisante et poserait moins de problème.
Comment écrire 40 jours ?

En VBA on utilise "DateAdd(interval, number, date)"
extrait de l'aide VBA
interval Expression de chaîne correspondant à l'intervalle de temps à ajouter (d pour ajouter des jours ).
number Expression numérique correspondant au nombre d'intervalles à ajouter. Peut être positive (pour obtenir des dates dans le futur) ou négative (pour obtenir des dates dans le passé).
date Variant (Date) ou caractère littéral représentant la date à laquelle l'intervalle est ajouté.

JP

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Merci pour l'info et le détails de la formule. je pensais naivement que c'était plus conseillé de soustraire une date avec une date.

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour le forum

Ci joint un fichier avec une macro a tester et à compléter pour les "bond" (equitie à recopier et nom des variables à changer)
Les principales fonctions sont :

Ouverture du fichier s'il n'est pas ouvert
Recherche de la ligne avec le code
comparaison au niveau des devises
appel d'une procédure en fonction du nom
comparaison ( partie à tester en mode pas à pas et en regardant le contenu des variables)

JP

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour JP14,

Merci infiniment pour la mise à disposition de vos compétences pointues et le temps que vous avez consacré à la résolution de mon problème.
Après avoir complété la sub pour les bonds, j'ai fait différents test d'efficacité. Pour l'instant, seul les valeurs XS0000000000 me posent problème. Je vais me pencher sur votre code (qui dépasse de très loin mes compétences en Excel) à l'aide de vos commentaires et des cours en lignes afin de comprendre en détails ce que vous avez fait.

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Merci beaucoup beaucoup JP14 !

Après avoir effectué une multitude de tests en tout genre, je ne peux que m'incliner devant votre travail si complet et abouti !


seul persiste le problème suivant : pour les codes_article pouvant se décliner en plusieurs couleurs (XS0000000000 et LU0000000000) seule la première couleur d'un code article est reconnue lors du scan du tableau de la feuille 2 par la macro. Pour toutes les autres couleurs, on obtient : code couleur introuvable.

j'ai essayé de rmodifier un peu votre code en rajoutant un next i afin que le scan continu mais sans résultat... je m'en remet donc une fois de plus à vous...

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Merci beaucoup beaucoup JP14 !

Après avoir effectué une multitude de tests en tout genre, je ne peux que m'incliner devant votre travail si complet et abouti !


seul persiste le problème suivant : pour les codes_article pouvant se décliner en plusieurs couleurs (XS0000000000 et LU0000000000) seule la première couleur d'un code article est reconnue lors du scan du tabkeau de la feuille 2 par la macro. Pour toutes les autres couleurs, on obtient : code couleur introuvable.

j'ai essayé de rmodifier un peu votre code en rajoutant un next i afin que le scan continu mais sans résultat... je m'en remet donc une fois de plus à vous...

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonsoir osishame

Merci beaucoup beaucoup JP14 !
seul persiste le problème suivant : pour les codes_article pouvant se décliner en plusieurs couleurs (XS0000000000 et LU0000000000) seule la première couleur d'un code article est reconnue lors du scan du tabkeau de la feuille 2 par la macro. Pour toutes les autres couleurs, on obtient : code couleur introuvable.

j'ai essayé de rmodifier un peu votre code en rajoutant un next i afin que le scan continu mais sans résultat... je m'en remet donc une fois de plus à vous...

Les fichiers avec cet erreur me permettrait d'analyser le problème.

JP

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

bonsoir JP,

Ci-joint les fichiers avec les exemples possibles ainsi que les solutions post macro et théoriques.

 

[jp14]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

Bonjour

Ci joint le fichier modifié a tester.

La recherche se fait sur le couple : code article et code couleur (concaténation)

JP

 

[osishame]

Re : vba - determiner un fautif selon des règles de déduction (2 fichiers)

C parfait ! je ne sais vraiment pas comment vous remercier. Merci mille fois pour tout. vive JP14 !