Discussion :

[OBADJ]

Bonjour à tous,

J'aurais besoin de votre aide pour une macro à faire sur un fichier excel où je dois automatiser plusieurs choses. Ps : Je suis débutant c'est une vrai galère pour moi :/

J'aimerais que sur un même numéro de sinistre (colonne J) et que ce même numéro de sinistre dispose de 2 codes CIE différents (colonnes R) alors je met un commentaire "Régularisation CIE-Template" en colonne M.
A savoir qu'il faudra faire une boucle je pense car il s'agit d'un fichier avec plus de 6000 lignes et je veux lui demandé qu'il retrouve parmi les mêmes numéros de sinistres s'il y a la présence de 2 codes CIE différents et qu'il m'écrive le commentaire en colonne M.

Je vous est mis 2 captures d'écran pour que ce soit compréhensible.

MERCI DE VOTRE AIDE !



Voici le début de ma macro qui ne fonctionne pas encore :

' Régulation CIE-Template :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheets("SUIVTRANS EN COURS").Select
 
   Dim man, sin, typo, montant, CIE
 man = Cells.Find(what:="Libellé Écriture (manuelle)").Column
 sin = Cells.Find(what:="N° OP transfert de valeur (sinistre/sinistre d'origine)").Column
 typo = Cells.Find(what:="ENVOI BD / TYPO - MAJ MANUELLE SUR LISTE DEROULANTE CONTROLEE DANS ONGLET PARAMETRES + MACRO").Column
 montant = Cells.Find(what:="Montant Converti Signé").Column
 CIE = Cells.Find(what:="Code Payeur - Fs - à conserver sur 6 positions").Column
 
 '1ere ligne à comparer
 For i = 2 To derligne
    '2eme ligne à comparer
    For ii = i + 1 To derligne
        'Pas même ligne
        If i <> ii Then
            'Vérif REGULATION CIE-Template
            If Cells(i, sin) = Cells(ii, sin) And Cells(i, CIE) <> Cells(ii, CIE) Then
                Cells(i, typo).Value = "REGULATION CIE-Template"
                Cells(ii, typo).Value = "REGULATION CIE-Template"
            End If
        End If
    Next ii
 Next i

[ARTURO83]

Bonjour,

Une simple formule en colonne M ne suffirait-il pas? Exemple en M2 et à tirer vers le bas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NB.SI.ENS($J$2:$J$10000;J2;$R$2:$R$10000;R2)>1;"REGULATION CIE-Template";"")

Cdlt

[OBADJ]

Bonjour ARTURO83,

Merci de ta réponse, c'est vrai que c'est une bonne solution sauf que dans cette même colonnes, je dois mettre d'autre commentaire avec des critères différents donc si je la tire vers le bas jusqu'à mes 6500 lignes, pour mes autres commentaires sa risque de tous fausser...
Si tu préférés la formule est bonne mais j'ai d'autre commentaire du même type à mettre en colonnes M...

Tu en penses quoi ?

[BENNASR]

bonjour
inutile d'ouvrir deux discussions pour le même sujet, ça charge le forum

J'aimerais que sur un même numéro de sinistre (colonne J) et que ce même numéro de sinistre dispose de 2 codes CIE différents (colonnes R) alors je met un commentaire "Régularisation CIE-Template" en colonne M.

je comprends mal ta demande mais si on résume : tu cherches à compter la présence de chaque numéro de sinistre dans la colonne J et si c'est deux ou plus tu ajoute un commentaire "Régularisation CIE-Template" en colonne M. c'est ça non???
si non merci de données d'expliquer ta demande avec un exemple concret

[ARTURO83]

Si tu préférés la formule est bonne mais j'ai d'autre commentaire du même type à mettre en colonnes M...

D'accord, mais que ce soit en vba ou par formules, il faudra bien connaître les autres critères. Y en a t-il beaucoup?

[MarcelG]

Bonjour à vous,

Si je peux me permettre.

Pour chaque numéro de sinistre, je créerais un dictionnaire des compagnies.
Si ce dictionnaire comporte 2 clés alors la colonne M serait alimentée.

[OBADJ]

Le "but du jeu" est que pour un même numéro de sinistre, s'il y a la présence de 2 Numéro CIE différents en colonnes R alors je mets un commentaire en colonne M "REGULARISATION CIE-TEMPLATE".
Il s'agit exactement de cette formule, pour ceux qu'ils n'ont pas totalement compris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NB.SI.ENS($J$2:$J$10000;J2;$R$2:$R$10000;R2)>1;"REGULATION CIE-Template";"")

