VBA problème avec la une boucle for range.end(xlup).row après suppression de lignes

**JohnNC115** · 21/01/2011, 11h46

Bonjour à tous
Débutant en VBA et nouveau sur ce forum, je sollicite votre aide

Dans le cadre d'un programme, je veux importer les valeurs de certaines cellules se trouvant sur différentes feuilles du classeur dans une même feuille.

Le souci se pose après la réinitialisation de cette feuille: avant de faire l'importation, je supprime toutes les lignes non vides et lors de mon test, le programme ne prend en compte que les 4 premières lignes.

Merci d'avance de votre aide

Cordialement

voici mon code:

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
Option explicit
'déclaration des variables
Dim i As Integer, j As Integer, k As Integer, nbf As Integer
Dim nomAPS As String, codeAPS As String
 
Private Sub CommandButton1_Click()
 
'initialisation de la fiche
For i = Range("A65536").End(xlUp).Row To 5 Step -1
    If Range("A" & i).Value <> "" Then
    Range("A" & i).EntireRow.Delete
    End If
Next i
 
'test et création de la fiche
nbf = ThisWorkbook.Sheets.Count
 
For i = nbf To 11 Step -1
    For j = ThisWorkbook.Sheets(i).Range("H65536").End(xlUp).Row To 4 Step -1
        nomAPS = ThisWorkbook.Sheets(i).Range("H" & j).Value
        codeAPS = ThisWorkbook.Sheets(i).Range("F" & j).Value
 
        For k = Range("A65536").End(xlUp).Row To 5 Step -1 '<-- le soucis vient de là !!! car il m'indique que k = 4 or je veux tester toutes les lignes
 
         If codeAPS <> Range("A" & k).Value And Range("A" & k - 1).Value <> "" Then
            Range("B" & k).Value = nomAPS
            Range("A" & k).Value = codeAPS
            End If
        Next k
    Next j
Next i
 
End Sub

**rvtoulon** · 21/01/2011, 12h06

Bonjour,
ici k = forcément 4 car avant tu effaces toutes les lignes non vides :

Code :

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

1
2
3
4
5
6
7
8
Private Sub CommandButton1_Click()
 
'initialisation de la fiche
For i = Range("A65536").End(xlUp).Row To 5 Step -1
If Range("A" & i).Value <> "" Then
Range("A" & i).EntireRow.Delete
End If
Next i

donc a la fin de cette boucle la seule cellule pleine de la colonne A est A4, ce qui fait que :

Code :

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

For k = Range("A65536").End(xlUp).Row To 5 Step -1 'le soucis vient de là !!! car il m'indique que k = 4 or je veux tester toutes les lignes

donc cette ligne de code donne a k le numero de la derniere cellule utilisée de la colonne A c'est a dire 4

**Qwazerty** · 21/01/2011, 12h51

Salut et bienvenu
A mon avis tu te mélanges les pinceaux entre tes différentes feuilles.
Tu devrais nommer explicitement sur quel classeur et feuille tu travailles

Dans ton code tu as des thisworkbook. et d'autre fois tu ne le précises pas, pareil pour sheets(x). Dans un première temps, même si ça charge ton code, défini comme il faut sur quelle feuille tu travailles a chaque fois, viendra ensuite la simplification de l'écriture.

