Attribution d'une plage de cellules à une variable

Version imprimable

Bonjour,

dans le cadre d'un projet professionnel, je réalise une macro qui vérifie les écarts entre deux fichiers (A et B).
Chaque ligne contient un identifiant unique qui est la combinaison de 2 colonnes.

En résumé, rien de bien sorcier :
- J'applique un filtre.
- Pour chaque ligne du fichier A, je compare les 2 colonnes (qui servent d'identifiant) avec 2 autres colonnes (aussi identifiant) du fichier B, pour chaque ligne aussi. Je procèdes avec des FOR EACH ligne IN plageA.Rows
- Si les 2 colonnes du fichier A ne sont pas trouvées dans le fichier B, les 2 colonnes du fichier A sont colorées en rouge.

Tout fonctionne très bien, sauf que la définition de mon Range pose problème, puisqu'il me prend la première ligne (celle d'en-tête avec les filtres).
Par conséquent, les en-tête des deux colonnes sont colorés en rouge, car inclus dans mes plages
Code:

1 2 3 Set plageA = Workbooks(fichier_A).Sheets(feuille_fichier_A).Range("_FilterDataBase").SpecialCells(xlCellTypeVisible) Set plageB = Workbooks(fichier_B).Sheets(feuille_fichier_B).Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
Pouvez-vous m'aider à attribuer à mes variables plageA et plageB uniquement les cellules filtrées et visibles, SANS la ligne d'en-tête ? S'il vous plait.
Ca peut paraitre tout simple mais je sèche ... malgré mes recherches (j'ai trouvé beaucoup de solutions, mais aucune ne me correspond).

Salut,

en redimensionnant et en décalant la plage

Code:

1
2
3
4
5
6
7
8
Sub test_forum()
    Dim sh As Worksheet
    Dim plageA As Range
    Set sh = Workbooks(fichier_A).Sheets(feuille_fichier_A)
    Set plageA = sh.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
    ' redimensionne la plage(une ligne en moins) et la décale une ligne vers le bas
    Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)
End Sub

11/04/2016, 13h16
kev59600

Merci pour ton aide Philippe ! ;)

J'avais déjà essayé, mais j'ai une erreur d'exécution :
Erreur d'exécution 1004
Erreur définie par l'application ou par l'objet

Causée par la ligne :

Code:

Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)

Grosse incompréhension :weird:
11/04/2016, 13h56
RyuAutodidacte

Bonjour,

je ne connaissais pas : Range("_FilterDataBase")
mais je viens de tester et ça marche, donc ma question est : as tu bien défini toutes tes variables ??
Du genre : Dim plageA as Range …, etc

Essaie de chercher la cause en testant chaque partie.
Par exemple:

Code:

1
2
3
4
5
6
7
8
 
Set plageA = plageA.Resize(plageA.Rows.Count - 1).Offset(1, 0)
plageA.Select
' puis
Debug.Print plageA.Rows.Count
' puis
 plageA.Resize(plageA.Rows.Count - 1).Select
' tu peux ainsi vérifier si les références sont bonnes

11/04/2016, 17h23
kev59600

Merci pour vos réponses !
Oui, j'ai bien défini toutes mes variables, dont les Range :)

La première sélection fonctionne à merveille, sans surprises, j'ai bien ma place sélectionné correctement (j'avais déjà vérifié de mon côté) :

Code:

plageA.Select

Le problème vient du fait que ma plage ne contient qu'une ligne. La procédure suivante me retourne 1 :

Code:

Debug.Print plageA.Rows.Count

D'où l'impossibilité de redimensionner, car on aurait donc 0 lignes en réduisant plageA d'1 ligne.
Cette ligne plante :

Code:

plageA.Resize(plageA.Rows.Count - 1).Select

Peut-être une histoire de lignes non-adjacentes ?
Mais alors pourquoi mon FOR EACH fonctionne ? :weird: C'est dingue !
11/04/2016, 18h57
RyuAutodidacte

Je pense que si tu fais le test avec :

Code:

Debug.print plageA.address

