Organiser des données avec une macro VBA
Bonjour à tous,
Je suis débutant en VBA.
Je cherche à écrire une macro VBA afin d'organiser mes données.
J'ai des valeurs numériques dans la colonne A (ligne 1 à 200 par exemple).
Je souhaite décaler la valeur A2 en B1, la valeur A4 en B3, la valeur A6 en B5, et ainsi de suite jusqu'à la ligne 300. (voir images ci-jointes)
Puis, supprimer les lignes A2, A4, A6, ..., qui sont maintenant vides.
J'ai bien essayé différents codes, sans prétention, et sans succès.
J'ai notamment testé le code ci-dessous mais dans tous les cas ça ne marchera pas en fait car Excel ne peut pas couper plusieurs plages non contiguës d'un coup. De plus, il manque le fait de supprimer les lignes vides avec une instruction du type Delete Shift:=xlUp .
Sub test()
Set plage = Cells(2, 1)
For i = 1 To 300 Step 2
Set plage = Union(plage, Cells(i, 1))
Next i
plage.Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
End Sub
En fait, il me faudrait une boucle à partir de A2 (i=2):
- couper la cellule A(i) avec i commençant à 2
- coller sur la cellule B(i-1)
- couper la cellule A(i+2)
- coller sur la cellule B((i+2)-1)
-Supprimer les lignes des cellules coupées A(i), A(i+2), ...
Si quelqu'un peut m'apporter une solution ou m'aider, j'en serais ravi !
Je voudrais même ajouter une instruction ensuite qui consisterait à décaler par paquets de 5 lignes des colonnes A+B dans les colonnes adjacentes. Autrement dit, couper les cellules (A6:A10;B6:B10) pour les coller en (C1:C5;D1:D5), puis couper les cellules (A11:A15;B11:B15) pour les coller en (E1:E5;F1:F5), etc. (voir image ci-jointe)
Merci de m'avoir lu et pour votre aide éventuelle.
Bonne soirée,
D-BuG
Bonsoir,
Essaie ainsi :
Sub Test()
Dim PlV(), PlH(), i%, n%, dvi
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlV(1 To n \ 2, 0)
ReDim PlH(1 To n \ 2)
For i = 2 To n Step 2
PlH(i / 2) = .Cells(i, 1)
PlV(i / 2, 0) = .Cells(i - 1, 1)
Next i
If n Mod 2 = 1 Then dvi = .Cells(i - 1, 1)
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(n \ 2).Value = PlV
If n Mod 2 = 1 Then .Offset(n \ 2) = dvi
End With
.Range("B1").Resize(, n \ 2).Value = PlH
End With
End Sub
Cordialement.
Bonjour MFerrand,
Merci beaucoup pour ta réponse.
Ton code répond déjà bien à ma problématique.
Il transpose bien une cellule sur deux et supprime la ligne devenue vide; seul petit bémol, les cellules sont transposées sur la ligne 1, et non pas sur la colonne B. Je ne sais pas si cela est possible.
voir images jointes: résultat obtenu versus résultat théorique
Je te laisse me dire si le résultat théorique est possible à obtenir ?
Je vais étudier en détail le code que tu m'as fourni pour bien le comprendre.
Bien cordialement,
D-BuG
Bonjour,
Je devais déjà fatiguer un peu !
Voilà ! ça simplifie...
Sub Test()
Dim PlgR(), i%, n%
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlgR(1 To n \ 2 + (n Mod 2), 1)
For i = 2 To n Step 2
PlgR(i / 2, 0) = .Cells(i - 1, 1)
PlgR(i / 2, 1) = .Cells(i, 1)
Next i
If n Mod 2 = 1 Then PlgR(i / 2, 0) = .Cells(i - 1, 1)
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(UBound(PlgR), 2).Value = PlgR
End With
End With
End Sub
C'est genre "bête et méchant" ! On fait un tableau, on efface tout et on met le nouveau tableau !
Cordialement.
Cher MFerrand,
Effectivement ça marche nickel, merci beaucoup !
Je vais regarder les subtilités de code que tu utilises de plus près.
Et donc la question finale, c'est à partir de ce nouveau "tableau" à deux colonnes créé, est-ce qu'on peut le répartir en plusieurs colonnes en coupant les données toutes les 5 lignes par exemple.
En clair, les 5 premières lignes restent en (A,B), les 5 lignes suivantes passent en (C,D), les 5 lignes suivantes en (E,F), et ainsi de suite.
(voir images jointes)
En effet, les 5 premières lignes représenteraient un groupe, les 5 lignes suivantes, un autre groupe, etc.
Merci en tout cas,
Bien cordialement,
D-BuG
Bonjour,
Il ne s'agissait donc que d'un résultat intermédiaire... !
Il faut voir si l'on peut facilement obtenir le résultat finale sans matérialiser ce résultat intermédiaire...
Je regarderai dès que possible...
Cordialement.
Bonsoir,
Oui effectivement, je le voyais en deux étapes car une seule étape me paraissait compliquée à coder mais peut-être que cela est au contraire plus simple.
Merci en tout cas,
Cordialement,
D-Bug
Bonjour,
Hier, pas dispo pour travailler...
On passe par le premier tableau intermédiaire, mais sans le matérialiser...
Sub Test()
Dim PlgR(), PlgF(), i%, n%
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlgR(1 To n \ 2 + (n Mod 2), 1)
For i = 2 To n Step 2
PlgR(i / 2, 0) = .Cells(i - 1, 1)
PlgR(i / 2, 1) = .Cells(i, 1)
Next i
If n Mod 2 = 1 Then PlgR(i / 2, 0) = .Cells(i - 1, 1)
n = UBound(PlgR, 1) \ 5 + (UBound(PlgR, 1) Mod 5 = 0)
ReDim PlgF(1 To 5, n * 2 + 1)
For n = 0 To UBound(PlgF, 2) Step 2
If UBound(PlgR, 1) < 1 + n / 2 * 5 Then Exit For
For i = 1 To 5
PlgF(i, n) = PlgR(i + n / 2 * 5, 0)
If PlgR(i + n / 2 * 5, 1) = "" Then Exit For
PlgF(i, n + 1) = PlgR(i + n / 2 * 5, 1)
Next i
Next n
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(5, UBound(PlgF, 2) + 1).Value = PlgF
End With
End With
End Sub
Cordialement.
Bonjour MFerrand,
En effet, c'est parfait comme ceci.
Et dans le cas où j'aurais des groupes de 8 lignes et non pas 5, puis-je modifier la valeur "5" en "8" à certains endroits du code afin d'adapter à chaque fois en fonction de mon nombre de valeurs par groupe ?
Merci beaucoup,
Cordialement,
D-Bug
Bonjour,
Normalement oui ! Je vérifierai... (j'ai un peu de mal à répondre à tous en ce moment...
On peut rendre variable ce groupement par 5, 8 ou autre, en le faisant définir au départ par l'utilisateur (questionnement par InputBox). Je vois ça dès que je peux.
Cordialement.
Bonjour MFerrand,
Ca serait effectivement idéal.
Merci pour vos réponses.
Cordialement,
D-BuG
Nouvelle version :
Sub Test()
Dim PlgR(), PlgF(), i%, n%, lgr
lgr = InputBox("Indiquer le nombre de lignes de groupement des données dans le " _
& "tableau final.", "Groupement des données")
If lgr = "" Then
Exit Sub
ElseIf IsNumeric(lgr) Then
lgr = CInt(lgr)
Else
MsgBox "Taper un nombre !", vbExclamation, "Groupement des données"
Exit Sub
End If
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlgR(1 To n \ 2 + (n Mod 2), 1)
For i = 2 To n Step 2
PlgR(i / 2, 0) = .Cells(i - 1, 1)
PlgR(i / 2, 1) = .Cells(i, 1)
Next i
If n Mod 2 = 1 Then PlgR(i / 2, 0) = .Cells(i - 1, 1)
n = UBound(PlgR, 1) \ lgr + (UBound(PlgR, 1) Mod lgr = 0)
ReDim PlgF(1 To lgr, n * 2 + 1)
For n = 0 To UBound(PlgF, 2) Step 2
If UBound(PlgR, 1) < 1 + n / 2 * lgr Then Exit For
For i = 1 To lgr
PlgF(i, n) = PlgR(i + n / 2 * lgr, 0)
If PlgR(i + n / 2 * lgr, 1) = "" Then Exit For
PlgF(i, n + 1) = PlgR(i + n / 2 * lgr, 1)
Next i
Next n
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(lgr, UBound(PlgF, 2) + 1).Value = PlgF
End With
End With
End Sub
Cordialement.
Bonjour MFerrand,
Merci pour cette nouvelle version.
Par contre, j'obtiens une erreur d'exécution '9' lorsque j'indique un chiffre dans la boite de dialogue qui apparaît quand je lance la macro. (voir pièce jointe)
L'erreur semble se situer à la ligne indiquée "BUG" ci-dessous
Sub test()
Dim PlgR(), PlgF(), i%, n%, lgr
lgr = InputBox("Indiquer le nombre de lignes de groupement des données dans le " _
& "tableau final.", "Groupement des données")
If lgr = "" Then
Exit Sub
ElseIf IsNumeric(lgr) Then
lgr = CInt(lgr)
Else
MsgBox "Taper un nombre !", vbExclamation, "Groupement des données"
Exit Sub
End If
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlgR(1 To n \ 2 + (n Mod 2), 1)
For i = 2 To n Step 2
PlgR(i / 2, 0) = .Cells(i - 1, 1)
PlgR(i / 2, 1) = .Cells(i, 1)
Next i
If n Mod 2 = 1 Then PlgR(i / 2, 0) = .Cells(i - 1, 1)
n = UBound(PlgR, 1) \ lgr + (UBound(PlgR, 1) Mod lgr = 0)
ReDim PlgF(1 To lgr, n * 2 + 1)
For n = 0 To UBound(PlgF, 2) Step 2
If UBound(PlgR, 1) < 1 + n / 2 * lgr Then Exit For
For i = 1 To lgr
BUG=> PlgF(i, n) = PlgR(i + n / 2 * lgr, 0)
If PlgR(i + n / 2 * lgr, 1) = "" Then Exit For
PlgF(i, n + 1) = PlgR(i + n / 2 * lgr, 1)
Next i
Next n
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(lgr, UBound(PlgF, 2) + 1).Value = PlgF
End With
End With
Cordialement,
D-BuG
A priori, c'est que tu as tapé un nombre supérieur à la moitié de ton nombre de lignes initial !
Merci de le vérifier.
Je n'ai pas verrrouillé pour ce type d'erreur, mais on pourra facilement le faire...
Cordialement.
Bonjour,
J'ai testé la macro dans un cas où j'ai 182 lignes initiales et taper 5 dans la boite de dialogue.
Cordialement,
D-Bug
C'est de ma faute !
Bon, version rectifiée (les parties modifiées sont surlignées):
Sub Test()
Dim PlgR(), PlgF(), i%, n%, lgr
lgr = InputBox("Indiquer le nombre de lignes de groupement des données dans le " _
& "tableau final.", "Groupement des données")
If lgr = "" Then
Exit Sub
ElseIf IsNumeric(lgr) Then
lgr = CInt(lgr)
Else
MsgBox "Taper un nombre !", vbExclamation, "Groupement des données"
Exit Sub
End If
With ActiveSheet
n = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim PlgR(1 To n \ 2 + (n Mod 2), 1)
For i = 2 To n Step 2
PlgR(i / 2, 0) = .Cells(i - 1, 1)
PlgR(i / 2, 1) = .Cells(i, 1)
Next i
If n Mod 2 = 1 Then PlgR(i / 2, 0) = .Cells(i - 1, 1)
n = UBound(PlgR, 1) \ lgr + (UBound(PlgR, 1) Mod lgr = 0)
ReDim PlgF(1 To lgr, n * 2 + 1)
For n = 0 To UBound(PlgF, 2) Step 2
For i = 1 To lgr
If n < UBound(PlgF, 2) - 1 Then
PlgF(i, n) = PlgR(i + n / 2 * lgr, 0)
PlgF(i, n + 1) = PlgR(i + n / 2 * lgr, 1)
Else
If UBound(PlgR, 1) < i + n / 2 * lgr Then Exit For
PlgF(i, n) = PlgR(i + n / 2 * lgr, 0)
If PlgR(i + n / 2 * lgr, 1) = "" Then Exit For
PlgF(i, n + 1) = PlgR(i + n / 2 * lgr, 1)
End If
Next i
Next n
Application.ScreenUpdating = False
With .Range("A1")
.CurrentRegion.ClearContents
.Resize(lgr, UBound(PlgF, 2) + 1).Value = PlgF
End With
End With
End Sub
J'ai un peu allongé le code pour ne tester que sur le dernier bloc (ce qui diminue le nombre de tests global, donc plus rapide). Et le test en début de boucle n disparaît.
L'erreur que j'avais signalée pour un nombre de lignes de groupement supérieur à la moitié du nombre d'éléments initial ne se produit plus (elle devait être en fait provoquée par le test que j'ai supprimé). Si tu as 20 lignes au départ, tu peux taper un groupement par 10, 15, 20, 30... tu auras le tableau sur 2 colonnes tel que travaillé au départ (puisqu'il ne peut comporter que 10 lignes)
Par contre un nombre de lignes inférieur à 1 (0 ou nombre négatif) provoquera une erreur. Si tu le souhaites on peut la bloquer dès le départ, au moment de la saisie.
Cordialement.
Bonjour MFerrand,
C'est parfait !
Le programme fait exactement ce qu'il faut.
Pour le cas où la valeur 0 est indiqué, j'ai rajouté les lignes de code suivantes:
If lgr = "" Then
Exit Sub
ElseIf lgr = 0 Then
MsgBox "Merci d'écrire un nombre > 0"
Exit Sub
Par contre, pour le cas où l'utilisateur écrit un nombre non entier x appartenant à 0 < x < 0,5 , il y aura une erreur d'exécution '11' due à une division par zéro.
Il faut que je vois comment assimiler ce cas au fait d'écrire "0" pour qu'il y ait un message d'erreur similaire.
Egalement, les utilisateurs ne sont pas censés écrire un nombre non entier. J'ai vu que cela s'arrondissait au nombre entier supérieur. Il faut que je vois comment faire apparaître un message d'erreur lorsqu'un nombre non entier est écrit.
Merci encore,
Cordialement,
D-BuG
Bonjour,
En fait le test qui était fait lors de la saisie dans l'InputBox prévoyait presque tous les cas :
- si lgr = "", c'est que l'utilisateur avait cliqué sur Annuler, donc on sortait,
- si lgr n'était pas un nombre, on lui signalait et on sortait,
plutôt que de sortir, l'ensemble dans une boucle Do... Loop lui renvoie l'InputBox pour recommencer la saisie...
- si lgr était un nombre on poursuivait,
la conversion éliminait la possibilité d'un nombre décimal, il reste là à tester que la valeur est supérieure à 0, sinon lui signaler et boucler comme pour le cas précédent.
On ne sortirait ainsi qu'avec un nombre entier positif ou en annulant.
Do
lgr = InputBox("Indiquer le nombre de lignes de groupement des données dans le " _
& "tableau final.", "Groupement des données")
If lgr = "" Then
Exit Sub
ElseIf IsNumeric(lgr) Then
lgr = CInt(lgr)
If lgr > 0 Then
Exit Do
Else
MsgBox "Taper un nombre entier positif !", vbExclamation, "Groupement des données"
End If
Else
MsgBox "Taper un nombre !", vbExclamation, "Groupement des données"
End If
Loop
NB : la conversion avec CInt arrondit à l'entier le plus proche, mais pour les décimales égales à 0,5 c'est à l'entier pair le plus proche : 0,5 sera arrondi à 0, 1,5 sera arrondi à 2, mais 0,75 ou 1,25 seront arrondis à 1...
Cordialement.
Bonjour MFerrand,
Effectivement la boucle Do...Loop est encore plus intéressante dans ce cas.
Et d'accord pour la conversion avec CInt, je retiens qu'elle arrondit à l'entier pair le plus proche.
C'est parfait.
Merci pour tout,
Bien cordialement,
D-BuG