Discussion :

[Rionma]

Bonjour à tous,

Je travaille sur un fichier excel dans lequel il y a des données que je modifie régulièrement et j’essaye au maximum d’automatiser toutes mes actions sur ce fichier grâce au langage VBA (pour éviter de tout reprendre à la main dès que je modifie quelques valeurs).
Dans une des macros que je souhaite faire, je veux faire 2 boucles for : une qui me permette de remettre en forme une feuille excel dans une autre feuille (cette boucle, c’est ok !) et à la suite, je veux en faire une qui me permette de joindre des colonnes entre elles dans une seule colonne. Je sèche sur cette partie ! :/

De façon très simplifiée, j’ai un fichier dans le même style que le tableau ci-dessous :

A	B	C
a1	b1
a2	b2
...	...
a1205	b1205

Et je voudrais obtenir :

A	B	C
a1	b1	a1 - b1
a2	b2	a2 - b2
...	...	...
a1205	b1205	a1205 - b1205

Sans passer par du VBA, je sais qu’il est très simple de faire cette manip’ avec =A2&« - »&B2
Mais je voudrais intégrer ça dans une boucle for pour que cette formule s’exécute dans ma macro, à la suite de la première boucle que j’ai déjà réussi à faire.

Pour faire ma boucle, j’étais partie sur une formule comme ci-dessous (je ne mets que la boucle sur laquelle je sèche) :

Code :

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

1
2
3
4
5
6
7
Public Sub macro1()
j = 2
For i = 2 To Longueur(« Feuil1 », « A », 2)
	ThisWorkbook.Worksheets(“Feuil1”).Range(“C” & j).Value = ???????
	j = j + 1
Next i
End Sub

C’est dans la seconde partie (celle avec ??????) de la ligne qui commence par « ThisWorkbook… » que je ne vois pas comment formaliser la jointure de plusieurs cellules, en précisant dans quelle feuille du classeur excel il doit chercher les valeurs à mettre bout à bout. J’ai testé plusieurs combinaisons, des plus simples aux plus farfelues, sans succès.
Si quelqu'un sait comment cela est possible, je suis preneuse de toute idée ou de toute piste pour y arriver (en espérant que mon problème soit assez clair...).

Merci d’avoir pris le temps de lire mon post.
Marion.

[rjamin]

Bonjour,
En utilisant Formula il y a moyen
Au lieu de ThisWorkbook.Worksheets(“Feuil1”).Range(“C” & j).Value = ???????

Code :

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

ThisWorkbook.Worksheets(“Feuil1”).Range(“C” & j).Formula = "Votre formule"

[Philippe Tulliez]

Bonjour,
Pour compléter la réponse de Robert que je salue au passage, il est possible de réaliser cela sans boucle.
Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
Option Explicit
Sub t()
 Const myFormula As String = "=RC[-2] & "" - "" & RC[-1]"  '  La formule
 Dim rng As Range
 Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion ' Définir la plage des données
 With rng.Offset(, 2).Resize(, 1) ' La colonne de la plage des données devant contenir la formule
 .FormulaR1C1 = myFormula
 '.Value = .Value ' Garder les valeurs et non les formules
 End With
End Sub

S'il faut garder les valeurs, il suffit de retirer l'apostrophe de la ligne 8 de la procédure.

[illight]

Bonjour Philippe,

Code :

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

Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion ' Définir la plage des données

Je ne comprend pas cette ligne : cette simple ligne de permet de sélection une plage de cellules "raccrochée à la cellule A1 ?

[Philippe Tulliez]

Bonjour Olivier,
La propriété CurrentRegion renvoie un objet Range représentant la zone en cours. C'est un peu l'équivalent du Ctrl+A ou Ctrl+* lorsque l'on a sélectionné une cellule dans une liste de données.

[Rionma]

Bonjour,

Merci pour vos réponses.
Robert, effectivement le .formula est plus adapté, je ne connaissais pas, merci pour le conseil

Philippe, j'ai testé votre code sur un tout petit tableau test. Ca me rapproche du but mais je ne comprends pas le code, du coup j'ai du mal à l'adapter à mon gros fichier (car je dois en réalité concatener 6 colonnes sur plus de 12 000 lignes).
Et j'ai notamment ce souci : la concatenation se fait également sur la première ligne (alors que dans mon fichier, la première ligne correspond aux noms des colonnes, donc je ne veux pas les concatener). J'ai donc essayé :

Code :

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

Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A2").CurrentRegion