tu verras sûrement comme résultat une plage discontinue, et comme tu le dis l'en-tête est bien pris en compte.
Il est donc normal que ton résultat pour le count soit 1 pour la plage du filtre, enfin je pense (je ne pourrais faire les tests que ce soir)
Je n'ai pas vu ton code mais ton for Each doit passer par l'ensemble des lignes de ton filtre (c'est pourquoi cela fonctionne et que l en-tête est prise en compte)
À mon avis, il faudrait rajouter une condition dans ton for Each pour que la ligne 1 (celle de l'en-tête) soit omise dans la boucle
Test en rajoutant un If et fait moi savoir si c'est OK

J'ai trouvé le pourquoi du comment :D
Quand l'on actionne un filtre, il cache des lignes, pour montrer le résultat avec les lignes visibles restantes (désolé @Philippe PONS :aie: :no: ;) :chin:) après des recherches et des tests on ne peut pas utilisé le Resize et le Offset à sa guise comme une simple Range

Mais pourquoi ? Tout simplement, par ce que le filtre créé quasi tout le temps plusieurs plages discontinuent (j'ai enfin trouvé, ouuuufppphee) que l'on peut intercepter via ce code _FilterDataBase (je viens d'apprendre une nouvelle chose :D), un peu comme Union(Range1, Range2, …)
Si quelqu'un peut m'éclairer sur la différence _FilterDataBase vs Union(Range1, Range2, …), merci ;)

Du coup le Filtre créé différentes AIRES (vba : Areas) composées de plusieurs plages, dont l'en-tête est apparemment comprise dans l'écriture du code donné par le demandeur du post : @kev59600
voici les tests :
• 1 - Création d'un nouveau fichier Excel puis copie des données dans Feuil1 -> activer le filtre sur l'en-tête
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 En-tête 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
pour le test on va paramétrer le filtre : Inférieur à 5 + cocher ou + Supérieur 10

• 2 - on copie le code dans ThisWorkbook ou dans un module :
Code:

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 Sub test() Dim DerL As Long, RgA_Filtre As Range, RgA_E As Range DerL = Feuil1.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Rows.Count Set RgA_Filtre = Feuil1.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible) MsgBox "Nombre de lignes visibles : " & DerL & vbLf & "Nb lignes visibles sans en-tête : " & DerL - 1 '1er test MsgBox "Nombre de lignes visibles du filtre : " & RgA_Filtre.Rows.Count & vbLf & "Nb lignes visibles du filtre sans en-tête : " & RgA_Filtre.Rows.Count - 1 '2è test => Ecriture correct pour le filtre MsgBox "Ensemble des RANGE du filtre, en-tête comprise : " & vbLf & RgA_Filtre.Address MsgBox "Ensemble du nbres de lignes(1e RANGE du filtre)" & vbLf & "mais seulement de la 1e AIRE/RANGE : " & vbLf & RgA_Filtre.Rows.Count 'ce qu'il faut en déduire : le filtre créer une multi-plage, un peu à la façon d'Union, mais ce n'est pas Union(range1, range2,…) PS : si meilleurs explications, je prends :) 'le Count se fait sur la 1ère plage contigu du filtre (filtre = plusieurs plage) Set RgA_E = RgA_Filtre.Areas(1).Rows(1) MsgBox "Adresse de l'en-tête du filtre : " & RgA_E.Address For Each Areas In RgA_Filtre.Areas x& = x& + 1 If x = 1 Then MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address Else MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address End If Next Set RgA_Filtre = Nothing Set RgA_E = Nothing End Sub
Le résultat parle de lui même à mon avis.

Je pense que cela va aider pas mal de personne, enfin je l'espère, en tout cas pour ma part j'ai appris de nouvelles choses

Voilà ;)

12/04/2016, 09h04
kev59600

