Définir une plage de feuille VBA
Résolu/Fermé
A voir également:
- Définir une plage de feuille VBA
- Frédéric cherche à faire le buzz sur les réseaux sociaux. il a ajouté une image de manchots sur une image de plage. retrouvez l'image originale de la plage. que cachent les manchots ? ✓ - Forum Windows
- Erreur 1004 vba ✓ - Forum VB / VBA
- Vba selection plage variable ✓ - Forum VB / VBA
- Supprimer une feuille word - Guide
- L'indice n'appartient pas à la sélection vba - Forum VB / VBA
9 réponses
michel_m
Messages postés
16593
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 mars 2023
3 292
Modifié par michel_m le 5/09/2012 à 07:46
Modifié par michel_m le 5/09/2012 à 07:46
Bonjour
juste après l'aube ou presque :o)
proposition:
durée avec proc 2ghz ram 512: >=11 secondes
macro dans module1 et non module feuille (erreur dangereuse)
Michel
juste après l'aube ou presque :o)
proposition:
durée avec proc 2ghz ram 512: >=11 secondes
macro dans module1 et non module feuille (erreur dangereuse)
Option Explicit
Option Base 1
Const Dercol As Integer = 8 'n° de la dernière colonne utilisée
Sub alter_reference()
Dim Derlig1 As Long, Derlig2 As Long
Dim Cptr As Integer, T1_colb(), T2_colb
Dim Dico1 As Object, Dico2 As Object
Dim T_out(), Nbre As Long, Col As Byte
Dim Start As Single, Duree As Single 'pour essai rapidité
Start = Timer
'fige le défilement de l'écran
Application.ScreenUpdating = False
'ipréparations feuil1
With Sheets("feuil1")
Derlig1 = .Cells(.Rows.Count, 2).End(xlUp).Row
'passage en ram tableau feuille1
T1_colb = .Range(.Cells(1, 1), .Cells(Derlig1, Dercol)).Value
'création du dictionnary feuille1 col b
Set Dico1 = CreateObject("scripting.dictionary")
For Cptr = 1 To UBound(T1_colb)
If Not Dico1.exists(T1_colb(Cptr, 2)) Then 'élimination des éventuels doublons
Dico1.Add T1_colb(Cptr, 2), ""
End If
Next
End With
With Sheets("feuil2")
'initialisations et préparations feuil2
Derlig2 = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(Derlig2, Dercol)).Interior.ColorIndex = xlNone
'passage en ram tableau feuil2
T2_colb = .Range(.Cells(1, 1), .Cells(Derlig2, Dercol)).Value
'création du dictionnary feuille1 col b
Set Dico2 = CreateObject("scripting.dictionary")
For Cptr = 1 To UBound(T2_colb)
If Not Dico2.exists(T2_colb(Cptr, 2)) Then 'élimination des éventuels doublons
Dico2.Add T2_colb(Cptr, 2), ""
End If
Next
'détecte les éléments de feuil2 manquant en feuil1 _
et les colorise en jaune
For Cptr = 1 To UBound(T2_colb)
If Not Dico1.exists(T2_colb(Cptr, 2)) Then
.Range(.Cells(Cptr, 1), .Cells(Cptr, Dercol)).Interior.ColorIndex = 6
End If
Next
'mémorise les éléments de feuil1 manquant en feuil2 _
et les retranscrit dans la feuille 2 et les colorise en rouge
Nbre = 1
ReDim T_out(Dercol, Nbre)
'mémorisation
For Cptr = 1 To UBound(T1_colb)
If Not Dico2.exists(T1_colb(Cptr, 2)) Then
For Col = 1 To Dercol
T_out(Col, Nbre) = T1_colb(Cptr, Col)
Next
Nbre = Nbre + 1
ReDim Preserve T_out(Dercol, Nbre)
End If
Next
ReDim Preserve T_out(Dercol, Nbre - 1)
'restitution
With .Cells(Derlig2 + 1, 1).Resize(Nbre - 1, Dercol)
.Value = Application.Transpose(T_out)
.Interior.ColorIndex = 3
End With
.Activate
End With
'pour essai
Duree = Timer - Start
Application.ScreenUpdating = False
MsgBox ("durée: " & Duree & " secondes")
End Sub
Michel
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
4 sept. 2012 à 00:20
4 sept. 2012 à 00:20
Bonjour,
Je propose la traduction suivante :
A+
Je propose la traduction suivante :
Sub Reference()
Dim PlageFE_1 As Range
Dim PlageFE_2 As Range
Dim CelFE_1 As Range
Dim CelFE_2 As Range
Dim DerCel As Long
'défini les plages en colonne B de Feuil1 et Feuil2
With Worksheets("Feuil1")
Set PlageFE_1 = .Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
With Worksheets("Feuil2")
Set PlageFE_2 = .Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp))
'mémorise la dernière ligne occupée
DerCel = .Cells(.Rows.Count, 2).End(xlUp).Row
End With
'boucle sur la plage en Feuil1 et recherche la valeur en Feuil2
'si pas trouvée, rajoute la ligne à Feuil2 puis colore (cellules non vides) en rouge
For Each CelFE_1 In PlageFE_1
Set CelFE_2 = PlageFE_2.Find(CelFE_1, , xlValues, xlWhole)
If CelFE_2 Is Nothing Then
DerCel = DerCel + 1
With Worksheets("Feuil2")
CelFE_1.EntireRow.Copy .Range("A" & DerCel)
.Range(.Cells(DerCel, 2), .Cells(DerCel, .Columns.Count).End(xlToLeft)).Interior.ColorIndex = 3
End With
End If
Next CelFE_1
'boucle sur la plage en Feuil2 et recherche la valeur en Feuil1
'si pas trouvée, colore la ligne (cellules non vides) en jaune
For Each CelFE_2 In PlageFE_2
Set CelFE_1 = PlageFE_1.Find(CelFE_2, , xlValues, xlWhole)
If CelFE_1 Is Nothing Then
With Worksheets("Feuil2")
.Range(.Cells(CelFE_2.Row, 2), .Cells(CelFE_2.Row, .Columns.Count).End(xlToLeft)).Interior.ColorIndex = 6
End With
End If
Next CelFE_2
End Sub
A+
f894009
Messages postés
16905
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
20 mars 2023
1 680
3 sept. 2012 à 18:48
3 sept. 2012 à 18:48
Bonjour,
Cells(1,1)==>A1
Cells(1,2)==>B1
Cells(1,1)==>A1
Cells(1,2)==>B1
En effet c'est ça, sauf que je n'arrive pas définir la plage des cellules de la colonne B à la place de A.
Merci d'avance.
Merci d'avance.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Merci pilas31, c'est la bonne.
Maintenant je m'attaque à un autre problème sur le même fichier, c'est que quant je lance la macro dans un tableau excel de plus de 3000 lignes, la boucle se lance ais c'est sans fin, à chaque fois je suis obliger d'arrêter la macro.
Avez vous une solution à ça?
Merci
Maintenant je m'attaque à un autre problème sur le même fichier, c'est que quant je lance la macro dans un tableau excel de plus de 3000 lignes, la boucle se lance ais c'est sans fin, à chaque fois je suis obliger d'arrêter la macro.
Avez vous une solution à ça?
Merci
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
4 sept. 2012 à 10:58
4 sept. 2012 à 10:58
Bonjour,
C'est curieux, il n'y a que des boucles for each dans une plage de cellules qui est forcément bornée. Donc celà ne me semble pas pouvoir boucler.
J'ai fait le test avec 3600 lignes et ça fonctionne chez moi.
N'y a t-il que cette macro qui se lance ?
C'est curieux, il n'y a que des boucles for each dans une plage de cellules qui est forcément bornée. Donc celà ne me semble pas pouvoir boucler.
J'ai fait le test avec 3600 lignes et ça fonctionne chez moi.
N'y a t-il que cette macro qui se lance ?
Oui il y a qu'une seule macro qui est lancée.
Avec une nouvelle tentative, cette dernière à ramé plus de 15 min avant que je l'arrête de nouveau
Avec une nouvelle tentative, cette dernière à ramé plus de 15 min avant que je l'arrête de nouveau
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
Modifié par pilas31 le 4/09/2012 à 11:34
Modifié par pilas31 le 4/09/2012 à 11:34
Le mieux serait de déposer le fichier (sans données confidentielles) sur www.cjoint.com en postant le lien de téléchargement.
Nous pourrons alors éssayer de comprendre pourquoi ça boucle.
Nous pourrons alors éssayer de comprendre pourquoi ça boucle.
michel_m
Messages postés
16593
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 mars 2023
3 292
4 sept. 2012 à 13:38
4 sept. 2012 à 13:38
Bonjour,
Pilas, excuse l'inscruste (perso: beau temps à villeneuve Today)
Avec 30000 et 3000 lignes sans écran figé et en recopiant des cellules, il n'est pas étonnant que tu mettes un temps fou !...
Il faudrait passer par des objets dictionary et des variables tableaux pour avoir une durée raisonnable
petite question car tu as joint un classeur modèle que je n'ai pas pu ouvrir: quel est le nombre de colonnes maxi ?
je pars vers 14,30h et reviendrais en fin d'aprèm
Pilas, excuse l'inscruste (perso: beau temps à villeneuve Today)
Avec 30000 et 3000 lignes sans écran figé et en recopiant des cellules, il n'est pas étonnant que tu mettes un temps fou !...
Il faudrait passer par des objets dictionary et des variables tableaux pour avoir une durée raisonnable
petite question car tu as joint un classeur modèle que je n'ai pas pu ouvrir: quel est le nombre de colonnes maxi ?
je pars vers 14,30h et reviendrais en fin d'aprèm
michel_m
Messages postés
16593
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
23 mars 2023
3 292
4 sept. 2012 à 14:01
4 sept. 2012 à 14:01
??? pourquoi colonne A alors que tu commence tes codes en colonne B ?
essaies de joindre un classeur avec environ 5000 lignes maxi et en xlsm et non en xltm
Merci
A tout à l'heure ou demain....
essaies de joindre un classeur avec environ 5000 lignes maxi et en xlsm et non en xltm
Merci
A tout à l'heure ou demain....
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
4 sept. 2012 à 14:56
4 sept. 2012 à 14:56
Bonjour Michel,
Tu es le bien venu bien sur....(beau temps ici aussi à Toulouse...)
@Baajo.
Michel à raison (comme toujours).... Ce n'est pas un problème de boucle sans fin c'est un problème de performance.
30 000 lignes dans la feuille 1 et 3 000 lignes dans la feuille 2.
Donc la macro fait 30 000 fois la recherche dans la plage de 3 000
Au passage elle fait un copier de 27 000 lignes (la différence) et change la couleur
Puis elle fait 3 000 fois une recherche dans la plage de 30 000 lignes
Donc il faut optimiser ce code si possible....Et là il faut de l'imagination... Je vais essayer d'y réfléchir mais je fais confiance à Michel....
A+
Tu es le bien venu bien sur....(beau temps ici aussi à Toulouse...)
@Baajo.
Michel à raison (comme toujours).... Ce n'est pas un problème de boucle sans fin c'est un problème de performance.
30 000 lignes dans la feuille 1 et 3 000 lignes dans la feuille 2.
Donc la macro fait 30 000 fois la recherche dans la plage de 3 000
Au passage elle fait un copier de 27 000 lignes (la différence) et change la couleur
Puis elle fait 3 000 fois une recherche dans la plage de 30 000 lignes
Donc il faut optimiser ce code si possible....Et là il faut de l'imagination... Je vais essayer d'y réfléchir mais je fais confiance à Michel....
A+
Merci à vos deux.
J'ai pensé à cette macro afin de mettre à jour un classeur excel.
1 à 2 fois par semaine je reçois un .csv avec les différentes référence (en générale entre 25000 et 33000 lignes), et comme je doit vérifier les données de chaque ligne et rajouter d'autres cellules (a partir d'un catalogue papier), j'essaye de conserver les données modifier et d'écraser les ligne qui n'existent plus.
De cette façon je peux modifier que les nouveaux éléments.
Donc, si vous avez une autre alternative je suis preneur.
Merci.
A+
J'ai pensé à cette macro afin de mettre à jour un classeur excel.
1 à 2 fois par semaine je reçois un .csv avec les différentes référence (en générale entre 25000 et 33000 lignes), et comme je doit vérifier les données de chaque ligne et rajouter d'autres cellules (a partir d'un catalogue papier), j'essaye de conserver les données modifier et d'écraser les ligne qui n'existent plus.
De cette façon je peux modifier que les nouveaux éléments.
Donc, si vous avez une autre alternative je suis preneur.
Merci.
A+
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
Modifié par pilas31 le 4/09/2012 à 17:26
Modifié par pilas31 le 4/09/2012 à 17:26
Re
J'ai pensé à une autre méthode que je vous décris rapidement :
1/ Sur chaque ligne de la feuille 1 je mets 1 en colonne 10 et le n° de ligne en colonne 11. Idem sur la feuille 2 avec 2 en colonne 10 et le n° de ligne en colonne 11.
2/Je copie les lignes de la feuille 1 et les lignes de la feuille 2 à la suite dans la feuille3
3/ Je trie la feuille 3 selon le code (colonne B)
4/ je supprime les doublons car cela signifie qu'ils sont dans les deux feuilles
5/ Ceux qui restent si ils ont 1 en colonne 10 c'est qu'ils sont dans la feuille1 et pas dans la feuille 2. Si ils ont 2 c'est le contraire.
Voila le code. J'ai testé et il traite le fichier en 4 minutes environ chez moi. Je pense que ce n'est pas trop mal :
A tester....
Cordialement,
J'ai pensé à une autre méthode que je vous décris rapidement :
1/ Sur chaque ligne de la feuille 1 je mets 1 en colonne 10 et le n° de ligne en colonne 11. Idem sur la feuille 2 avec 2 en colonne 10 et le n° de ligne en colonne 11.
2/Je copie les lignes de la feuille 1 et les lignes de la feuille 2 à la suite dans la feuille3
3/ Je trie la feuille 3 selon le code (colonne B)
4/ je supprime les doublons car cela signifie qu'ils sont dans les deux feuilles
5/ Ceux qui restent si ils ont 1 en colonne 10 c'est qu'ils sont dans la feuille1 et pas dans la feuille 2. Si ils ont 2 c'est le contraire.
Voila le code. J'ai testé et il traite le fichier en 4 minutes environ chez moi. Je pense que ce n'est pas trop mal :
Sub AUTRE()
Dercel1 = Feuil1.Cells(Rows.Count, 2).End(xlUp).Row
DerCel2 = Feuil2.Cells(Rows.Count, 2).End(xlUp).Row
For Ligne1 = 2 To Dercel1
Feuil1.Cells(Ligne1, 10) = 1
Feuil1.Cells(Ligne1, 11) = Ligne1
Next Ligne1
For Ligne2 = 2 To DerCel2
Feuil2.Cells(Ligne2, 10) = 2
Feuil2.Cells(Ligne2, 11) = Ligne2
Next Ligne2
Sheets("Feuil1").Select
Rows(2 & ":" & Dercel1).Select
Selection.Copy
Sheets("Feuil3").Select
Rows("1:1").Select
ActiveSheet.Paste
Sheets("Feuil2").Select
Rows(2 & ":" & DerCel2).Select
Selection.Copy
Sheets("Feuil3").Select
Rows(Dercel1).Select
ActiveSheet.Paste
' on trie la feuille3
With ActiveWorkbook.Worksheets("Feuil3").Sort
.SetRange Range("A1:K" & Dercel1 + DerCel2 - 2)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' on enlève les doublons
For Ligne = Dercel1 + DerCel2 - 2 To 2 Step -1
If Cells(Ligne, 2) = Cells(Ligne - 1, 2) Then
Rows(Ligne & ":" & Ligne - 1).Select
Selection.Delete Shift:=xlUp
End If
Next Ligne
' toutes les lignes avec 1 en colonne 10 sont celles présentes en feuil1 et pas en feuil2
' Toutes celles avec 2 c'est le contraire
Derligne = Feuil3.Cells(Rows.Count, 2).End(xlUp).Row
For Ligne = 1 To Derligne
If Cells(Ligne, 10) = 1 Then
DerCel2 = DerCel2 + 1
Ligne1 = Cells(Ligne, 11)
With Worksheets("Feuil2")
Feuil1.Cells(Ligne1, 1).EntireRow.Copy .Range("A" & DerCel2)
.Range(.Cells(DerCel2, 2), .Cells(DerCel2, .Columns.Count).End(xlToLeft)).Interior.ColorIndex = 3
End With
ElseIf Cells(Ligne, 10) = 2 Then
Ligne2 = Cells(Ligne, 11)
With Worksheets("Feuil2")
.Range(.Cells(Ligne2, 2), .Cells(Ligne2, .Columns.Count).End(xlToLeft)).Interior.ColorIndex = 6
End With
End If
Next Ligne
End Sub
A tester....
Cordialement,
pilas31
Messages postés
1823
Date d'inscription
vendredi 5 septembre 2008
Statut
Contributeur
Dernière intervention
24 avril 2020
636
Modifié par pilas31 le 4/09/2012 à 18:12
Modifié par pilas31 le 4/09/2012 à 18:12
Non la macro fait tout toute seule.
Au fait il vaut mieux mettre la macro dans un module de excel VBA car dans une macro de feuille cela risque quelquefois de poser des problèmes lors des selections.
(Dans l'éditeur VBA faire Insertion/Module)
Il est lancé et ça tourne !
Une question quant au doublons que la macro détecte, est ce qu'elle supprime ceux qui se trouve en 1er et haut de la feuille (soit ceux de la Feuil1) ou ceux détecter en 2 ème.
Car les références modifier et que je souhaite conserver en cas de doublon sont ceux de la Feuil2.
C'est ça?
Une question quant au doublons que la macro détecte, est ce qu'elle supprime ceux qui se trouve en 1er et haut de la feuille (soit ceux de la Feuil1) ou ceux détecter en 2 ème.
Car les références modifier et que je souhaite conserver en cas de doublon sont ceux de la Feuil2.
C'est ça?
Voilà voilà,
J'ai exécuté la macro, résultat:
Exécution rapide 2min30s.
Les élément en double sont présent sur la Feuil2 et Feuil3.
La Feuil1 à conservée le nombre de lignes du départ soit 31074.
La Feuil2 est passée de 3000 lignes à 34154 (sans supprimer les doublon elle a additionnée les lignes)
Et quant je relance la macro une seconde fois, les Feuil2 et 3 passent à 65000 lignes.
J'ai exécuté la macro, résultat:
Exécution rapide 2min30s.
Les élément en double sont présent sur la Feuil2 et Feuil3.
La Feuil1 à conservée le nombre de lignes du départ soit 31074.
La Feuil2 est passée de 3000 lignes à 34154 (sans supprimer les doublon elle a additionnée les lignes)
Et quant je relance la macro une seconde fois, les Feuil2 et 3 passent à 65000 lignes.
5 sept. 2012 à 09:24
dans les déclarations, mettre Cptr en type Long et non Integer....
excusez moi
5 sept. 2012 à 10:47
C'est remarquable ! ça fonctionne chez moi en à peine 3 secondes. (intel core i5 - 2,5 GHz - RAM 4 GB )
Bien sur tu as raison la solution était de travailler en mémoire...
Chapeau !
5 sept. 2012 à 12:18
Merci Michel mille fois!
Après une matinée de prise de tête sans internet, c'est la bonne nouvelle.
Oui c'est vraiment la solution pour mon problème.
Je vais lancer la macro sur mon ordinateur cet apm et je vous tiendrais au courant du temps que ça prend dès mon retour.
5 sept. 2012 à 16:20
Merci encore car je vais enfin faire mon travail pendant les heures du bureau, enfin.
6 sept. 2012 à 18:30
Toujours pour le même classeur j'ai besoin de vos conseils SVP.
Pour la Feuil2, je souhaite mettre en place une macro dans la Col "C" qui me supprime automatiquement les lignes de la cellule qui contiennent "Fedex", afin de conserver que les autres lignes de avec les autres modes de transport. Pensez-vous que la meilleure solution sera de créer une macro supplémentaire?
Quant à la macro de base, finalement je vais opter pour la suppression des lignes qui ne sont plus détectées dans la Feuil1 au lieu de les mettre en couleur jaune, car ça me prends énormément de temps.
Merci d'avance pour vos réponse.
Bonne soirée.