(au lieu de "A1") en pensant que la concatenation commencerait ainsi à la deuxième ligne (d'après la réponse que vous avez apporté à Olivier). Mais ça ne marche pas :/

Je me doute que ce n'est pas l'endroit pour poser cette question, mais au cas où, j'en profite : auriez-vous une documentation à me conseiller pour apprendre les rudiments du langage VBA? Je commence à m'y mettre seule et en voyant votre code, je me rends que j'ai beaucoup de choses à découvrir (je suis vraiment larguée sur le codage que vous me conseillez).

En tout cas, encore merci pour vos réponses

[illight]

Merci Philippe,


Après quelques tests suite à ta définition, j'ai compris

Et j'ai notamment ce souci : la concatenation se fait également sur la première ligne (alors que dans mon fichier, la première ligne correspond aux noms des colonnes, donc je ne veux pas les concatener). J'ai donc essayé :

Code :

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

Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A2").CurrentRegion

(au lieu de "A1") en pensant que la concatenation commencerait ainsi à la deuxième ligne (d'après la réponse que vous avez apporté à Olivier). Mais ça ne marche pas :/

Donc si j'ai bien compris (Philippe me corrigera si jamais), en utilisant CurrentRegion, ça te permet de sélectionner une plage de cellules en cours.
Si tu fais un test, en sélectionnant par exemple la cellule A1, et que tu fais un CTRL + A, tout ton tableau, jusqu'aux cellules non vides va être sélectionné.
Si tu fais exactement la même manipulation, en te positionnant dans la cellule A2, ben ça va te faire pareil, car ça va te sélectionner toute ta plage de cellule en cours, et pas celle qui commence par la cellule spécifiée : la cellule permet en fait de savoir quel "tableau de cellules" (si je puis m'exprimer ainsi) tu vas sélectionner.

[Philippe Tulliez]

Bonjour,
Pour les tutoriels, voir dans le lien Les meilleurs cours et tutoriels pour Excel
Comme expliqué à Olivier (voir réponse plus haut), la propriété CurrentRegion est un peu l'équivalent du Ctrl+A ou Ctrl+* lorsque l'on a sélectionné une cellule dans une liste de données donc Rang("A1").CurrentRegion ou Range("B2").CurrentRegion renverra la même plage de données.
Pour commencer à la ligne 2, il faut utiliser Offset.
Cependant lorsque l'on déplace une plage en ligne et/ou en colonne, on déplace toute la plage. Il y a donc lieu de rectifier s'il y a lieu le nombre de lignes et/ou de colonnes et ce à l'aide de la propriété Resize
Petit exemple

Code :

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

1
2
3
4
 Set rng = rng.CurrentRegion
 With rng
  Set rng = .Offset(1, 2).Resize(.Rows.Count - 1, 1)
 End With

Pour mieux comprendre le comportement des propriétés CurrentRegion, Offset, Resize, remplir des données sur la feuille nommées [Feuil1] du classeur où se trouve le code VBA à partir de la cellules A1,, copier le code ci-dessous et lancer la procédure.

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
Sub TestCurrentRegionOffsetResize()
 Dim rng As Range
 Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion
 rng.Select
 MsgBox "Continuer 1 - Adresse " & rng.Address
 Set rng = ThisWorkbook.Worksheets("Feuil1").Range("B2").CurrentRegion
 MsgBox "Continuer 2 - Adresse " & rng.Address
 ' Sélection à partir de la ligne 2 soit un déplacement de une ligne à partir de la cellule A1
 rng.Offset(1).Select
 MsgBox "Continuer 3 - Adresse " & rng.Offset(1).Address
 With rng
 .Offset(1).Resize(.Rows.count - 1).Select
  MsgBox "Adresse " & .Offset(1).Resize(.Rows.count - 1).Address
 End With
End Sub

[EDIT]
Trop vite écrit pas assez testé, les lignes 12 & 13 de la procédure contenaient des erreurs que je viens de rectifier (mis en rouge)

[illight]

Merci pour l'exemple, il y a des choses que je comprend mieux

[Philippe Tulliez]

Bonjour Olivier,
Merci pour le retour.

[illight]

Re,


Désolé d'importuner avec ça, mais j'aimerai comprendre en fait le code, pour pouvoir essayer de l'implémenter sur des trucs que j'ai fait par le passé, et ça me parait beaucoup plus rapide ta méthode.

J'ai testé ton code :

Code :

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

1
2
3
4
5
6
7
8
9
10
Option Explicit
Sub t()
 Const myFormula As String = "=RC[-2] & "" - "" & RC[-1]"  '  La formule
 Dim rng As Range
 Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion ' Définir la plage des données
 With rng.Offset(, 2).Resize(, 1) ' La colonne de la plage des données devant contenir la formule
 .FormulaR1C1 = myFormula
 '.Value = .Value ' Garder les valeurs et non les formules
 End With
End Sub

Dans un premier temps, dans la cellule, j'ai la formule qui s'affiche mais sans la calculer je comprend pas pourquoi.
Dans un 2e temps, si j'enlève l'apostrophe pour avoir la valeur, ça me fait exactement le même résultat qu'au-dessus :



Est-ce parce que MyFormula est une variable "string" ?

En mettant ça :

Code :

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

 Const myFormula As String = "=RC[-2] - RC[-1]"

On a bien la formule désirée et la formule donne bien le résultat.

Pour revenir au code, et plus spécifiquement, cette ligne :

Code :

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

With rng.Offset(, 2).Resize(, 1)

Le Resize permet d'écrire sur la dernière colonne non vide si j'ai bien compris (pour mon tableau par exemple, sur la colonne C) c'est bien ça ?

Par contre, le OffSet je comprend pas : le offSet permet de décaler la plage de cellules de 2 colonnes ? Etant donné qu'on commence à la colonne A, la colonne A "est considérée comme la colonne 0" ?
Si on fait ça :

Code :

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

With rng.Resize(, 1)

ou :

Code :

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

With rng.Offset(, 0).Resize(, 1)

(c'est la même chose non ?)

ça donne quoi ? ça va écrire sur la colonne A ?


Du coup, je comprend pas pourquoi tu fait le offSet de 2 colonnes, puis un resize le offset te permet de de positionner sur la colonne C si j'ai bien compris ? Mais le resize, il sert à quoi ici ?? à agrandir ta CurrentRegion ??
Je m'excuse par avance si mes questions te paraissent absurdes

Merci pour tes lumières

[Philippe Tulliez]

Bonjour,
La propriété Offset dont la syntaxe est expression.Offset(Ligne, Colonne) fait un déplacement en ligne et en colonne para rapport à une cellule ou à une plage de cellules et dans le cas d'une plage la cellule de référence est la première cellule de la plage et la propriété Resize dont la syntaxe est expression.Resize(Ligne, Colonne) redimensionne la plage spécifiée par l'expression.
Exemple 1
Donc si nous avons une plage A1:M15 calculée ou pas par la propriété CurrentRegion et définie par la variable objet rng et que nous voulions sélectionner la colonne E, nous allons nous déplacer de quatre colonnes. Soit

Code :

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

debug.print rng.Offset(, 4).Address

Nous allons obtenir comme affichage $E$1:$Q$15 et pour obtenir uniquement la colonne E, nous allons utiliser Resize

Code :

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

 Debug.Print rng.Offset(, 4).Resize(, 1).Address

C'est à dire que nous redimensionnons la plage en une colonne à partir de la colonne E définie par la propriété Offset.
Exemple 2 Imaginons que nous voulions obtenir la plage de la colonne E à partir de la ligne 2 soit E2:M15
Nous allons donc faire un déplacement de 1 ligne et 4 colonnes

Code :

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

 Debug.Print rng.Offset(1, 4).Address

donnera $E$2:$Q$16 et

Code :

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

1
2
3
 With rng
  Debug.Print .Offset(1, 4).Resize(.Rows.Count - 1, 1).Address
 End With

donnera $E$2:$E$15
Si nous voulions la plage E2:F15

Code :

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

1
2
3
 With rng
  Debug.Print .Offset(1, 4).Resize(.Rows.Count - 1, 2).Address '   Resize sur 2 colonnes
 End With

Dans l'exemple nous avons utilisé la propriété Address mais on aurait pu utiliser la méthode Select ou tout simplement assigner une nouvelle adresse à rng

Code :

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

1
2
3
4
5
6
7
8
Sub t2()
 Dim rng As Range
 Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion ' Définir la plage des données
 With rng
  Set rng = .Offset(1, 4).Resize(.Rows.Count - 1, 2)
 End With
 Debug.Print rng.Address
End Sub

ou d'ailleurs à une autre variable objet (rngColumn) comme ci-dessous

Code :

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

1
2
3
4
5
6
7
8
Sub t2()
 Dim rng As Range, rngColumn As Range
 Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion ' Définir la plage des données
 With rng
  Set rngColumn = .Offset(1, 4).Resize(.Rows.Count - 1, 2)
 End With
 Debug.Print rngColumn.Address
End Sub

Voilà, j'espère que c'est plus clair.

[illight]

Merci pour ces explications

Il va falloir que je lise plusieurs fois ton exemple (que je vais m'empresser de sauvegarder dans un coin ) pour bien l'assimiler. Etant donné que je ne connais pas encore ces rouages, je pense que ça rentrera une fois que je l'aurai intégré dans un de mes traitements "à ma sauce" si je puis dire

[Philippe Tulliez]

Bonjour,
Il est évident qu'il faut faire des tests sur des données connues et de plus rien ne vaut l'expérience.
Isoler une colonne dont on connaît le nom de son étiquette est un des cas pratique de l'utilisation des propriétés Offset et Resize.
Exemple sur une liste de données A1:M15 dont on recherche l'étiquette Salaire

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub SelectionColonne()
 Dim rng As Range, rngLabel As Range, rngData As Range, Position As Integer
 Set rng = ThisWorkbook.Worksheets("db").Range("A1").CurrentRegion ' Ref A1:M15
 Set rngLabel = rng.Resize(1) ' Ref A1:M1
 With rng
  Set rngData = .Offset(1).Resize(.Rows.Count - 1) ' Ref A2:M15
 End With
 ' On veut sélectionner la colonne dont l'étiquette est SALAIRE qui se trouve en colonne 5 (E)
 Position = Application.Match("SALAIRE", rngLabel, 0) ' Position renvoie 5
 ' Affiche la référence E2:E15
 MsgBox "Référence de la colonne Salaire est " & rngData.Offset(, Position - 1).Resize(, 1).Address
End Sub

[illight]

Et dire que je m'étais créé une fonction pour ça, avec une boucle FOR ta fonction est beaucoup plus rapide que la mienne, et beaucoup plus simple


Avec la pratique, j'arriverai aussi à le faire