Je te remercie grandement pour ton aide et le temps accordé (une réponse aussi complète à 02h36, je n'en demandais pas tant) :D
Grâce à toi je comprends mieux la source de l'erreur !

En effet, si je fais un plageA.Address (que j'avais déjà réalisé puisque j'ai passé pas mal de temps à trouver la source d'erreur) j'ai bien des plages de cellules non adjacentes.

Je vais donc intégrer une condition qui permettra de ne pas considérer la ligne d'en-tête, comme tu me l'indiques.
J'avais aussi songé à cette solution, mais je voulais être fixé quant à la possibilité ou non de combiner Resize et Offset sur mes plages.
J'effectue le test et je reviens vous informer.
12/04/2016, 09h21
Philippe PONS

@RyuAutodidacte: pas de problème, je vais aussi apprendre qquechose, quand j'aurais le temps de lire le résultat de tes recherches!
@kev59600: désolé, j'avais, un peu, laissé tombé le fil après ma première intervention.
Bonne journée!
12/04/2016, 09h27
RyuAutodidacte

Bonjour

Citation:

Envoyé par kev59600

Je vais donc intégrer une condition qui permettra de ne pas considérer la ligne d'en-tête, comme tu me l'indiques.
J'avais aussi songé à cette solution, mais je voulais être fixé quant à la possibilité ou non de combiner Resize et Offset sur mes plages.

Comme tu peux le voir Resize et Offset peuvent s'utiliser avec Areas voir la boucle for Each dans l'exemple. ;)
Si je réponds, j'essaie d'assumer le plus possible, et ça me permet d'apprendre aussi, après je e suis pas infaillible, mais avec les tests,cela m'a paru d'une évidence, de plus tout le monde en profite ;)

N'étant pas à l'aise avec les Areas, j'ai opté pour un IF. Ce qui donne (après un petit tour de passe-passe pour récupérer le numéro de ligne) :
Code:

1 2 3 4 5 6 'Si la ligne parcourue est la ligne d'en-tête, on l'ignore If Mid(ligneBW.Address, InStrRev(ligneBW.Address, "$") + 1, Len(ligneBW.Address)) = ligne_entete_fichier_bw Then trouve = True Exit For End If
Encore merci ! :mrgreen:

Re,
pour éviter de tomber sur une erreur dans la boucle

Code:

1
2
3
4
5
6
7
8
    For Each Areas In RgA_Filtre.Areas
        x& = x& + 1
        If x = 1 And RgA_Filtre.Rows.Count <> 1 Then
            MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address
        Else
            MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address
        End If
    Next

bonsoir,
j'ai fais des petites rectifications de mon code pour que cela soit plus juste (Modif boucle):

Code:

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
Sub test()
    Dim DerL As Long, RgA_Filtre As Range, RgA_E As Range, x&
 
    DerL = Feuil3.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Rows.Count
    Set RgA_Filtre = Feuil3.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
 
    MsgBox "Nombre de lignes visibles : " & DerL & vbLf & "Nb lignes visibles sans en-tête : " & DerL - 1 '1er test
    MsgBox "Nombre de lignes visibles du filtre : " & RgA_Filtre.Rows.Count & vbLf & "Nb lignes visibles du filtre sans en-tête : " & RgA_Filtre.Rows.Count - 1 '2è test => Ecriture correct pour le filtre
 
    MsgBox "Ensemble des RANGE du filtre, en-tête comprise : " & vbLf & RgA_Filtre.Address
    MsgBox "Ensemble du nbres de lignes(1e RANGE du filtre)" & vbLf & "mais seulement de la 1e AIRE/RANGE : " & vbLf & RgA_Filtre.Rows.Count 'ce qu'il faut en déduire : le filtre créer une multi-plage, un peu à la façon d'Union, mais ce n'est pas Union(range1, range2,…) PS : si meilleurs explications, je prends :)
    'le Count se fait sur la 1ère plage contigu du filtre (filtre = plusieurs plage)
    Set RgA_E = RgA_Filtre.Areas(1).Rows(1)
    MsgBox "Adresse de l'en-tête du filtre : " & RgA_E.Address
 
    x = 1
    For Each Areas In RgA_Filtre
        If x = 1 And RgA_Filtre.Areas(x).Rows.Count > 1 Then 
'Si RgA_Filtre.Areas(x).Rows.Count = 1, cela veut dire qu'il n'y a que l'entête alors on passe au prochain Areas - Si RgA_Filtre.Areas(x).Rows.Count > 1 donc la Range est adjacent à l'en-tête, on se décale d'1 ligne et on Resize -1 ligne pour ne pas prendre l'en-tête
            MsgBox "1ere Aire/Range du filtre Sans l'en-tête : " & vbLf & RgA_Filtre.Areas(x).Resize(RgA_Filtre.Areas(x).Rows.Count - 1).Offset(1).Address
        Else
            x = x + 1
            MsgBox "Les autres adresses du filtre : " & vbLf & RgA_Filtre.Areas(x).Address
        End If
        If x = RgA_Filtre.Areas.Count Then Exit For ' Si le nombre de boucle est = au nombre d' Areas dans le filtre, on quitte la boucle
    Next
 
    Set RgA_Filtre = Nothing
    Set RgA_E = Nothing
End Sub