XL 2016 Transformer un nouveau Tableau à double entrée en liste en VBA - impossibilité d'adapter le code

[Jerome-lost]

Bonjour à tous,

C'est vraiment une chance de pouvoir faire appel à une communauté d'amateurs compétents, cela fait maintenant un jour que je n'arrive à rien et je commence à desespérer.

Voilà, Je dispose d'un tableau à double entrée qu'un code VBA transforme en liste . Le script fonctionne tres bien (placé dans la fenetre ci dessous)... en gras ce que je ne comprends pas . Ou cela se complique , c'est que j'ai un nouveau tableau à double entrée que je dois convertir en liste. J'ai essayé d'adapter le code mais je n'abouti à rien. J'aurai aimé partir de ce code pour commencer à comprendre les arcanes des tableaux dynamiques VBA. Je vous transmets le nouveau tableau à double entree que je dois convertir en liste (fichier joint).

Je vous remercie pour l'attention que vous voudrez bien accorder à ma demande

Sub TRANSPOSE_RHPM()
Dim a, b(), i As Long, j As Long, n As Long, x
With Sheets("RHPM SOURCE").Range("a4").CurrentRegion
a = .Value
End With
ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 6)
For i = 2 To UBound(a, 1)
For j = 4 To UBound(a, 2) Step 2
n = n + 1
x = Split(a(1, j), "-")

b(n, 1) = a(i, 1): b(n, 2) = a(i, 2): b(n, 3) = a(i, 3)
b(n, 4) = x(0): b(n, 5) = a(i, j)
b(n, 5) = a(i, j): b(n, 6) = a(i, j + 1)
Next
Next
With Sheets("RHPM RESULTAT").Cells(1).Resize(, 6)
.CurrentRegion.Clear
.Value = [{"Unité","Code Statut","Libellé Statut","Mois","Eff Prév","Etp Rém"}]
.Offset(1).Resize(n).Value = b
With .CurrentRegion
.Font.Name = "calibri"
.Font.Size = 10
.VerticalAlignment = xlCenter
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
With .Rows(1)
.BorderAround Weight:=xlThin
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Font.Size = 11
End With
.Columns.ColumnWidth = 14
End With
.Parent.Activate
End With
End Sub

 

[sylvanu]

Bonjour Jerome_lost, et bienvenu sur XLD,
Pour comprendre ce que fait votre macro, encore faudrait il avoir le bon fichier.
La première ligne de code est With Sheets("RHPM SOURCE").Range("a4").CurrentRegion
alors que votre fichier ne contient pas cette feuille. il ne contient que "ETAT ECDU INTERMEDIAIRE"

 

[Jerome-lost]

Bonjour sylvanu,

Le fichier ETAT ECDU INTERMEDIAIRE sera le nouveau tableau double entree à transformer en liste .
Je vous fais parvenir le fichier originel qui fonctionne (macro et état à transformer). Il fonctionne ainsi:
dans l'onglet RHPM SOURCE je dépose l'état brut et grace à la macro transpose une liste est générée dans l'onglet RHPM RESULTAT

 

[sylvanu]

En première approche :
On sélectionne toutes les données de la feuille "RHPM SOURCE"
Qu'on transfert dans un tableau VBA, dit array nommé a ( avec a = .Value)
On veut le transformer en un autre tableau appelé b, qu'il faut dimensionner.
UBound(a, 1) vous donne le nombre de lignes du tableau a ( ici 1448 )
UBound(a, 2) vous donne le nombre de colonnes du tableau a ( ici 9 )
Le nombre de colonnes de b sera 6 car on veut 6 colonnes, le nombre de ligne sera de :

(((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1))

"/2" car on va analyser une ligne sur 2 pour concaténer multiplié par le nombre de lignes, le -3 car on fait une ligne sur deux et qu'on ne tient pas compte de la ligne 1 qui est les titres.
Ensuite pour toutes les lignes et une colonne sur deux, on prends la valeur et on découpe cette valeur en utilisant comme séparateur "-", par exemple x=Split("MARS- Etp Rém (4)","-") donnera
x(0)=MARS et x(1)=Etp Rém (4)
Puis on construit les 6 valeurs du tableau avec les trois premières valeurs ( colonne 1,2,3 ), puis avec la valeur x(0) c'est à dire le mois, puis les deux valeurs Eff et Etp.
Et on duplique ça pour les 4 mois.
( à noter un petit bug sans conséquence, on a deux fois b(n, 5) = a(i, j), ce qui est inutile.
Et à la fin on va ranger ce tableau b dans la feuille RHPM RESULTAT avec la partie With .... End With.

 

[Jerome-lost]

Merci pour ton retour Sylvanu. Je dois avouer malgre tes explications que certains éléments de la démarche demeurent obscurs.
Apres la déclaration des variables

Dim a, b(), i As Long, j As Long, n As Long, x

Le tableau source à double entrée est selectionné dans son entiereté et c'est la variable a qui enregistre les valeurs de ce tableau

With Sheets("RHPM SOURCE").Range("a4").CurrentRegion
a = .Value
End With

Le tableau dynamique b() serait caractérisé (redim) comme le tableau initial ? à savoir:

ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 6)

UBound(a, 1) >>> nb de lignes du tableau existant (1448) OK
UBound(a, 2) >>> nb de colonnes du tableau existant , j'ai compté 27 colonnes dans l'onglet RHPM SOURCE ?

Tu m'indiques que le nombre de colonnes du futur tableau sera de 6, OK, mais ca se voit ou dans l'expression:

1 To 6) ?