Concernant mes autres commentaires, voici le suivant : (j'aurais 7 commentaires en colonne M, es-ce-que c'est possible ?)

Si la sommes de montants (en colonne K) est un écart < ou = à 10euros et <ou= à -10euros pour un même numéro de sinistre (colonne J) et le même N° CIE (colonne R) alors je mets un commentaire (en colonne M) REGULATION ECART-TEMPLATE"

Après avoir réussi à faire la macro de mon TCD qui permet de trouvé les écarts (<ou = à 10 et -10 euros) des sommes des montants correspondant aux mêmes numéros de sinistre (cf : capture d'écran).

Je voudrais copier chaque numéro de sinistre trouvé par mon TCD en colonne D (Cf :capture d'écran TCD) dans l'onglet "TCD" sachant que ce numéro de sinistre copier dans mon filtre en colonne J de mon onglet "SUIVTRANS EN COURS" afin de pouvoir attribuer le commentaire "Régulation Ecart-Template" en colonne M.

Donc je voudrais que la macro vienne directement récupérer copier numéro de sinistre en colonne D dans mon TCD et le coller dans mon filtre en colonne J afin de pouvoir mettre mon commentaire comme indiqué ci-dessus.

MERCI DE VOTRE AIDE !

Voici mon code et je bloque à partir de :'Copier le N° sinistre dans "SUIVITRANS EN COURS" et mettre en typo "RUGULATION ECART-TEMPLATE"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
'TCD sur Feuille "TCD" 
ActiveWorkbook.Worksheets("SUIVTRANS EN COURS").PivotTables("Tableau croisé dynamique1"). _ 
PivotCache.CreatePivotTable TableDestination:="TCD!R3C1" 
 
Sheets("TCD").Select 
Cells(3, 1).Select 
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("N° OP transfert de valeur (sinistre/sinistre d'origine)") 
.Orientation = xlRowField 
.Position = 1 
End With 
ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _ 
PivotTables("Tableau croisé dynamique2").PivotFields("Montant Converti Signé") _ 
, "Nombre de Montant Converti Signé", xlCount 
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Nombre de Montant Converti Signé") 
.Caption = "Somme de Montant Converti Signé" 
.Function = xlSum 
End With 
 
Range("A4:B4").Select 
Range(Selection, Selection.End(xlDown)).Select 
Range("A4:B2699").Select 
Selection.Copy 
 
Range("D4").Select 
Selection.PasteSpecial Paste:=xlPasteValues 
Range("E3").Select 
Application.CutCopyMode = False 
ActiveCell.FormulaR1C1 = "filtre" 
Range("E3").Select 
Selection.AutoFilter 
ActiveSheet.Range("$D$3:$E$2699").AutoFilter Field:=2, Criteria1:="<=10", Operator:=xlAnd, Criteria2:="<=-10" 
 
'Copier le N° sinistre dans "SUIVITRANS EN COURS" et mettre en typo "RUGULATION ECART-TEMPLATE"' 
 
Dim Lg% 
 
typo = Cells.Find(what:="ENVOI BD / TYPO - MAJ MANUELLE SUR LISTE DEROULANTE CONTROLEE DANS ONGLET PARAMETRES + MACRO").Column 
Lg = Range("D65536").End(xlUp).Row 
derligne = Range("D" & Rows.Count).End(xlUp).Row 
For i = i + 1 To derligne 
Range("Di:D" & derligne).Copy 
 
Sheets("SUIVTRANS EN COURS").Select 
ActiveSheet.Range("$A$1:$AH$6117").AutoFilter Field:=10, Criteria1:= _ 
"B1730544792" 
'Essayé de copier coller la même commentaire" 
 
Range("M2740").Select 
Selection.FillDown 
ActiveCell.FormulaR1C1 = "REGULATION ECART-TEMPLATE" 
Range("M2740").Select 
Selection.FillDown 
Sheets("TCD").Select 
Range("D1517").Select 
Selection.Copy 
Sheets("SUIVTRANS EN COURS").Select 
ActiveSheet.Range("$A$1:$AH$6117").AutoFilter Field:=10, Criteria1:= _ 
"B1760521836" 
Next i

[BENNASR]

Pour gagner du temps j'ai souhaité recevoir un fichier sans macro avec un petit exemple..bref ..je crois qu'on peux faire ça sans passer par un TCD
sachant que je suis aussi débutant et autodidacte VBA
voila comment faire pour remplir la colonne M :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
Dim Tablo
Dim i As Long
Dim C As Integer
With Sheets("feuil1")
i = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To i
NUMSIN = .Cells(i, 10)
C = WorksheetFunction.CountIf(.Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row), NUMSIN)
If C > 1 Then .Cells(i, 13) = "Régularisation CIE-Template"
Next i
End With
End Sub

[OBADJ]

C'est top BENNSAR ça fonctionne bien mais es-ce que tu as pas oublié la condition qu'il doit y avoir 2 code CIE (colonne R) différents.

Concernant le TCD ce n'était pas pour ce commentaire mais le suivant comme indiqué car je suis obligé de faire la somme des montants pour savoir s'il y a un écart.

[BENNASR]

et c'est le cas non ?? le sinistre 125205 à deux sous code le 12 et le 13
pour ne pas perdre de temps merci de préparer un fichier test avec qq lignes
Pour la somme pas grave on peux se calculer la somme pour chaque code de sinistre

[OBADJ]

Non ce n'est pas le cas car quand je rentre la formule il prend pas en considération les codes CIE et je vois pas dans ta macro la prise en compte de la colonne R (CODE CIE). Exemple dans mon fichier excel ci-joint, en couleur jaune tu as bien le commentaire mais tu vois bien que leur codes CIE (colonne R) est identique alors que le commentaire est mit que lorsque il y la présence de 2 code CIE différent

Ensuite en couleur blanc il s'agit d'un écart de -0.09 euros et en couleur vert d'un écart de 10euros trouver car au TCD de la feuille TCD et en vert un écart de 10 euro pour le même numéro de sinistre et moi j'aimerais automatiser cela pour chaque écart qu'il voit de <10euros - >-10euros - <>0euros qu'il me mette un commentaire "REGULATION ECART-TEMPLATE" en colonne M.

Je t'ai pas mis le fichier original, c'est un extrait c'est pour cela qu'il n'y a pas les macros dans VBA.

Merci de ton aide, c'est très gentil !

[BENNASR]

re

merci de tester ça avant de continuer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub test()
Dim Tablo
Dim i As Long
Dim C As Integer
With Sheets("feuil1")
i = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To i
NUMSIN = .Cells(i, 10)
C = WorksheetFunction.CountIf(.Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row), NUMSIN)
If C > 1 Then
NUMSOUSSIN = .Cells(i, 18)
D = WorksheetFunction.CountIf(.Range("R2:R" & .Range("R" & Rows.Count).End(xlUp).Row), NUMSOUSSIN)
If D = 1 Then .Cells(i, 13) = "Régularisation CIE-Template"
End If
Next i
End With
End Sub