Ps: lorsque tu postes du code, pense a utiliser la balise code (sélectionne ton code + bouton # dans le menu de l’éditeur)

++
Qwaz

**JohnNC115** · 21/01/2011, 13h01

Tout d'abord merci de vos réponses rapides (ça fait plaisir de ne pas se sentir abandonné ^^)

pourriez m'éclairer sur ce que renvoie la fonction range.end(xlup).row ?

@ rvtoulon
Merci pour ton explication, mais aurais tu une solution pour effacer toutes les données et mise en page de cellules avant d'importer les nouvelles données ?

@Qwazerty
Merci pour tes remarques.
Je vais essayer de m'organiser un peu mieux pour que cela devienne plus comprehensible :p
Pour ce qui est de la simplification, je ne suis pas sûr d'y arriver tout de suite, n'étant absolument pas spécialisé dans la programmation et n'étant que ponctuellement sur mon projet.

**Qwazerty** · 21/01/2011, 13h13

Alors le code

Code :

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

range("Dernière cellule de la colonne A").end(xlup).row

te renvoie le numéro de la dernière cellule non vide de ta colonne A (la colonne dépend bien sur de ce que tu mets dans range(...) )
Pour avoir ce genre d'information n’hésite pas a utiliser l'aide Excel, même si je trouve que la version 2007 est moins bien qu'avant, tu y trouveras des infos.

Pour ce qui est de simplifier le code, ça n'est pas un soucis, fait d'abords un code qui fonctionne, nous t'aiderons a le simplifier (enfin si tu sais simplifier certaines écritures fait le bien sur ^^)

Pour une aide plus efficace, un petit classeur exemple serait bien

++
Qwaz

**JohnNC115** · 21/01/2011, 13h28

Merci encore pour ta rapidité de réponse

je joins le classeur qui me pose tant de soucis

à l'ouverture il faut répondre non à la userform car elle appelle d'autres classeurs ^^

ensuite c'est le code de la feuille materiel qu'il faut que j'adapte

en fait ce que je veux faire c'est importer les noms et code des APS qui se trouve dans mes fiches de zones (feuilles à partir de la 11 inclue) en évitant les doublons et les cases vides

**Qwazerty** · 21/01/2011, 14h01

Alors pour etre sur de bien comprendre

Le but: Ajouter dans la feuille "Materiel" le contenu des feuilles "Zones"?
Les feuilles zones ne sont pas présentes dans ton fichier, sont elles dans un autre fichier?
Il ne faut pas de doublon et il faut se débarrasser des lignes ne contenant pas de code APS en colonne A.

Question: Comment sont structuré les feuilles "Zones".
Tu dis les feuille a partir de 11, 11 inclu, la feuille 11, c'est la feuille Accueil, les feuilles Zone sont elles structuré comme Accueil?
SI c'est le cas les seules lignes ayant une valeur de Code APS (feuille Accueil) ne contiennent aucune autre valeur, que faut il faire exactement

++
Qwaz

**JohnNC115** · 21/01/2011, 14h47

en fait, gros oubli de ma part, une feuille zone se nomme comme la zone quelle représente. Ici je n'en ai insérée qu'une (la feuille accueil).

Toutes les feuilles zone sont structurées de la même manière et les seules valeurs à tester sont celles de la colonne H (par rapport à celles de la colonne B de la feuille materiel)
et les valeurs à importer sont celles de la colonne H (dans la colonne B de la feuille matériel) et de la colonne F (dans la colonne A de la feuille materiel).

sachant que ce test doit permettre une importation sans doublon ni vide

j'ai réussi à modifier un peu le code pour qu'il me fasse l'importation mais je me retrouve avec des doublons :'(

Je suis désolé pour le discours très fouilli

**Qwazerty** · 21/01/2011, 15h11

Salut
Regarde la si ce code te convient

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
Private Sub CommandButton1_Click()
'Si tu n'utilises les variables que dans cette fonction, il faut les déclarer ici
Dim Ws_Materiel As Worksheet
Dim Ws_Accueil As Worksheet
Dim TheCell As Range
Dim i As Integer, j As Integer, k As Integer, nbf As Integer
Dim nomAPS As String, codeAPS As String
 
'Init des variable feuille
Set Ws_Materiel = ThisWorkbook.Sheets("Materiel")
Set Ws_Accueil = ThisWorkbook.Sheets("Accueil")
 
'initialisation de la fiche
'ici tu supprimes les lignes n'ayant pas de code APS
'Le code d'importantion qui va suivre ne laissera pas de case vide
'donc cette partie ne me semble pas utile, sauf si tu vas rajouter des lignes manuellement dans ton tableau ou supprimer des valeurs en colonne A
'For i = Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Row To 5 Step -1
'    If Ws_Materiel.Cells(i, "A").Value = "" Then 'ici c'est = et non pas <> (si j'ai bien compris ce que tu souhaites)
'        Ws_Materiel.Cells(i, "A").EntireRow.Delete
'    End If
'Next i
 
'Si je comprend bien tu veux prendre les infos contenues dans la feuille Accueil et les ajouter (sans doublon) dans la feuille Materiel
'On va donc passer en revu le contenu de la colonne H de la feuille Accueil
'Ici on rajoutera une boucle pour traiter les differentes feuilles
'For each TheSheet in ThisWorkbook.sheets
    'If On regarde si la feuille correspond a une zone, si oui
        'On parcour la colonne H
        For Each TheCell In Ws_Accueil.Range("F4", Ws_Accueil.Cells(Rows.Count, "F").End(xlUp))
            If TheCell.Value <> "" Then
                'La cellule contient une valeur, on la rajoute a la suite du tableau Materiel
                With Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    'Dans une boucle With si VBA rencontre un '.' sans rien devant, il rajoute le code qui suit le With en début de boucle
                    '(ici Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
                    .Value = TheCell
                    .Offset(0, 1).Value = TheCell.Offset(0, 1).Value
                End With
            End If
        Next
    'End If
'Next
 
'test et création de la fiche
'nbf = ThisWorkbook.Sheets.Count
 
'For i = nbf To 11 Step -1
'    For j = ThisWorkbook.Sheets(i).Range("H65536").End(xlUp).Row To 4 Step -1
'        nomAPS = ThisWorkbook.Sheets(i).Range("H" & j).Value
'        codeAPS = ThisWorkbook.Sheets(i).Range("F" & j).Value
'        For k = Range("A65536").End(xlUp).Row + 1 To 5 Step -1
'            If codeAPS <> Range("A" & k).Value And Range("A" & k - 1).Value <> "" Then
'            Range("B" & k).Value = nomAPS
'            Range("A" & k).Value = codeAPS
'            End If
'        Next k
'    Next j
'Next i
 
End Sub

Pour le moment seul Accueil est traité, il est facile de créer une boucle pour faire les autres Feuille, je l'ai initié (il manque la déclaration de TheSheet et son intégration dans le code interne de la boucle (en remplacement de Ws_Accueil), mais il faut savoir comment déterminer si la feuille est une feuille de Zone, j'ai bien compris qu'elle était a la suite a partir de la feuille 11, mais attention avec se genre de syntaxe, si une feuille est rajouté ou supprimé ou déplacé, les numéros de toutes les feuilles peuvent être chamboulée.
Je te conseil de réfléchir a un moyen de les repérer facilement, soit le contenu d'une cellule, soit le nom de la feuille ("Zone_1", "Zone_2",...) par exemple permet de tester le début du nom de la feuille.
Je serais de retour plus tard dans la soirée je pense, mais quelqu'un prendra peut être la suite entre temps

Bon courage a toi

++

Qwaz

**JohnNC115** · 24/01/2011, 09h30

Salut Qwazerty

je suis impressionné par ta rapidité de réponse et ta facilité à coder

je te remercie pour ton code mais je me suis aperçu qu'il me créait des doublons :'(

j'ai donc modifié mon code de départ avec l'aide d'un collègue (avoir un oeil extérieur ça aide ^^) et on y a ajouté un booléen

je pense que ce n'est pas le plus simple mais ça a l'avantage de fonctionner

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
Option Explicit
Dim i As Integer, j As Integer, k As Integer, nbf As Integer, nbl As Integer
Dim nomAPS As String, codeAPS As String
Dim unique As Boolean
 
Private Sub CommandButton1_Click()
 
'initialisation de la fiche
For i = Range("A65536").End(xlUp).Row To 5 Step -1
    If Range("A" & i).Value <> "" Then
    Range("A" & i).EntireRow.Delete
    End If
Next i
 
'test et création de la fiche
nbf = ThisWorkbook.Sheets.Count
 
'test pour chaque feuille du classeur à partir de la feuille 11
For i = nbf To 11 Step -1
 
    'test pour chaque cellule de la feuille testée
    For j = 4 To ThisWorkbook.Sheets(i).Range("H65536").End(xlUp).Row
        nomAPS = ThisWorkbook.Sheets(i).Range("H" & j).Value
        codeAPS = ThisWorkbook.Sheets(i).Range("F" & j).Value
        nbl = Range("A65536").End(xlUp).Row
 
        If nomAPS <> "" Then
 
        'intégration d'un booléen
        unique = True
 
            For k = 5 To nbl + 1
                If nomAPS = Range("B" & k).Value Then
                unique = False
                End If
            nbl = Range("B65536").End(xlUp).Row
            Next k
 
            If unique = True Then
            Range("B" & Range("A65536").End(xlUp).Row + 1).Value = nomAPS
            Range("A" & Range("A65536").End(xlUp).Row + 1).Value = codeAPS
            End If
        End If
    Next j
Next i
 
End Sub

je vais essayer de passer par ta méthode pour tenter de simplifier mon code

par contre je n'arrive pas à trouver d'aide pour utiliser le "with ... end with"
pourrais tu éclairer ma lanterne à ce sujet

merci d'avance

**Godzestla** · 24/01/2011, 09h53

Bonjour,

le With - End With te permet d'alléger ton code en évitant de répéter un préfixe pour un objet, une variable (Pour de l'aide toujours faire <F1> pour l'aide en ligne dans le VBA)

Donc le code de Qwazerty,

Code :

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

1
2
3
4
5
6
                With Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    'Dans une boucle With si VBA rencontre un '.' sans rien devant, il rajoute le code qui suit le With en début de boucle
                    '(ici Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
                    .Value = TheCell
                    .Offset(0, 1).Value = TheCell.Offset(0, 1).Value
                End With

sans with serait

Code :

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

1
2
3
4
5
6
                    'Dans une boucle With si VBA rencontre un '.' sans rien devant, il rajoute le code qui suit le With en début de boucle
                    '(ici Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
                    Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = TheCell
                    Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Offset(0, 1).Value = TheCell.Offset(0, 1).Value
                End With

J'ai mis en gras ce que le With t'évite. En fait le préfixe avant le point est placé juste derrière la clause with.

Principe

Code :

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

1
2
3
With machin
       .truc = True
end with

équivaut à

Code :

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

 machin.truc = true

j'espère que cela t'éclaire un peu.

**JohnNC115** · 24/01/2011, 11h12

un grand merci Godzestla

je galère depuis ce matin à essayer de trouver un minimum d'explication sur ce fameux "with ... end with" et tu me sauve ma matinée ^^

étant sur un PC du boulot, je n'arrive pas à accéder à l'aide en ligne (ils ont peut être bridé cet accès Oo )

je vais pouvoir simplifier toutes mes lignes de code grâce à ça (ça va surement devenir plus lisible et donc compréhensible, enfin !!!)

**Qwazerty** · 24/01/2011, 12h48

Salut
Avec verif doublon

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
Option Explicit
 
Private Sub CommandButton1_Click()
'Si tu n'utilises les variables que dans cette fonction, il faut les déclarer ici
Dim Ws_Materiel As Worksheet
Dim Ws_Accueil As Worksheet
Dim TheCell As Range
Dim RgDoublon As Range
Dim i As Integer, j As Integer, k As Integer, nbf As Integer
Dim nomAPS As String, codeAPS As String
 
'Init des variable feuille
Set Ws_Materiel = ThisWorkbook.Sheets("Materiel")
Set Ws_Accueil = ThisWorkbook.Sheets("Accueil")
 
'On va donc passer en revu le contenu de la colonne H de la feuille Accueil
'Ici on rajoutera une boucle pour traiter les differentes feuilles
'For each TheSheet in ThisWorkbook.sheets
    'If On regarde si la feuille correspond a une zone, si oui
        'On parcour la colonne H
        For Each TheCell In Ws_Accueil.Range("F4", Ws_Accueil.Cells(Rows.Count, "F").End(xlUp))
            If TheCell.Value <> "" Then
                'on verifie que la valeur n'existe pas deja
                With Ws_Materiel
                    Set RgDoublon = .Range("A5", .Cells(Rows.Count, "A").End(xlUp)).Find(TheCell.Value, , , xlWhole)
                End With
                If RgDoublon Is Nothing Then
                    'La cellule contient une valeur, on la rajoute a la suite du tableau Materiel
                    With Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                        'Dans une boucle With si VBA rencontre un '.' sans rien devant, il rajoute le code qui suit le With en début de boucle
                        '(ici Ws_Materiel.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))
                        .Value = TheCell
                        .Offset(0, 1).Value = TheCell.Offset(0, 1).Value
                    End With
                Else
                    Set RgDoublon = Nothing
                End If
            End If
        Next
    'end if
'next
End Sub

L'utilisation de find sera moins gourmand en ressource qu'une boucle.
++
Qwaz

**JohnNC115** · 24/01/2011, 14h24

A tient encore une fonction qui va pouvoir me servir à simplifier mes codes

merci Qwazerty

je suis admiratif de votre rapidité de réponse et de votre facilité à coder

**Godzestla** · 24/01/2011, 14h39

merci Qwazerty

je suis admiratif de votre rapidité de réponse et de votre facilité à coder

Mieux que Harry Potter, il y a Qwazy Coder.

**JohnNC115** · 24/01/2011, 15h45

je t'incluais également Godzestla

**Godzestla** · 24/01/2011, 16h10

Envoyé par JohnNC115

je t'incluais également Godzestla

Merci, mais tu te trompes. Qwaz est vraiment trés fort.

**Qwazerty** · 24/01/2011, 19h36

Salut
Heu... ben ... je suis pas sur d'en mériter tant...

D'un autre coté, vu qu'un compliment fait toujours plus plaisir qu'un coup de pied au c*l, je prend!

Mais bon on a tous nos maîtres et certain sur le forum se débrouillent mieux que moi. Il y a une logique dans toutes choses, disons que même si je critique M$ assez souvent, leur structure de logiciel et de langage me convient :p

Pour ce qui est de cette petite particule qui parait si noble, je parle du Magic', les amis qui me l'ont accrochés ne faisaient pas forcement référence a des miracles ^^.

En ce qui te concerne Godzestla, pas de fausse modestie

, tu mérites bien tes étoiles et le nouveau système de stat. ne dit pas le contraire non plus.

je suis admiratif de votre rapidité de réponse et de votre facilité à coder

Il serait facile de s'en glorifier, mais tu sais, savoir codé est utile c'est très vrai, mais savoir rechercher et encore plus important, surtout quand on débute. Bien souvent on pense que notre problème est tellement complexe et que la solution ne peut être déjà toute faite quelque part. Hors c'est bien souvent faux, même s'il faut adapter un peu, des gens ont souvent déjà bûché sur le problème et il est inutile de réinventer la roue.
Et pour ce qui est du code, t’inquiètes ça vient avec le temps.

++
Qwaz

**Godzestla** · 25/01/2011, 09h40

Réponse très intelligente Qwaz. J'approuve à 100 %.

ma petite rajoute perso :

Plus l'analyse du problème avant d'entamer le codage est approfondie et préventive, plus viable , stable, durable et qualitative sera la solution.

Il n'y a pas de miracle : quel que soit le domaine, il faut toujours mouiller son propre col de chemise pour atteindre la connaissance.

Bonne journée.

**JohnNC115** · 25/01/2011, 09h48

ah tient ça tombe bien que tu soit là Godzestla

petite question: sais tu comment afficher la position de la cellule qui contient une valeur testée par la fonction .find ?

Je m'explique: j'ai utiliser la méthode .find conseillée par Qwazerty pour tester la présence d'une valeur (d'une cellule se trouvant dans un classeur) dans une feuille se trouvant dans un autre classeur.
Par contre je ne sais pas comment récupérer la position de la cellule contenant cette valeur dans la feuille de l'autre classeur.

aurais tu une idée ^^"

VBA problème avec la une boucle for range.end(xlup).row après suppression de lignes [XL-2003]

Macros et VBA Excel

Discussions similaires

Partager

Partager