Apres je ne comprends pas pourquoi l'analyse se fait sur une ligne sur 2 et le -3 me laisse perplexe

- 3) / 2)

J'ai compris la dissociation du mois et de l'indicateur et l'application à chaque ligne

J'ai egalement compris le principe de dissocier le tableau en 3 catégories en fonction du comportement de l'information,
b(n,1) info en colonne qui demeure en colonne
b(n,4) info en ligne mois qui doit etre transposé en colonne
b(n,5)info en ligne de nature numérique (indicateur ) qui sera basculée en colonne.

C'est juste que la syntaxe n'est pas evidente

Avec le nouveau tableau à double entrée , ce qui change c'est qu'il n'y a plus qu'un indicateur numerique (realisé), par contre le nombre de variables qui doivent rester en colonne sont tres nombreuses , le tableau dynamique peut le supporter ? le comportement du mois reste le même que pour RH PM

En tout cas je te remercie pour ton retour

 

[sylvanu]

j'ai compté 27 colonnes dans l'onglet RHPM SOURCE : Exact, je me suis planté... en comptant
Ensuite le but est de trouver le nombres de lignes de b.
C'est égal aux nombres de colonnes utiles, donc -3, puis de diviser par deux puisqu'il y a deux colonnes "jumelées" pour chaque mois.
Puis on multiplie ça pas le nombre de lignes de a moins 1 car la première ligne sont les titres.

C'est juste que la syntaxe n'est pas evidente

L'auteur aurait pu mettre des variables plus explicites, c'est toujours plus simple pour la maintenance.
Par ex appeler "a" Source, et "b" Résultat.
Ensuite on adresse un tableau par NomDuTableau(N°ligne,N°colonne) donc b(n,1) pourrait se lire TableauRésultat ligne "n" colonne 1. "n" étant une variable qui s'incrémente à chaque For Next. Et si on renomme "n" Ligne, cela donnerait Résultat(ligne,1).

 

[Jerome-lost]

Si je m'interesse à cette expression:

b(n, 1) = a(i, 1): b(n, 2) = a(i, 2): b(n, 3) = a(i, 3)

b(n, 1) >>> 1ere colonne du nouveau tableau est égale ...
a(i, 1): b(n, 2) >>> je ne sais pas comment cela doit se lire ( peut etre col 1 de l'ancien tableau jusqu'à colonne 2 du nouveau tableau .....qui est lui meme égal .....


Mais j'y pense j'ai 16 colonnes dans mon nouveau tableau à double entrées qui restent inchangés, ca vas etre affreusement fastidieux

 

[sylvanu]

b(n, 1) = a(i, 1): b(n, 2) = a(i, 2): b(n, 3) = a(i, 3)

C'est une astuce d'écriture, le ":" veut dire ligne suivante. Donc equivalent à :

b(n, 1) = a(i, 1)
b(n, 2) = a(i, 2)
b(n, 3) = a(i, 3)

"Il fallait le savoir !"
VBA permet pas mal de choses, c'est sympa quand on maitrise mais perturbant sinon.

 

[sylvanu]

Mais j'y pense j'ai 16 colonnes dans mon nouveau tableau à double entrées qui restent inchangés,

Ca veut dire quoi "inchangées" ? Elle ne sont pas concernées ?
Et c'est dans quel tableau ? "a" ou "b" ?

 

[Jerome-lost]

Ma question initiale était : comment avec mon script VBA, une fois modifié, je peux transformer un nouveau tableau a double entrée en liste . Ce tableau (fichier dans mon 1er mail) commence par 16 colonnes qui restent inchangées , ...excuse moi si j'écorne la synthaxe vba , mais ca va donner ce qui est en dessous ?

b(n, 1) = a(i, 1)
b(n, 2) = a(i, 2)
b(n, 3) = a(i, 3)..........b(n, 16) = a(i, 16)

 

[sylvanu]

La ligne :

ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 6)

dimensionne le tableau sur 6 colonnes ( le 1 to 6 ). Pour 16 colonnes il vous suffit de faire "1 to 16" :

ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 16)

en supposant bien sur que l'expression du nombre de ligne reste le même.

 

[Jerome-lost]

je m'interroge également sur les valeurs données aux 2 variables numérique de la boucle,
soit i qui est égal à 2 et j qui est égal à 4 .

 

[Jerome-lost]

La ligne :

ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 6)

dimensionne le tableau sur 6 colonnes ( le 1 to 6 ). Pour 16 colonnes il vous suffit de faire "1 to 16" :

ReDim b(1 To (((UBound(a, 2) - 3) / 2) * (UBound(a, 1) - 1)), 1 To 16)

en supposant bien sur que l'expression du nombre de ligne reste le même.

Justement vu qu'il n'y a qu'un seul indicateur numerique par mois dans ce fichier (realisé) ,l'expression ne serait pas plutot:

ReDim b(1 To (((UBound(a, 2) - 16) ) * (UBound(a, 1) - 1)), 1 To 18)

-16 étant le nombre de colonnes inchangées et 18 le nombre total de colonnes du nouveau tableau b () ?

 

[sylvanu]

C'est l'initialisation de la boucle.
On commence à la seconde ligne (i=2) et )à la quatrième colonne (j=4).
Là aussi, en appelant "i" NumLigne et "j" NumColonne" c'aurait été plus lisible.

 

[sylvanu]

-16 étant le nombre de colonnes inchangées et 18 le nombre total de colonnes du nouveau tableau b () ?

Comment puis je savoir à quoi ressemble votre nouveau fichier et ce que vous voulez en faire ?

Vraiment désolé, j'ai essayé mais je n'y arrive pas.