[BENNASR]

post ci haut normalement faux
testez ça svp

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
Dim Tablo
Dim i As Long
Dim C As Integer
With Sheets("feuil1")
i = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To i
NUMSIN = .Cells(i, 10)
NUMSOUSSIN = .Cells(i, 18)
C = WorksheetFunction.CountIf(.Range("J2:J" & .Range("J" & Rows.Count).End(xlUp).Row), NUMSIN)
D = WorksheetFunction.CountIf(.Range("R2:R" & .Range("R" & Rows.Count).End(xlUp).Row), NUMSOUSSIN)
If C > 1 And D >= 1 And C <> D Then .Cells(i, 13) = "Régularisation CIE-Template"
Next i
End With
End Sub

[OBADJ]

Oui voila c'est beaucoup mieux après je comprends pas pourquoi il me prend en compte dans quelque cas, juste un numéro de sinistre qui est différent du code CIE. Normalement il devrait comparer sur 2 mêmes sinistres ou plus, si le code CIE est différent ou pas...
Sinon dans les autres cas, ca marche mais je comprends pas pourquoi il me sors juste 1 numéro de sinistre...

[OBADJ]

Génial ça fonctionne enfin ! Merci !

Essayons maintenant les écarts... un peu plus complexe

Sur le fichier excel en pièce jointe :

En couleur blanc il s'agit d'un écart de -0.09 euros et en couleur vert d'un écart de 10euros trouver car au TCD de la feuille TCD et en vert un écart de 10 euro pour le même numéro de sinistre et moi j'aimerais automatiser cela pour chaque écart qu'il voit de <10euros - >-10euros - <>0euros qu'il me mette un commentaire "REGULATION ECART-TEMPLATE" en colonne M.

