[Excel] Grosses difficultés pour écrire le script d'une macro

Question

Bonsoir à tous,




Je suis actuellement embarqué dans un cauchemar qui semble ne pas vouloir se finir... Voici l'histoire :




J'ai une feuille excel contenant une liste de 7800 contacts pour lesquels il y a de nombreux détails (donc plein de colonnes aussi). Chaque contact est relatif à une société (et dans la liste il a souvent plusieurs contacts qui peuvent avoir la même société). Je viens de passer 4 mois à travailler sur ces contacts et leurs détails pour les nettoyer à la main (un par un) :  numéros de téléphones, adresses, etc., etc. Bref, une horreur...




De l'autre côté j'ai une autre feuille excel contenant une liste de 5000 sociétés pour lesquelles il y a aussi de nombreux détails. Ce sont normalement les mêmes sociétés qui sont dans liste des contacts mais parfois il y a des sociétés dans cette liste qui ne sont pas présentes dans la liste des contacts. Réciproquement, certaines sociétés présentes dans la liste des contacts ne sont pas présentes dans la liste des sociétés.




L'idée serait d'avoir une structure commune pour rapidement vérifier si les détails dans la liste des sociétés correspondent aux détails dans la liste des contacts. Cette structure commune (c'est à dire que la société X ligne 3 dans la liste des contacts se retrouve également à la ligne 3 dans la liste des sociétés) me permettrait de gagner énormément de temps.






J'ai donc pensé à un traitement successif mais je n'arrive pas à faire les macros :

Etape 1




Appliquer une macro qui vérifierait si telle société (dans la liste des sociétés, feuille 1) est présente dans la liste des contacts (feuille 2) grâce à son nom.


Si oui, il ne se passerait rien


Si non, la ligne entière serait coupée et collée dans une troisième feuille




Je précise que dans la liste des sociétés (feuille 1), le nom de la société est dans la colonne V et que dans la liste des contacts (feuille 2), le nom de la société est dans la colonne B.




Ceci devrait éliminer le problème des sociétés présentes dans la liste des sociétés mais absentes dans la liste des contacts.







Etape 2







Appliquer une macro qui vérifierait si telle société (dans la liste des contacts, feuille 2) est présente dans la liste des sociétés (feuille 1) grâce à son nom.


Si oui, il ne se passerait rien


Si non, la ligne entière serait coupée et collée dans une quatrième feuille




Ceci devrait éliminer le problème des sociétés présentes dans la liste des contacts mais absentes dans la liste des sociétés.







Etape 3







Appliquer une macro qui vérifierait si le nom d'une société dans la liste des sociétés (feuille 1) est à la même ligne dans la liste des contacts (feuille 2).


Si oui, il ne se passerait rien


Si non, il faudrait insérer une ligne vide après la ligne en question et poursuivre la vérification sans bloquer sur la ligne vide (évidemment si la macro teste ensuite la ligne vide, cela va créer un sacré paquet de ligne vide)






Voilà un exemple : avant la 3ème étape

Numéro de ligne ..... Colonne V (feuille1) ..... Colonne B (feuille 2)
1............................. Société X ..................... Société X
2............................. Société Y ..................... Société X
3............................. Société Z ..................... Société X
4............................. etc. .............................. Société Y
5............................. etc. .............................. Société Z

après la 3ème étape

Numéro de ligne ..... Colonne V (feuille1) ..... Colonne B (feuille 2)

1............................. Société X ..................... Société X

2.............................                 ..................... Société X

3.............................                 ..................... Société X

4............................. Société Y ...................... Société Y

5............................. Société Z ...................... Société Z

En effet, souvent plusieurs contacts ont la même société ce qui décale toute la liste...




Voilà, vous savez tout... Navré de vous déranger avec toute l'histoire mais je ne sais pas moi même si les macros que je propose sont faisables alors bien qu'elle soit longue, j'espère qu'elle vous permettra de comprendre tout le problème. Vous l'aurez compris, je suis une tanche en Visual Basic .




Je m'en remet donc à votre infinie connaissance en espérant que je pourrais couler des jours heureux à nouveau .






Merci d'avance,

Ganach

P.S. : Si quelqu'un a une autre idée que mes macros pour résoudre ce soucis, je suis ouvert à toutes les possibilités tant que cela abouti au même résultat .

Ganach001 · Answer

Excusez-moi de double-poster mais concentré dans mon écriture, j'ai oublié de faire mention de plusieurs choses :

- J'utilise Microsoft Excel 2003 (je ne sais pas si cela a une incidence)
- J'espère avoir posté dans le bon thème, si ce n'est pas le cas j'en suis désolé

PCPT · Answer

salut,

excel =  VBA, topic déplacé vers le bon forum

quelque chose me bloque dans tes étapes....
si plusieurs contacts sont dans la même société, tu ne vas pas perdre des infos (ou de l'organisation sur le résultat final voulu tout au moins) en coupant les lignes des 2 classeurs?

çà ne serait pas plus pratique d'avoir pour résultat un seul nouveau classeur avec pour référence les contacts, liés aux sociétés (donc doublons à l'affichage possible), complété par des "lignes" vides pour les cas de sociétés sans contacts ?

ton explication est bonne, merci d'avoir été si précis. c'est juste le "couper" qui me bloque, tu risque d'avoir au final 4 classeurs, et non "2 nouveaux, ré-organisés"

sinon sur le principe, tu parcours une colonne avec ce type de boucle 
( syntaxe à vérifier ) 
:
dim i as integer, j as integer
, kNew1 as integer


kNew1 =1 


for i = 1 to 7800 
  for j=1 to 5000
    if sheets("feuill1").range("V:" & i) = sheets("feuill2").range("B:" & j) then
      'même société des 2 côtés
      
'insérer la ligne sur 
kNew1, de la feuille 3, genre .select, .copy, puis .select feuille3 et .paste
      
kNew1=
kNew1+1


      if i=j then
        'toutes les 2 sur la même ligne, simulation de 2 lignes vides
        
kNew1=
kNew1+2
       
      endif
    endif
    doevents
  next j
next i

sauve bien ton fichier avant en tout cas ^^
4 mois de boulot, faudrait pas perdre çà 

<hr size="2" width="100%" />
Prenez un instant pour répondre à [sujet-SONDAGE-POP3-POUR-CS_769706.aspx ce sondage] svp

cs_BimBam · Answer

Vous cherchez a faire beaucoup de chose en meme temps ^^

Le plus simple je pense est de passer par Access pour trier/classer/uniformiser vos enregistrements.

Si vous possédez Access importez vos fichiers excel pour crééer 2 tables correspondant à votre fichier "Contact" et votre fichier "Société"

Onglet "Table" 
Click droit "Importer"
Type de Fichier "Excel"
Prenez votre 1° fichier et appelez la table "T_contact"
Procédez de même avec le fichier "Société" pour créer la table "T_Societe"

Après vous pourrez utiliser les requêtes création, mise à jour ou Ajout pour uniformiser vos fichiers en 1 seul.

Sinon tout a fait possible de le faire par macro (inspirée du code proposé précédemment) mais ca risque d'etre long car pour chaque contact vous allez rebalayer toutes les sociétés de votre 2° fichier (5000 lignes a priori). 
Pour gagner en temps classez les 2 fichiers par sociétés et donc si dans le 2° fichier la société n'est plus la meme que celle du contact (de votre 1° fichier)  vous pouvez arreter la lecture dans le 2° tant que vous n'avez pas changer de société dans le 1°. (Moyennement clair me Mp si besoin)

Avec des opération de requetes sur des tables les parcours sont automatiques pour peu que vous ayez des notions Sql.

Bon courage 


Ben Harper is our Messiah

Ganach001 · Answer

J'ai oublié de préciser quelques points mais d'abord, merci de me répondre aussi rapidement !




En fait, je ne souhaite pas avoir au final un seul et même fichier comportant une seule et même liste. Ces deux listes (contacts et sociétés) sont supposées être importées séparément par la suite dans d'autres logiciels. C'est pour cette raison que j'utilise Excel et non Access. De la même manière, ce n'est pas un problème si je me retrouve avec 4 feuilles. Toutes ces opérations sont destinées à me permettre de travailler rapidement sur ma liste des sociétés, ce n'est pas le résultat final de chez final. Par la suite, je pourrais réintégrer les listes présentes en feuille 3 et 4 sans trop de problèmes.




Comme je vous l'ai dis, ma liste des contacts est totalement prête (toutes les informations relatives aux contacts sont valides et vérifiées) ; ce qui n'est pas le cas de ma liste des sociétés (où toutes les informations relatives aux sont à vérifier). Pour m'éviter d'avoir à revérifier chacune des 5000 lignes de la liste des sociétés, il me faudrait une concordance entre la liste des contacts et la liste des sociétés. Pour me permettre d'avoir cette concordance, je dois "éliminer" (temporairement) les sociétés qui ne sont pas présentes dans les deux listes (d'où l'étape 1 et 2), ce qui implique le "couper". Je précise aussi que le temps de traitement des macros n'est pas un problème (dans la mesure où je ne les excécute qu'une seule fois à priori).






La raison pour laquelle je veux cette concordance, c'est pour facilement comparer les colonnes de détails des deux listes. Je m'explique :




Dans la liste des contacts (feuille 2), il y a par exemple un colonne numéro de téléphone (imaginez 7800 numéros de téléphone dans 35 pays à formatter ). Dans la liste des sociéts (feuille 1), il y a aussi une colonne de numéro de téléphone et ces numéros sont censés être concordants. Pour vérifier si ces numéros sont concordants je vais dans la liste des sociétés et j'insère (temporairement) la liste des numéros de téléphones des contacts à côté de la liste des numéros de téléphones des sociétés. Par la suite je peux même vérifier automatiquement la concordance avec une formule de ce typer : =1*NON(ESTNA(EQUIV(I8;A:A;0))) 






Mais pour que cela marche, il faut que les lignes soient les mêmes, s'il y a un décalage tout mon plan s'écroule.

cs_Joh76 · Answer

Bonjour Ganach001,


Voici le code pour l'étape1 et 2:
/////////////////////////////////
Sub Etape1()
Dim a As Integer ' itération feuille1
Dim b As Integer ' itération feuille1
Dim c As Integer ' itération feuille2
Dim Existe As Boolean ' 'Variable à vrai si ligne existe
'Arrête le rafraîchissement de l'écran pour être plus rapide
Application.ScreenUpdating = False
'affectation de b
b = Worksheets("Feuil1").Range("V65536").End(xlUp).Row


' scrute ligne par ligne feuille1
For a = 0 To b
    ' initialisation de Existe à faux
    Existe = False
    'scrute ligne par ligne feuille2
    For c = 0 To Worksheets("Feuil2").Range("B65536").End(xlUp).Row
        'vérifie si la ligne existe
        'conversion en texte et majuscule
        If CStr(UCase(Worksheets("Feuil1").Range("V1").Offset(a).Value)) = CStr(UCase(Worksheets("Feuil2").Range("B1").Offset(c).Value)) Then
            Existe = True
            Exit For
        End If
    Next c
    
    'Si n'existe pas
    If Existe = False Then
        Worksheets("Feuil1").Range("V1").Offset(a).EntireRow.Copy Worksheets("Feuil3").Range("A1").Offset(Worksheets("Feuil3").Range("V65536").End(xlUp).Row)
        Worksheets("Feuil1").Range("V1").Offset(a).EntireRow.Delete
        a = a - 1
        b = Worksheets("Feuil1").Range("V65536").End(xlUp).Row
    End If


Next a
End Sub


Sub Etape2()
Dim a As Integer ' itération feuille2
Dim b As Integer ' itération feuille2
Dim c As Integer ' itération feuille1
Dim Existe As Boolean ' 'Variable à vrai si ligne existe
'Arrête le rafraîchissement de l'écran pour être plus rapide
Application.ScreenUpdating = False
'affectation de b
b = Worksheets("Feuil2").Range("B65536").End(xlUp).Row


' scrute ligne par ligne feuille1
For a = 0 To b
    ' initialisation de Existe à faux
    Existe = False
    'scrute ligne par ligne feuille2
    For c = 0 To Worksheets("Feuil1").Range("V65536").End(xlUp).Row
        'vérifie si la ligne existe
        'conversion en texte et majuscule
        If CStr(UCase(Worksheets("Feuil2").Range("B1").Offset(a).Value)) = CStr(UCase(Worksheets("Feuil1").Range("V1").Offset(c).Value)) Then
            Existe = True
            Exit For
        End If
    Next c
    
    'Si n'existe pas
    If Existe = False Then
        Worksheets("Feuil2").Range("B1").Offset(a).EntireRow.Copy Worksheets("Feuil4").Range("A1").Offset(Worksheets("Feuil4").Range("B65536").End(xlUp).Row)
        Worksheets("Feuil2").Range("B1").Offset(a).EntireRow.Delete
        a = a - 1
        b = Worksheets("Feuil2").Range("B65536").End(xlUp).Row
    End If


Next a
End Sub
////////////////////////////////////////////////////////////////////////////////////////////
En partant du principe qu'il n'y a pas de titre dans tes feuilles 1 et 2, autrement dit que la valeur dans la cellule V1 de feuille 1 est une société, et que les feuilles3 et 4 existe (vierge).
Fait une copie de ton fichier d'origine, en programmation il faut toujours être prudent!

Concernant l'étape 3, tu ne veux insérer des lignes vide que dans la feuille1?

Il est possible de faire une macro qui s'occupe de tout.

cordialement

Ganach001 · Answer

O_O

J'hallucine 

Çà c'est de la macro 

Alors pour te répondre, oui il ne faut des lignes vides que dans la feuille 1. 

Si c'est possible d'avoir The Macro qui fait les 3 trois étapes à elle toute seule çà serait juste magnifique 

Un très grand merci à toi pour t'occuper de mon cas !

P.S. : J'ai déjà fait les copies, mon processeur n'attend plus que toi

cs_Joh76 · Answer

Salut,

J'ai oublié de te demander, est-ce que le nom des société est écrit à l'identique sur la feuille 1 et feuille 2?

Je m'explique, si sur ta feuille 1 dans la cellule V1 tu as le nom d'une société qui est, à priori, identique au nom de la cellule B1 mais qu'à la fin de cette dernière il y a un espace que tu ne vois pas, ces deux sociétés ne seront pas identiques!
De même s'il y à des accents dans l'une et pas dans l'autre.

Pour ce qui concerne les espaces, on peut les supprimer de la variable lors du test avec Trim pour supprimer les espaces à gauche et à droite.

avec l'étape 3, en programme complet ça donne:

Sub Etape1_2_3()
Dim a As Integer ' itération feuille1
Dim b As Integer ' itération feuille1
Dim c As Integer ' itération feuille2
Dim Existe As Boolean ' 'Variable à vrai si ligne existe
Dim Tps As Date ' variable pour le temps mis pour la tache
'Arrête le rafraîchissement de l'écran pour être plus rapide
Application.ScreenUpdating = False
' affectation de Tps
Tps = Time


'///////////////// ETAPE 1 //////////////////
'affectation de b
b = Worksheets("Feuil1").Range("V65536").End(xlUp).Row


' scrute ligne par ligne feuille1
For a = 0 To b
    ' initialisation de Existe à faux
    Existe = False
    'scrute ligne par ligne feuille2
    For c = 0 To Worksheets("Feuil2").Range("B65536").End(xlUp).Row
        'vérifie si la ligne existe
        'conversion en texte et majuscule, supression des espaces gauche et droite
        If CStr(UCase(Trim(Worksheets("Feuil1").Range("V1").Offset(a).Value))) = CStr(UCase(Trim(Worksheets("Feuil2").Range("B1").Offset(c).Value))) Then
            Existe = True
            Exit For
        End If
    Next c
    
    'Si n'existe pas
    If Existe = False Then
        Worksheets("Feuil1").Range("V1").Offset(a).EntireRow.Copy Worksheets("Feuil3").Range("A1").Offset(Worksheets("Feuil3").Range("V65536").End(xlUp).Row)
        Worksheets("Feuil1").Range("V1").Offset(a).EntireRow.Delete
        a = a - 1
        b = Worksheets("Feuil1").Range("V65536").End(xlUp).Row
    End If


Next a




'///////////////// ETAPE 2 //////////////////
'affectation de b
b = Worksheets("Feuil2").Range("B65536").End(xlUp).Row


' scrute ligne par ligne feuille2
For a = 0 To b
    ' initialisation de Existe à faux
    Existe = False
    'scrute ligne par ligne feuille1
    For c = 0 To Worksheets("Feuil1").Range("V65536").End(xlUp).Row
        'vérifie si la ligne existe
        'conversion en texte et majuscule, supression des espaces gauche et droite
        If CStr(UCase(Trim(Worksheets("Feuil2").Range("B1").Offset(a).Value))) = CStr(UCase(Trim(Worksheets("Feuil1").Range("V1").Offset(c).Value))) Then
            Existe = True
            Exit For
        End If
    Next c
    
    'Si n'existe pas
    If Existe = False Then
        Worksheets("Feuil2").Range("B1").Offset(a).EntireRow.Copy Worksheets("Feuil4").Range("A1").Offset(Worksheets("Feuil4").Range("B65536").End(xlUp).Row)
        Worksheets("Feuil2").Range("B1").Offset(a).EntireRow.Delete
        a = a - 1
        b = Worksheets("Feuil2").Range("B65536").End(xlUp).Row
    End If


Next a


'///////////////// ETAPE 3 //////////////////
' scrute ligne par ligne feuille2
For a = 0 To Worksheets("Feuil2").Range("B65536").End(xlUp).Row
    ' test si les valeurs sont différentes
    If CStr(UCase(Trim(Worksheets("Feuil2").Range("B1").Offset(a).Value))) <> CStr(UCase(Trim(Worksheets("Feuil1").Range("V1").Offset(a).Value))) Then
        'insère une ligne
        Feuil1.Range("V1").Offset(a).EntireRow.Insert
    End If
Next a


Tps = Time - Tps
MsgBox "Demande terminée au bout de :" & Tps, vbInformation


End Sub

Bonne continuation!
Joh76

cs_Joh76 · Answer

En partant du principe que V et B sont triés dans le même ordre....

Ganach001 · Answer

Salutations !




Alors j'ai testé le script pour l'étape 1 et 2 (message du 30/12 à 23h) et il marche parfaitement ! Donc déjà un grand merci pour çà 




Par contre, je viens de tester le dernier script que tu as envoyé et çà n'a pas marché. Il ne m'a pas fait de couper/coller dans les feuilles 3 et 4. Sur le plan des lignes vides, il m'en a fait quelques unes mais à un moment il s'est bloqué (environ 7500 lignes vides d'un coup).




J'espère que tu pourra régler ce problème.. Un grand merci d'avance en tout cas !

Ganach

P.S : Je vais refaire encore une fois un test au cas où l'erreur viendrait d'une mauvaise manipulation de ma part.

cs_Joh76 · Answer

Salut,

C'est normal que le script de l'étape 3 n'ait pas fait de couper/coller, i est prévu pour insérer des lignes vides dans la feuille 1 comme tu l'as demandé dans ton tout premier message.

Concernant le bogues peux-tu me dire si à l'endroit où il a inséré les lignes c'est la même société?

Ganach001 · Answer

Je croyais que le script posté le 31/12 regroupait l'étape 1, 2 et 3 c'est pour çà que je n'ai pas compris pourquoi il n'avait pas fait les couper/coller lors de mon test (sachant que dans ce test j'ai pris tout le script posté le 31/12 que j'ai directement exécuté sur un fichier test). Je  dois donc exécuter sur le fichier d'abord le script posté le 30/12 puis celui du 31/12 ?




Autrement, vis-à-vis des lignes vides, il s'est bloqué à un société écrite exactement de la même manière dans les deux listes. Mais comme il n'y a pas eu de couper/coller, forcément il y avait plein de différences dans les listes.




Je vais refaire un test en appliquant le script posté le 30/12 puis celui du 31/12.

cs_Joh76 · Answer

Oui , le script posté le 31/12 intègre les 3 étapes.
Le mieux est que tu refasses une copie de ton fichier d'origine et que tu exécute cette macro (Etape1_2_3).
Par contre j'ai cherché pourquoi tu as une 7500 ligne vides d'un coup, je me suis aperçu qu'il ne faut pas avoir de cellule vide dans la colonne V de la feuille 1 entre le première et la dernière, sinon cela fausse tout!
Et n'oublie pas de trier tes tableau par rapport aux colonnes V et B dans le même ordre (croissant ou décroissant, comme tu veux).

cs_Joh76 · Answer

Si ça ne marche toujours pas, reprends le script du 30/12,  repars sur une copie de ton fichier puis exécute l'étape 1 et 2.
 Ensuite il faudrait que tu m'envois le contenu des colonnes V de la feuille 1 et B de la feuille 2. Si ce n'est pas trop personnel. Cela m'aiderai car ce n'est pas facile de dépanner comme ça.

Ganach001 · Answer

Salutations,

Je crois que j'ai trouvé d'où venait le bug !!




D'une part, ta macro du 31/12 trie effectivement avec les couper/coller, il s'agissait d'une erreur de manipulation de ma part. D'autre part, il n'y a pas de cellule de nom d'entreprise vide dans la feuille 1 entre la 1ère entreprise et la dernière.




Mais, au moment où le script me fait le paquet de lignes vides, il y a un doublon. C'est à dire qu'il y a des lignes blanches de la ligne 69 (à cette ligne, il y a disons la société X) à la ligne 7500 et quelques (et à cette ligne aussi il y a la société X). Je ne savais pas que cela pouvait avoir une incidence mais effectivement, à certains moment il se peut qu'il y ait plusieurs fois la même société (différentes filiales) mais avec exactement le même nom dans la colonne V.




Peut-être pourrais-tu insérer un ligne de code pour modifier le nom (par exemple mettre un chiffre après le nom de telle sorte qu'il n'y ait plus ce bug) ?




J'espère que tu pourras trouver une solution. Si toutefois, tu pense que le problème ne vient pas de là, je t'enverrais des données pour que tu puisse voir par toi-même.

cs_Joh76 · Answer

Salut, 
Il faudrait que tu m'envois un message avec les données de la colonne V et un autre avec B, ce sera plus facile pour moi!
Tu vas sur ma fiche en cliquant sur mon nom et tu verras "Ecrire un message privé".
Même si ça sature le peu que j'aurais sera utile.

[Excel] Grosses difficultés pour écrire le script d'une macro

15 réponses

Votre réponse

Discussions similaires