Discussion :

[danydanydan]

Rebonjour à tous
Merci encore à ceux qui m'ont aider sur la création d'une liste d'équivalences!
Je commence à comprendre un peu la structure VBA mais c'est pas encore ça !

J'essaye de rajouter une fonctionnalité qui serait de copier vers un onglet existant si il n'y a pas de match dans la liste d'équivalences...

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
61
62
63
64
65
66
67
 
Sub Remplacement_IP()
'Déclarations =============================
Dim Cl_L As Workbook
Dim Cl As Workbook
Dim F_L As Worksheet
Dim F As Worksheet
Dim Cel_L As Range
Dim Cel As Range
Dim Flg As Boolean
Dim Plage_T As String
'Dim i As Integer
'MEI ======================================
Set Cl = ActiveWorkbook
Set F = ActiveSheet
Set F_Inco = Cl.Sheets(4)
'Ouverture classeur liste si pas ouvert ----
Flg = True
For Each Cl_L In Workbooks
    If Cl_L.Name = "Liste IP - source.xls" Then
        Flg = False
        Exit For
    End If
Next Cl_L
If Flg Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\Liste IP - source.xls"
    Set Cl_L = ActiveWorkbook
End If
'si macro depuis classeur liste => sortie
If Cl.Name = Cl_L.Name Then Exit Sub
Set F_L = Cl_L.Sheets(1)
'Définition de la plage de remplacement --------------
Plage_T = F.Range("F1:F" & _
F.UsedRange.SpecialCells(xlCellTypeLastCell).Row).Address(0, 0) & "," & _
F.Range("H1:H" & F.UsedRange.SpecialCells(xlCellTypeLastCell).Row).Address(0, 0)
'Remplacement ===============================
 
For Each Cel In F.Range(Plage_T).Cells
    If Not (IsEmpty(Cel)) Then
        Flg = True
        For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel Then
                Cel = Cel_L.Offset(0, 1)
                Flg = False
                Exit For
            End If
        Next Cel_L
       If Flg Then
            If Cel.Interior.ColorIndex <> xlNone Then
 
            Else
                Cel.Interior.ColorIndex = 3
            End If
 
 
 
        Else
          Cel.Interior.ColorIndex = Cel_L.Interior.ColorIndex
        End If
 
 
 
    End If
 
Next Cel
Cl_L.Close
End Sub

If Flg Then
If Cel.Interior.ColorIndex <> xlNone Then

Else
Cel.Interior.ColorIndex = 3
Je pense qu'ici il serait possible de copier la cellule vers un nouvel onglet... j'ai essayé pendant une petite heure de trouver comment faire mais pour moi le problème c'est que c'est un range et que je veux copier vers une cellule... Est il possible de faire Cast ? si vous avez une autre idée n'hésitez pas!


Merci d'avance !

[Gorfael]

Salut danydanydan
Le problème de créer un nouveau sujet, c'est que tu perds les "abonnés" à l'ancien, et donc, il faut tout ré-expliquer.

Je pense qu'ici il serait possible de copier la cellule vers un nouvel onglet... j'ai essayé pendant une petite heure de trouver comment faire mais pour moi le problème c'est que c'est un range et que je veux copier vers une cellule... Est il possible de faire Cast ? si vous avez une autre idée n'hésitez pas!

Si tu veux apprendre, le mieux, c'est de mettre le code que tu as trouvé. Comme ça, on peut partir de ta manière de travailler, au lieu que tu partes de la notre.

Cast ? Je ne connais pas cette instruction. Elle est supposée faire quoi ?

Si j'ai bien compris, tu voudrais que si ton IP n'existe pas, elle soit recopiée en A de la feuille support de liste ?
Problème : La macro d'origine transforme les IP en noms part rapport à une liste => elle fait une comparaison "basique".
Si tu la relances une 2me fois, elle ne trouve plus aucune correspondance, puisque les IP ont été remplacées.
Donc il faut redéfinir une partie du code :

Code :

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

1
2
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel Then

Pour chaque cellule de la colonne A, si l'IP correspend à la valeur de Cel alors.
Une solution simple :

Code :

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

1
2
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel or Cel_L.Offset(0,1) = Cel Then

On compare Cel à Cel_L (colonne A) ou à Cel_L(même ligne, colonne+1) (Colonne B)
Avantage : pas beaucoup de modif
Inconvénient : En cas d'égalité, on recopie le nom