Parmis tous les montants, mon TCD fais la somme des montants des même numéro de sinistres et même CIE et j'ai fais un filtre sur mon TCD pour qu'il prenne que la sommes qui est < à 10-> à 10-<> à 0 euros. Et moi je veux que les N° sinistres trouvé par mon TCD je les copier un par un pour mettre mon commentaire sur la feuille 1 en colonne M. Et tous ça automatiquement

Je t'ai pas mis le fichier original, c'est un extrait c'est pour cela qu'il n'y a pas les macros dans VBA.

Merci de votre aide !

[BENNASR]

re
merci de tester ça avant de continuer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub test()
Dim Tablo
Dim j As Long
Dim C As Integer
With Sheets("feuil1")
j = .Range("A" & Rows.Count).End(xlUp).Row
.Range("M2:M" & j).ClearContents
For j = 2 To j
NUMSIN = .Cells(j, "J")
NUMSOUSSIN = .Cells(j, "R")
C = WorksheetFunction.CountIf(.Range("J2:J" & .Range("A" & Rows.Count).End(xlUp).Row), NUMSIN)
D = WorksheetFunction.CountIf(.Range("R2:R" & .Range("A" & Rows.Count).End(xlUp).Row), NUMSOUSSIN)
If C > D Then .Cells(j, 13) = "Régularisation CIE-Template"
Next j
 
 
Tablo = .Range("J2", "K" & .Range("K" & .Rows.Count).End(xlUp).Row)
 For j = 2 To j
 x = 0
   For i = LBound(Tablo, 1) To UBound(Tablo, 1)
             If .Cells(j, "j") = Tablo(i, 1) Then
               x = x + CDbl(Tablo(i, 2))
             End If
  Next i
  MsgBox CDbl(x)
Next j
 
 
 
End With
End Sub

[BENNASR]

Une proposition à tester et m'indiquer les insuffisances
je répète : je suis autodidacte donc à tester toute proposition sur copie de ton classeur et j'assume aucune responsabilité en cas de perte de données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Dim j As Long
Dim C As Integer
With Sheets("feuil1")
j = .Range("A" & Rows.Count).End(xlUp).Row
MsgBox j
.Range("M2:M" & j).ClearContents
For j = 2 To j
NUMSIN = .Cells(j, "J")
NUMSOUSSIN = .Cells(j, "R")
C = WorksheetFunction.CountIf(.Range("J2:J" & .Range("A" & Rows.Count).End(xlUp).Row), NUMSIN)
D = WorksheetFunction.CountIf(.Range("R2:R" & .Range("A" & Rows.Count).End(xlUp).Row), NUMSOUSSIN)
If C > D Then .Cells(j, 13) = "Régularisation CIE-Template"
Next j
 
Tablo = .Range("J2", "K" & .Range("K" & .Rows.Count).End(xlUp).Row)
 For j = 2 To j - 1
 x = 0
   For i = LBound(Tablo, 1) To UBound(Tablo, 1)
             If .Cells(j, "j") <> "" And .Cells(j, "j") = Tablo(i, 1) Then
               x = x + CDbl(Tablo(i, 2))
             End If
  Next i
  If x > -10 And x < 10 Then .Cells(j, 13) = "REGULATION ECART-TEMPLATE"
  Next j
End With
End Sub

[OBADJ]

Ça ne fonctionne pas BENNASAR, quand je lance la macro ça m'affiche un message avec une fenêtre avec un "0" via ma capture pièce jointe.
Je suis pas sûr mais je crois que c'est à cause du x=0 j'ai pas compris ça te servait à quoi ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Tablo = .Range("J2", "K" & .Range("K" & .Rows.Count).End(xlUp).Row)
 For j = 2 To j
 x = 0

[BENNASR]

Et la pièce jointe ? Normalement le code fait l'affaire non ?
Je crois que y a erreur d'adaptation !!! teste la pièce jointe et me dire ce que ne va pas

[OBADJ]

Bonjour BENNASR,

Tu es vraiment un Génie, il faudrait que j'y arriveà le faire tous seul ! T'es grave fort

Juste une petite question tu vois ton codage pour les écart, es-ce-que il prend toujours en considération la condition qu'il faut que pour un même N° de sinistre, il faut le Même Code CIE.
Et aussi pour mes prochain commentaire, j'espère que ca va pas effacer les commentaire précédents car ya des conditions qui sont proches des précédent donc ca peut fausser... ?

Un plaisir que tu es trouvé la solution sans TCD ! T'es un BOSS ahah

Merci de ta réponse