Boucles pour parcourir une colonne, une ligne, une plage de données - 2 méthodes
Question récurrente s'il en est : "Comment parcourir une colonne, une ligne, une plage de cellules ?"
Réponse certainement données dans la FAQ, je viens cependant proposer deux méthodes, celle utilisant la/les boucles For ... Next et celle utilisant la boucle For each ... Next
Exemple 1
Parcours simple des cellules d'une colonne donnée, ici la colonne 1, à l'aide d'une boucle For ... Next sur le numéro de ligne de la colonne.
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
| Sub For_X_to_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer
Dim NoLig As Long, DerLig As Long, Var As Variant
'Instance de la feuille qui permet d'utiliser FL1 partout dans
'le code à la place du nom de la feuille
Set FL1 = Worksheets("Feuil2")
'Détermine la dernière ligne renseignée de la feuille de calculs
'(Voir explication sur l'utilisation de Split en bas de cette discussion)
DerLig = Split(FL1.UsedRange.Address, "$")(4)
'Fixe le N° de la colonne à lire
NoCol = 1
'Utilisation du N° de ligne dans une boucle For ... Next
For NoLig = 1 To DerLig
Var = FL1.Cells(NoLig, NoCol)
'Pour tester : Affiche les variables dans la fenêtre Exécution de VBA
Debug.Print Var
Next
Set FL1 = NoThing
End Sub |
La même procédure est utilisable pour lire les données d'une colonne ligne par ligne, ou pour lire les donnée d'une ligne colonne par colonne
Exemple 1 (bis) (code seul simplifié)
Parcours simple des cellules d'une Colonne déterminée, ligne après ligne
Code:
1
2
3
4
5
6
7
8
9
10
| Sub For_X_to_Next_Ligne()
Dim FL1 As Worksheet, NoCol As Integer
Dim NoLig As Long, Var As Variant
Set FL1 = Worksheets("Feuil2")
NoCol = 1 'lecture de la colonne 1
For NoLig = 1 To Split(FL1.UsedRange.Address, "$")(4)
Var = FL1.Cells(NoLig, NoCol)
Next
Set FL1 = NoThing
End Sub |
Exemple 2
Parcours simple des cellules d'une Ligne déterminée, colonne après colonne
Code:
1
2
3
4
5
6
7
8
9
10
11
| Sub For_X_to_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer
Dim NoLig As Long, Var As Variant
Set FL1 = Worksheets("Feuil2")
NoLig = 5 'Lecture de la ligne 5
'(Voir explication sur l'utilisation de Split en bas de cette discussion)
For NoCol = 1 To Columns(Split(FL1.UsedRange.Address, "$")(3)).Column
Var = FL1.Cells(NoLig, NoCol)
Next
Set FL1 = NoThing
End Sub |
Exemple 3
Seconde méthode utilisable : La foucle For each ... Next
L'exemple ci-dessous lit chaque cellule d'une seule colonne
NoCol1 = 1 ; NoCol2 = 1
On constate immédiatement que le code suivant est plus lourd que les précédents (Exemple 1 et 2)
S'il est donné malgré tout c'est afin de conduire aux exemples suivants en abordant la notion de plage de données
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| Sub For_Each_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol1 As Integer, NoCol2 As Long
Dim DerLig As Long, Plage As Range
'Les données récupérées
Dim Var1, Var2, Var3, adres As String, NoLig As Long, NoCol As Integer
'Instance de la feuille : Permet d'utiliser FL1 partout dans ...
'... le code à la place de Worksheets("Feuil2")
Set FL1 = Worksheets("Feuil2")
'Fixe le N° de première colonne de la plage à lire
NoCol1 = 1
'Fixe le N° de la dernière colonne de la plage à lire
NoCol2 = 1
'Détermine la dernière ligne renseignée de la feuille de calculs
DerLig = Split(FL1.UsedRange.Address, "$")(4)
'où FL1.Range(FL1.Cells(1, NoCol1), FL1.Cells(Derlig, NoCol2)) détermine
'la plage de cellules à lire
With FL1
Set Plage = .Range(FL1.Cells(1, NoCol1), FL1.Cells(DerLig, NoCol2))
'Utilisation de l'objet range (Cell) dans une boucle For Each... Next
For Each Cell In Plage
'*** Récupération des valeurs de plusieurs cellule ***
'Valeur de la cellule lue
Var1 = Cell.Value
'Valeur de la cellule de la même ligne, colonne NoCol + 1
Var2 = Cell.Offset(0, 1)
'Valeur de la cellule de la même ligne, colonne NoCol + 2
Var3 = Cell.Offset(0, 2)
'*** Récupération de l'adresse de la cellule lue ***
'Adresse complète
adres = Cell.Address
'Numéro de ligne
NoLig = Cell.Row
'Numéro de colonne
NoCol = Cell.Column
'Pour tester : Affiche les variables dans la fenêtre Exécution de VBA
Debug.Print adres & " " & NoLig & " " & NoCol & " "
Debug.Print Var1 & " " & Var2 & " " & Var3
Next
End With
Set FL1 = NoThing
Set Plage = NoThing
End Sub |
Exemple 4
La même méthode permet de parcourir chaque cellule d'une plage de données.
L'exemple ci-après lit les cellules d'une plage de données prè-déterminée
Exemple :
Adresse de la plage : "B3:E15"
Les données seront lues colonne après colonne pour une ligne, puis ligne après ligne :
Ligne1, Colonne1 ; Ligne1, Colonne2 ; Ligne1, Colonne3 ; ... puis
Ligne2, Colonne1 ; Ligne2, Colonne2 ; Ligne2, Colonne3 ; ...
etc.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| Sub For_Each_Next_Plage()
Dim FL1 As Worksheet, Cell As Range, Plage As Range
Dim Var1
Set FL1 = Worksheets("Feuil2")
With FL1
'Détermination de la plage de cellules à lire
'Peut s'écrire en utilisant l'objet range de la plage
'For Each Cell In .Range("B3:E15")
'ou en utilisant l'objet Plage (range) de la plage
Set Plage = .Range("B3:E15")
For Each Cell In Plage
'Valeur de la cellule lue
Var1 = Cell.Value
Next
End With
Set FL1 = NoThing
Set Plage = NoThing
End Sub |
Exemple 5
L'exemple suivant, utilise la propriété UsedRange pour lire toutes les cellules de la plage de données d'une feuille de calculs
Ici aussi, les données sont lues colonne après colonne pour une ligne, puis ligne après ligne :
Code:
1
2
3
4
5
6
7
8
9
10
11
| Sub For_Each_Next_UsedRange()
Dim FL1 As Worksheet, Cell As Range
Set FL1 = Worksheets("Feuil2")
With FL1
'Utilisation de la propriété UsedRange dans une boucle For Each... Next
For Each Cell In .UsedRange
Var1 = Cell.Value
Next
End With
Set FL1 = NoThing
End Sub |
Exemple 6
Cet exemple réalise la même opération en utilisant deux boucles imbriquées
pour une lecture colonne après colonne pour une ligne, puis ligne après ligne.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| Sub For_Next_Plage()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer, NoLig As Long
Dim DerLig As Long, DerCol As Integer, Var As Variant
Set FL1 = Worksheets("Feuil2")
'Détermine la dernière ligne renseignée de la feuille de calculs
DerLig = Split(FL1.UsedRange.Address, "$")(4)
'Détermine la dernière colonne renseignée de la feuille de calculs
DerCol = Columns(Split(FL1.UsedRange.Address, "$")(3)).Column
For NoLig = 1 To DerLig
For NoCol = 1 To DerCol
Var = FL1.Cells(NoLig, NoCol)
Next
Next
End Sub |
Split
Une explication sur l'utilisation de Split(FL1.UsedRange.Address, "$")(4) :
Worksheets("Feuil1").UsedRange.address donne l'adresse de la plage de données sous la forme "A1:" & DernièreColonne & dernièreLigne"
Par exemple "$A$1:$H$75"
En réalité on utilisera Split(Worksheets("Feuil1").UsedRange.address, "$") ou Split(FL1.UsedRange.address, "$")
En utilisant "$" comme séparateur de données, Split("$A$1:$H$75", "$") crée un tableau de taille 5 (0 à 4) de cette adresse
Exemple :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
| Sub Ex1()
Dim adres As Variant, DerCol As Variant, DerLig As Long
adres = Split("$A$1:$H$75", "$")
adres(0) = ""
adres(1) = "A"
adres(2) = "1"
adres(3) = "H"
adres(4) = "75"
'donne
MsgBox Split("$A$1:$H$75", "$")(4) 'Dernière ligne = 75
MsgBox Columns(Split("$A$1:$H$75", "$")(3)).Column 'Dernière colonne (H) que Columns convertit en numéro (8)
End Sub |
De même, en utilisant ":" comme séparateur, on obtient un tableau de taille 2 (0 à 1) contenant les adresses de début et de fin de plage
Exemple :
Code:
1
2
3
4
5
6
7
| Sub Ex2()
Dim adres As Variant, Ad1 As String, Ad2 As String
adres = Split("$A$12:$F$75", ":")
'donne
MsgBox Split("$A$12:$F$75", ":")(0) '= $A$12
MsgBox Split("$A$12:$F$75", ":")(1) '= $F$75
End Sub |
(A compléter)