Donc, si on a pas recopié le nom, Flg reste à Vrai.
Dans ce cas, on met la couleur rouge
cel.interior.ColoIndex = 3
et on copie la valeur de Cel à la suite de la liste
cel.copy F_L.Range("A" & F_L.[A65536].end(xlUp).row +1)
La propriété Copy fonctionne mal avec les Offset

Pourrait être bien, mais :
en ligne 12 tu as IP_12 et en ligne 13 tu as IP_13, IP inconnues toutes les 2
à la ligne 14 tu as IP_12 et à la ligne 15 tu as IP_13

Ta macro arrive sur la ligne 12 => inscrit IP_12 dans la liste
arrive sur la ligne 13 => inscrit IP_13 dans la liste
ça, c'est encore bon.
Arrive sur la ligne 14 => reconnaît IP_12 en A et la remplace par la valeur de la colonne B, même ligne : c'est-à-dire par "", puisqu'elle vient juste d'être mise et qu'il n'y a pas de nom correspondant
Idem pour la ligne 15.

Donc tu viens d'effacer 2 IP, mais lesquelles ?

Il faut :
- soit que les IP inconnues soient copiées ailleurs que dans la liste
- soit qu'avant de considérer que l'IP peut-être remplacée par le nom, tu teste la présence du nom

Code :

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

1
2
ForEach Cel_L In F_L.UsedRange.Columns("A").Cells
            If (Cel_L = Cel or Cel_L.Offset(0,1) = Cel) And not(isempty(Cel_L.Offset(0,1))) Then

et la copie de l'adresse IP ne peut se faire que si elle n'existe pas déjà dans la liste
[code]flg=true
ForEach Cel_L In F_L.UsedRange.Columns("A").Cells

next cel_l

[Gorfael]

Salut danydanydan
Le problème de créer un nouveau sujet, c'est que tu perds les "abonnés" à l'ancien, et donc, il faut tout ré-expliquer.

Je pense qu'ici il serait possible de copier la cellule vers un nouvel onglet... j'ai essayé pendant une petite heure de trouver comment faire mais pour moi le problème c'est que c'est un range et que je veux copier vers une cellule... Est il possible de faire Cast ? si vous avez une autre idée n'hésitez pas!

Si tu veux apprendre, le mieux, c'est de mettre le code que tu as trouvé. Comme ça, on peut partir de ta manière de travailler, au lieu que tu partes de la notre.

Cast ? Je ne connais pas cette instruction. Elle est supposée faire quoi ?

Si j'ai bien compris, tu voudrais que si ton IP n'existe pas, elle soit recopiée en A de la feuille support de liste ?
Problème : La macro d'origine transforme les IP en noms part rapport à une liste => elle fait une comparaison "basique".
Si tu la relances une 2me fois, elle ne trouve plus aucune correspondance, puisque les IP ont été remplacées.
Donc il faut redéfinir une partie du code :

Code :

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

1
2
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel Then

Pour chaque cellule de la colonne A, si l'IP correspend à la valeur de Cel alors.
Une solution simple :

Code :

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

1
2
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel or Cel_L.Offset(0,1) = Cel Then

On compare Cel à Cel_L (colonne A) ou à Cel_L(même ligne, colonne+1) (Colonne B)
Avantage : pas beaucoup de modif
Inconvénient : En cas d'égalité, on recopie le nom

Donc, si on a pas recopié le nom, Flg reste à Vrai.
Dans ce cas, on met la couleur rouge
cel.interior.ColoIndex = 3
et on copie la valeur de Cel à la suite de la liste
cel.copy F_L.Range("A" & F_L.[A65536].end(xlUp).row +1)
La propriété Copy fonctionne mal avec les Offset

Pourrait être bien, mais :
en ligne 12 tu as IP_12 et en ligne 13 tu as IP_13, IP inconnues toutes les 2
à la ligne 14 tu as IP_12 et à la ligne 15 tu as IP_13

Ta macro arrive sur la ligne 12 => inscrit IP_12 dans la liste
arrive sur la ligne 13 => inscrit IP_13 dans la liste
ça, c'est encore bon.
Arrive sur la ligne 14 => reconnaît IP_12 en A et la remplace par la valeur de la colonne B, même ligne : c'est-à-dire par "", puisqu'elle vient juste d'être mise et qu'il n'y a pas de nom correspondant
Idem pour la ligne 15.

Donc tu viens d'effacer 2 IP, mais lesquelles ?

Il faut :
- soit que les IP inconnues soient copiées ailleurs que dans la liste
- soit qu'avant de considérer que l'IP peut-être remplacée par le nom, tu teste la présence du nom

Code :

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

1
2
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If (Cel_L = Cel or Cel_L.Offset(0,1) = Cel) And not(isempty(Cel_L.Offset(0,1))) Then

et la copie de l'adresse IP ne peut se faire que si elle n'existe pas déjà dans la liste

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
if flg=true then
cel.interior.colorindex=3
For Each Cel_L In F_L.UsedRange.Columns("A").Cells
if cel_l=cel then 
flg=false
exit for
endif
next cel_l
if flg then cel.copy F_L.Range("A" & F_L.[A65536].end(xlUp).row +1)
else
cel.interior.colorindex=xlnone
endif

Mais comme tout ceci est fait en direct sur le forum, ce n'est évidemment pas testé.

A+

[danydanydan]

wow c'est vraiment sympa d'avoir la patience de m'expliquer tout ça!!

j'avais marqué l'ancien topic comme résolu c'est pour ça que j'en est créer un nouveau...

Pour le Cast je parlais juste de la propriété qui permet de faire passer un int en long par exemple En gros je voulais transformer un range en cell

Le problème de recopier dans la liste ne se pose pas : je me suis mal exprimé je veux recopier l'ip qui n'a pas de nom équivalent dans un onglet vide.

J'accepte avec grand plaisir qu'on regarde ce que j'avais penser faire !
Je met le bout de code que j'avais penser mettre mais qui ne marche pas (vraiment pas grand chose et ça ne marche pas )

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
Dim F_Inco as Worksheet
Dim i as Integer
-----
Set F_Inco = Cl.Sheets("Inconnu")
-----

For Each Cel In F.Range(Plage_T).Cells
    If Not (IsEmpty(Cel)) Then
        Flg = True
        For Each Cel_L In F_L.UsedRange.Columns("A").Cells
            If Cel_L = Cel Then
                Cel = Cel_L.Offset(0, 1)
                Flg = False
                Exit For
            End If
        Next Cel_L
       If Flg Then
            If Cel.Interior.ColorIndex <> xlNone Then
                       
            Else
                Cel.Interior.ColorIndex = 3
                F_Inco.Cells(i,0) = Cel
                i = i + 1
            End If
                                                          
        Else
          Cel.Interior.ColorIndex = Cel_L.Interior.ColorIndex
        End If
  
        
    
    End If
    
Next Cel
Cl_L.Close
End Sub

[Gorfael]

salut danydanydan et le forum

j'avais marqué l'ancien topic comme résolu c'est pour ça que j'en est créer un nouveau...
C'était pas une critique, juste une explication

Pour le Cast je parlais juste de la propriété qui permet de faire passer un int en long par exemple En gros je voulais transformer un range en cell
Range=plage de cellules(1 ou plusieurs)
cell n'existe pas à ma connaissance.
Cells est une propriété de range ou sheet
Clng() convertit en nombre Long

On va passer le code qui devrait fonctionner, on s'occupe juste de ce que je ne comprends pas, ou qui me semble bizarre :

Dim i as Integer
i ici te sert à définir un numéro de ligne. évidemment, tu ne penses pas avoir une ligne supérieure à 32767, mais le jour où ça arrive, on passe du temps à trouver l'erreur.
Depuis, je déclare mes variables "ligne" en Long

If Cel.Interior.ColorIndex <> xlNone Then

Else
Cel.Interior.ColorIndex = 3
Litéralement : si la couleur est différente de xlNone alors
pn ne fait rien
sinon, on colorie Cel en rouge, etc...
Il faut éviter ce genre de formules. Elles complique la compréhension. Tu as 2 solutions possibles.
Soit inverser le résultat que tu veux :
If Cel.Interior.ColorIndex = xlNone Then
Mais ce n'est pas toujours possible, donc utiliser un inverseur logique
If Not(Cel.Interior.ColorIndex <> xlNone) Then
Bien que les parenthèses ne soient pas obligatoires, moi, je les mets systématiquement => tu ne fais par d'erreur de priorité d'un calcul sur l'autre, et ça délimite bien la portée du Not, même si ce n'est pas le créateur qui doit faire des modifs.

F_Inco.Cells(i,0) = Cel
Pas glop, pas glop !
Cells, je ne l'utilise que lorsque je fais évoluer le numéro des colonnes.
Range("BQ" & i) génère moins d'erreurs que Cells(i, 124) (pas sur que 124=BQ)
Ceci étant posé, tu veux copier la valeur de cel dans la cellule située à l'intersection de la ligne i et de la colonne 0 (Glups) ! À remplacer par
F_Inco.Range("A" & i) = Cel
ou
F_Inco.Cells(i,1) = Cel
-------------------------
Cel.Interior.ColorIndex = Cel_L.Interior.ColorIndex
Tu colories la cellule source de la couleur de la cellule destination ?
Pour tout les coloriages, j'évite le hazard. je préfére fixer la couleur, mais c'est chacun son système.

Norme allemand, ça devrait être plus moins pire

Quand le système fonctionnera, on pourra essayer d'améliorer la vitesse de la macro.
Déjà, je suis parti du principe que le nombre de cellule à tester et remplacer était inférieur à celui des cellules de la liste.

Une instruction Find est plus rapide, mais est plus complexe à comprendre et à mettre en oeuvre.
en gros la macro pourrait
Colorier les 2 colonnes C & E en rouge
pour chaque cellule de la liste
trouver les cellules de C & E qui lui sont égale et les colorier en xlNone
rechercher en C & E les cellules en rouge et les copier sur la feuille inconnu, si elles n'y sont pas déjà.

A+

[danydanydan]

J'ai l'impression d'avoir un cours particulier ! Merci infiniment d'avoir la patience de m'expliquer tout ça

Pour te situer le contexte je n'ai aucune connaissance en basic j'ai fais un peu de c, c++,java donc beaucoup de mauvaises habitudes viennent du fait que je connais un peu ces langages sans en maîtriser aucun...(ce n'est pas ma spécialité l'info je suis dans les réseaux...d'où les adresses ip!)

Le savoir ne prend pas de place, j’apprends avec plaisir !


Range=plage de cellules(1 ou plusieurs)
cell n'existe pas à ma connaissance.
Cells est une propriété de range ou sheet
Clng() convertit en nombre Long

Donc si tu veux selectionner une case (pour la copier etc ) tu utilise range en prenant seulement une case c'est ça ?
Cells sert à quoi ?


Dim i as Integer
i ici te sert à définir un numéro de ligne. Évidemment, tu ne penses pas avoir une ligne supérieure à 32767, mais le jour où ça arrive, on passe du temps à trouver l'erreur.
Depuis, je déclare mes variables "ligne" en Long


J'imagine tout à fait l'horreur !

If Not(Cel.Interior.ColorIndex <> xlNone) Then
Bien que les parenthèses ne soient pas obligatoires, moi, je les mets systématiquement => tu ne fais par d'erreur de priorité d'un calcul sur l'autre, et ça délimite bien la portée du Not, même si ce n'est pas le créateur qui doit faire des modifs.


Une syntaxe propre permet d'éviter les erreurs je prend note!


J’ai testé F_Inco.Cells(i,1) = Cel ça marche très bien !


Tu colories la cellule source de la couleur de la cellule destination ?
oui mon fichier source a un code couleur selon que c'est une ip concernant mon appli, a virer, qui n'a rien à voir avec mon appli etc

Visuellement ça permet d'aller plus vite !


Pour la rapidité de la macro ça me dérange pas qu'elle mette 30sec à s'exécuter par contre j’ai remarqué en faisant du pas à pas que la liste s’ouvrait et se fermait à chaque boucle est ce normal ?

[Gorfael]

Salut danydanydan et le for home

Pour te situer le contexte je n'ai aucune connaissance en basic j'ai fais un peu de c, c++,java donc beaucoup de mauvaises habitudes viennent du fait que je connais un peu ces langages sans en maîtriser aucun...(ce n'est pas ma spécialité l'info je suis dans les réseaux...d'où les adresses ip!)
Comme beaucoup, moi y compris, tu mets le s doigts dans Excel et il te bouffe en entier.
Vous avez du pot d'avoir internet, parce que quand j'ai commencé, j'ai galéré des semaines pour trouver un bouquin où avoir une idée de ce que je cherchais.
C'est pour ça que je suis bon en recherche de bug

Le savoir ne prend pas de place, j’apprends avec plaisir !
C'est ce que j'avais cru comprendre. C'est pour ça que j'explique ma démarche, au lieu de juste résoudre le problème.

Donc si tu veux selectionner une case (pour la copier etc ) tu utilise range en prenant seulement une case c'est ça ?
Cells sert à quoi ?
Pour faire référence à la cellule C8 tu as différentes possibilités :
Range("C8") : la plus simple et la plus connue. Quand tu mets un point derrière, Excel t'aide en te proposant la liste des propriétés, et c'est bien utile
[C8] : la plus courte, mais, tu n'as pas d'aide d'excel
cells(8,3) : aide d'Excel, mais c'est plus dur à interprêter

Pour une référence à la plage C8:E10.
Range("C8:E10")
Range(Range("C8'), Range("E10"))
Range([C8], [E10])
Range(cells(8,3), cells(10,5))

L'usage que tu en fais conditionne l'utlisation d'une forme plutôt que d'une autre.
je préfère lire Range("CZ" & X) (avec X en variable), que Cells(X, 104). C'est pas une question d'esthétique, seulement de recherche de bugs.
La colonne 104, il faut que je la calcule, avec les risques que ça peut représenter, alors que BZ, HD, DJ, je n'ai qu'à aller sur Excel vérifier que c'est la bonne colonne.
For Each Cel in Range("A" & Range("A65535").end(xlup) & ":B" & Range("B65536").end(xlup))
For Each Cel in Range(Range("A65535").end(xlup), Range("B65536").end(xlup))
For Each Cel in Range([A65535].end(xlup), [B65536].end(xlup))
3 fois la même instruction, mais la lisibilité n'est pas la même et encore, j'ai pas mis de "protection" si clonne B plus petite que colonne A, etc....
En gros, je construis avec la 2me formule pour avoir les aides, puis je réduis à la 3me formule

J'imagine tout à fait l'horreur !
Oui, le problème d'utiliser des variables "inadaptées", parce qu'elles suffisent dans la pluspart des cas est que l'erreur ne pas pas venir de là, puisque chez moi, sur mon fichier d'essai de 10 lignes, 10 colonnes, ça fonctionne sans erreur !!! Et l'utilisateur, il n'a qu'à employer 128 colonnes sur 32736 lignes et pas une de plus, na !

Une syntaxe propre permet d'éviter les erreurs je prend note!
Des fois, faut ruser, mais plus c'est clair, moins tu interprêtes. Et quand tu as fait la macro, il y a plus d'un an, et que ça urge, ça prend toute sa valeur.

J’ai testé F_Inco.Cells(i,1) = Cel ça marche très bien !
C'est plus mieux bien, hein !

Pour la rapidité de la macro ça me dérange pas qu'elle mette 30sec à s'exécuter par contre j’ai remarqué en faisant du pas à pas que la liste s’ouvrait et se fermait à chaque boucle est ce normal ?
C'est normal, mais on peut, et on devrait bloquer le rafraîchissement écran.
Problème : on ne bloque pas le rafraîchissement pour la macro, mais pour Excel. Donc, si tu ne passes pas sur l'instruction de remise en marche, tu ne le sauras pas forcément et tu risques d'y perdre quelques cheveux.

Bien que les super-programmeurs s'en passent, moi, dès que j'utilise une instruction affectant Excel, je fais une gestion d'erreur.
Sans gestion d'erreur, Excel les gèrent : il te met un message et arrête la macro au niveau où il a rencontré l'erreur.
Donc, suivant la loi de Murphy, après le blocage, et avant la remise en route du rafraîchissement écran

Donc dans la macro, je remplace
Sub Remplacement_IP()
par
Sub Remplacement_IP()
On error goto Err_Remplacement_IP
en cas d'erreur aller à l'adresse

Derrière les déclarations, au niveau de la MEI, j'intercale
Application.ScreenUpdating = False
bloquer le rafraîchissement écran

Toutes les instruction qui terminaient la macro
Exit Sub
sont remplacées par
Goto Exit_Remplacement_IP
aller à la sortie

et je remplace
End Sub

Par
Exit_Remplacement_IP:
Application.ScreenUpdating = True
Exit Sub
Err_Remplacement_IP:
MsgBox (Err.Number & " - " & Err.Description)
Resume Exit_Remplacement_IP
End Sub
Sortie unique et obligatoire : Exit_Remplacement_IP
ensuite on remet le rafraîchissement en route, et on sort
S'il y a une erreur fonctionnelle de la macro
Excel prend la main et envoie la macro à l'adreesse définie derrière on Error Goto
Là, je donnes les infos sur le numéro et la description de l'erreur
et on continue la macro en allant directement vers la sortie, en remettant au passage le rafraîchissement écran en route.

En gros, chaque fois que j'utilise une instruction qui commence par Application dans une macro, je gère les erreurs.
Je ne dirais pas ce que je pense de ceux qui mettent
Application.DisplayAlerts=False
sans gérer les erreurs, parce que le client risquent de demander pourquoi le programme produit des erreurs, les insultes sont proscrites sur les forums

Bon, fin de la petite séance
A+

[danydanydan]

que dire de plus que merci !
merci pour toutes ces explications grace à toi je pourrais peut être me faire une petite macro la prochaine fois plutôt que tout faire à la main !

merci encore Gorfael !