Lecture ligne à ligne VBA
Bonjour,
J'essaie désespérément de faire fonctionner un programme vba (je suis un grand débutant de chez débutants) pour construire un tableau. Au lieu de remplir ligne à ligne, le programme remplit colonne par colonne et cela fait décaler les données. Si quelqu'un pouvait me venir en aide, ce serait sympa.
Dans le fichier :
- les données de départ
- le tableau que j'attends
- le résultat que j'ai eu
il faut plus que 2 boucles
une clé peut avoir plusieurs nom et chaque nom peut lui même avoir plusieurs lignes
le tableau 'attendu' est il vide (sauf ligne des libellé) avant chaque traitement ou faut il le vider(sauf ligne des libellé) en début de traitement ou les données d'un traitement vont se mettre à la suite du traitement précédent
si un nom à plusieurs ligne la clé se retrouve devant chaque ligne (ligne 3 et 4 sur la feuille 'attendu' )
mais si une clé à plusieurs nom on ne retrouve pas la clé devant les noms à partir du deuxième ? ( A5 sur la feuille 'attendu' est vide pour Jean)
A plus sur le forum
Bonsoir eHarry, Papyg, bonsoir le forum
Beau cadeau !... À tester (dans la série pourquoi faire simple quand on peut faire compliqué) :
Sub Macro1()
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim K As Integer 'déclare la variable K (incrément)
Dim I As Integer 'déclare la variable I (Incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim IL As Integer 'déclare la variable IL (Intervale de Lignes)
Dim PL As Range 'déclare la variable PL (Plage)
Dim NLM As Integer 'déclare la variable NLM (Nombre de Ligne Max)
Dim NF As Integer 'déclare la variable NF (Nombre de Fois)
Dim LI As Integer 'déclare la variable LI (LIgne)
Dim L As Integer 'déclare la variable L (incrément)
Dim LP As Integer 'déclare la variable LP (Ligne de Placement)
Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
Set OS = Worksheets("donnees depart") 'définit l'onglet source OS
Set OD = Worksheets("attendu") 'définit l'onglet destination OD
OD.Range("A1").CurrentRegion.Offset(1, 0).ClearContents 'efface d'éventuelle ancienne données de l'onglet destination OD
TV = OS.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
'********************************************************************
'définit les blocs correspondant à chaque nom indépendamment du NumCL
'********************************************************************
K = 1 'initialise la variable K
For I = 1 To UBound(TV, 1) 'boucle sur toutes les ligne I du tableau des valeurs TV
If TV(I, 1) = "nom" Then 'condition 1 : si la donnée ligne I, colonne 1 de TV est égale à "nom"
ReDim Preserve TL(1 To 2, 1 To K) 'redimensionne le tableau des lignes TL (2 lignes, K colonnes)
If K = 1 Then 'condition 2 : au premier tour (K=1)
TL(1, K) = I 'recupère dans la première ligne de TL(K) le numéro de ligne I
K = K + 1 'incrémente K (ajoute une colonne au tableau des lignes TL)
Else 'sinon (à partir du second tour)
TL(2, K - 1) = IIf(TV(I - 1, 1) = "NumCL", I - 2, I - 1) 'récupère dans la seconde ligne de TL(K-1) la dernière ligne du nom
TL(1, K) = I 'recupère dans la première ligne de TL(K) le numéro de ligne I
K = K + 1 'incrément K (ajoute une colonne au tableau des lignes TL)
End If 'fin de la condition 2
End If 'fin de la condition 1
Next I 'prochaine ligne de la boucle
TL(2, K - 1) = UBound(TV, 1) 'recupère dans la seconde ligne de TL(K-1) le numéro de la dernière ligne du tableau TV
'le tableau TL contient le première et la dernière ligne de chaque nom
'*********************************************************************
For I = 1 To UBound(TL, 2) 'boucle 1 : sur chaque bloc
IL = TL(2, I) - TL(1, I) 'définit l'intervalle de lignes IL (dernière ligne du bloc - première ligne du bloc)
Set PL = OS.Range(OS.Cells(TL(1, I), "A"), OS.Cells(TL(2, I), "B")) 'définit la plage PL (le bloc)
NLM = 0: NF = 1 'initialise les variables NLM et NF
For Each CEL In Application.Intersect(PL, OS.Columns(1)) 'boucle 2 : sur toutes les cellules CEL de la colonne 1 de la plage PL
'si la cellule CEL est répétée plusieurs fois définit le nombre de fois NF que la celllule est répétée
If Application.WorksheetFunction.CountIf(PL, CEL.Value) > 1 Then NF = Application.WorksheetFunction.CountIf(PL, CEL.Value)
If NF > NLM Then NLM = NF 'si le nombre de fois NF est supérieur au nombre de ligne max NLM, NLM devient NF
Next CEL 'prochaine cellule de la boucle 2
'On connait le nombre NLM de lignes qu'il faudra pour stocker les données de ce bloc
LI = OD.Cells(Application.Rows.Count, "B").End(xlUp).Row + 1 'définit la ligne de destination LI de l'onglet destination OD
Select Case NLM 'agit en fonction du nombre de ligne max NLM
Case 1 'cas d'une seule ligne pour stocker les données
'si la cellule au dessus de la première ligne du bloc est égale à "NumCL",
'renvoie "MunCL" dans la cellule ligne LI colonne A de l'onglet OD, sinon renvoie du vide
OD.Cells(LI, "A").Value = IIf(OS.Cells(TL(1, I) - 1, "A") = "NumCL", OS.Cells(TL(1, I) - 1, "B"), "")
'si la cellule au dessus de la première ligne du bloc est égale à "NumCL",
'renvoie du vide dans la cellule ligne LI colonne K de l'onglet OD, sinon renvoie "A créer"
OD.Cells(LI, "K").Value = IIf(OS.Cells(TL(1, I) - 1, "A") = "NumCL", "", "A créer")
OD.Cells(LI, "B").Value = OS.Cells(TL(1, I), "B") 'renvoie le nom dans le cellule ligne LI colonne B de OD
For L = 2 To IL 'boucle 3 : des lignes 2 à IL du bloc
Select Case PL(L, 1).Value 'agit en fonction de la valeur de la cellule ligne L colonne 1 de du bloc PL
Case "légumes" 'cas
COL = 3 'définit la colonne COL
Case "fruits" 'cas
COL = 5 'définit la colonne COL
Case "viandes" 'cas
COL = 7 'définit la colonne COL
Case "boisson" 'cas
COL = 9 'définit la colonne COL
Case Else 'tous les autres cas
COL = 0 'définit la colonne COL
End Select 'fin de l'action en fonction de la valeur de la cellule ligne L colonne 1 de du bloc PL
If COL <> 0 Then 'condition : si COL est différente de 0
'renvoie dans la cellue ligne LI colonne COL de l'onglet OD la valeur de la cellule ligne L colonne 2 du bloc PL
OD.Cells(LI, COL).Value = PL(L, 2)
'renvoie dans la cellue ligne LI colonne COL+1 de l'onglet OD la valeur de la cellule ligne L+1 colonne 2 du bloc PL
OD.Cells(LI, COL + 1).Value = PL(L + 1, 2)
End If 'fin de la condition
Next L 'prochaine ligne de la boucle 3
Case Else 'cas ou plusieurs lignes pour stocker les données
'si la cellule au dessus de la première ligne du bloc est égale à "NumCL",
'renvoie "MunCL" dans la cellule redimensionnée ligne LI colonne A de l'onglet OD , sinon renvoie du vide
OD.Cells(LI, "A").Resize(NLM, 1).Value = IIf(OS.Cells(TL(1, I) - 1, "A") = "NumCL", OS.Cells(TL(1, I) - 1, "B"), "")
'si la cellule au dessus de la première ligne du bloc est égale à "NumCL",
'renvoie du vide dans la cellule redimensionnée ligne LI colonne K de l'onglet OD, sinon renvoie "A créer"
OD.Cells(LI, "K").Resize(NLM, 1).Value = IIf(OS.Cells(TL(1, I) - 1, "A") = "NumCL", "", "A créer")
'renvoie le nom dans le cellule redimensionnée ligne LI colonne B de OD
OD.Cells(LI, "B").Resize(NLM, 1).Value = OS.Cells(TL(1, I), "B")
For L = 2 To IL 'boucle 4 : sur les lignes 2 à IL du bloc
Select Case PL(L, 1).Value 'agit en fonction de la valeur de la cellule ligne L colonne 1 du bloc PL
Case "légumes" 'cas
COL = 3 'définit la colonne COL
Case "fruits" 'cas
COL = 5 'définit la colonne COL
Case "viandes" 'cas
COL = 7 'définit la colonne COL
Case "boisson" 'cas
COL = 9 'définit la colonne COL
Case Else 'tous les autres cas
COL = 0 'définit la colonne COL
End Select 'fin de l'action en fonction de la valeur de la cellule ligne L colonne 1 du bloc PL
If COL <> 0 Then 'condition : si COL est différente de 0
LP = LI 'définit la ligne de placement LP
Do Until OD.Cells(LP, COL).Value = "" 'exécute jusqu'à ce que la cellule ligne LP, colonne COL soit vide
LP = LP + 1 'incrément LP
Loop 'boucle
'renvoie dans la cellue ligne LP colonne COL de l'onglet OD la valeur de la cellule ligne L colonne 2 du bloc PL
OD.Cells(LP, COL).Value = PL(L, 2)
'renvoie dans la cellue ligne LP colonne COL+1 de l'onglet OD la valeur de la cellule ligne L+1 colonne 2 du bloc PL
OD.Cells(LP, COL + 1).Value = PL(L + 1, 2)
End If 'fin de la condition
Next L 'prochaine ligne de la boucle 4
End Select 'fin de l'action en fontion du nombre de ligne max NLM
Next I 'prochain bloc de la boucle 1
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
MsgBox "Données traitées !" 'message
End SubBonsoir Thau Theme, Papyg,
Bonsoir à tous,
Euh...effectivement, j'étais vraiment très loin...
J'ai testé et ça fonctionne bien.
Bravo et un grand merci : ça a dû vous prendre beaucoup de votre temps.
Vif remerciement également à Papyg d'avoir apporté une réponse qui va m'aider à comprendre.
Oui, je vais prendre le temps de tout comprendre.
A plus
Bonjour à tous,
arf, j'avais presque fini hier soir, j'aurais dû continuer...
Bon,, comme j'ai fini avant de regarder ici je le met quand même :
Sub aplatir()
Const titres As String = "NumCL,nom,légumes,n° légumes,fruits,n° fruits,viandes,n° viandes,boisson,n° boisson,NumCLP"
Dim datas, result, lig As Long, lig2 As Long
Dim numCL, nom As String, article(1 To 2), nbLig As Long, identEnCours As Boolean
Dim dictT As Object, tmp, i As Long
Set dictT = CreateObject("Scripting.Dictionary")
tmp = Split(titres, ",")
For i = 0 To UBound(tmp)
dictT(tmp(i)) = dictT.Count + 1
Next i
datas = [A1].CurrentRegion.Value
ReDim result(1 To UBound(datas) / 3, 1 To dictT.Count)
nbLig = 1
For lig = 1 To UBound(datas)
Select Case datas(lig, 1)
Case "NumCL"
numCL = datas(lig, 2)
identEnCours = True
Case "nom"
nom = datas(lig, 2)
lig2 = lig2 + nbLig
nbLig = 1
If Not identEnCours Then numCL = ""
identEnCours = False
Case "NumCLP"
result(lig2, dictT("NumCLP")) = datas(lig, 2)
Case Else
If Left(datas(lig, 1), 2) <> "n°" Then
article(1) = datas(lig, 1) ' catégorie
article(2) = datas(lig, 2) ' produit
Else ' n°
nbLig = Application.Max(nbLig, datas(lig, 2))
datas(lig, 1) = Application.Trim(datas(lig, 1)) ' des espaces en trop se baladent !!!
result(lig2 + datas(lig, 2) - 1, 1) = numCL
result(lig2 + datas(lig, 2) - 1, 2) = nom
result(lig2 + datas(lig, 2) - 1, dictT(datas(lig, 1))) = datas(lig, 2) ' n°
result(lig2 + datas(lig, 2) - 1, dictT(article(1))) = article(2) ' produit
End If
End Select
Next lig
With Sheets("resultat")
.[A1].CurrentRegion.Offset(1).ClearContents
.[A2].Resize(UBound(result, 1), UBound(result, 2)) = result
.Select
End With
Set dictT = Nothing
End Suberic
Bonjour Eriiic,
Bonjour à tous !
Merci infiniment à vous. ça ne sera pas perdu car comme je suis en apprentissage, ça me permet de voir différentes manières de faire. De plus, j'ai envoyé un exemple de données mais ce n'est pas exactement les données réelles, donc si cela se trouve, j'aurai des adaptations mais avant tout il faut que je comprenne avec les données simples.
Bonne journée
Re,
Un conseil... Laisse tomber mon code, prend celui d'Éric...
Un détail qui a son importance.
Je lis et écris les données en une fois via des variables tableau pour gagner du temps.
Pour le tableau résultat j'ai taillé à la louche avec :
ReDim result(1 To UBound(datas) / 3, 1 To dictT.Count)si tu peux avoir beaucoup de lignes par nom dans les vraies données met plutôt :
ReDim result(1 To UBound(datas) 1 To dictT.Count)par sécurité.
Sinon en gros, comme l'info qui donne le n° de ligne arrive en dernier, le principe est de stocker les données jusqu'au moment où on peut les écrire dans result()
eric
Bonsoir Eric
Bonsoir à tous
Merci pour la précision.
J'aurai une question sur la partie "article"
Est-ce que vous pouvez traduire cette partie :
article(1 to 2)
Case Else
If Left(datas(lig, 1), 2) <> "n°" Then*
article(2) = datas(lig, 2) ' produit
Else ' n°
nbLig = Application.Max(nbLig, datas(lig, 2))
datas(lig, 1) = Application.Trim(datas(lig, 1)) ' des espaces en trop se baladent !!!**
result(lig2 + datas(lig, 2) - 1, 1) = numCL
result(lig2 + datas(lig, 2) - 1, 2) = nom
result(lig2 + datas(lig, 2) - 1, dictT(datas(lig, 1))) = datas(lig, 2) ' n°
result(lig2 + datas(lig, 2) - 1, dictT(article(1))) = article(2) ' produit
* Si la rubrique était <> "n° fruits/viandes etc...", par exemple numfacture, ..., on aurait eu besoin d'écrire cette partie ?
Je joins les données réelles (désolé, il a fallu que je modifie les données). Du coup, j'ai du mal sur cette partie (quoique ce n'est peut-être pas bon non plus pour le reste, je me suis contenté de calquer pour l'instant et je suis bloqué par Indice en dehors de la plage.
Merci de votre aide.
Bonjour,
j'ai du mal à me rendre compte sans une série de lignes réelles complètes.
Si tu es en train de me dire que ces 2 lignes arrivent en ordre inverse, ça ne change pas grand chose car il y a 2 actions différentes à faire. Il faut savoir savoir d'une façon ou d'une autre où on en est dans la lecture avant d'écrire.
J'aurais aussi pu utiliser un flag true/false pour savoir si j'étais sur la 1ère ou 2nde ligne. C'est lui que j'aurais tester au lieu de "n°".
eric
Bonjour
J'ai ajouté un exemple de traitement des cas (cas Bardot) où il y a plusieurs occurrences de deux articles.
Merci
Bonne journée
Ah oui mais là ça change beaucoup de choses.
Il ne faut pas trop simplifier lorsque tu déposes une demande, il faut reprendre à zéro...
1) je ne vois plus de ligne supplémentaire avec "à créer" en l'absence de S21G0030001.
Du coup on laisse vide ? On met "à créer" dedans ? Dans une dernière colonne ?
2) est-ce les S21G0050001 et S21G0056001 seront toujours présents ?
3) est-ce que ça te gêne si je laisse une ligne 1 avec par exemple S21.G00.30.001 S21.G00.30.002 S21.G00.30.003 etc ou faut-il la supprimer pour la recréer à chaque fois ?
Ou bien on met 2 lignes de titre : n° et libellé
Il y aura possiblement d'autres questions lorsque je démarrerai. Essaie de surveiller régulièrement que je ne reste pas bloqué trop longtemps.
eric
re,
1) en fait c'est mon exemple qui a peutr-être induit en erreur. Il faut prendre les données telles qu'elles sont. >J'aurais du mettre un numéro provisoire à la place.
2) ou seulement l'un des deux, oui
3) on laisse une ligne fixe. En fait si j'ai mis le numéro, je me suis dit que c'était un moyen de savoir qu'on est toujours dans le bloc du même individu mais il faut prendre les données telles qu'elles sont (je ne sais pas si je m'exprime bien
Je reste connecté
Merci
Re,
2) On pourrait ne pas aller systématiquement à la ligne avec S21G0056001 si ça facilite les choses, tant que l'on reste sur le même individu. Dans l'exemple de Bardot, les lignes 5 et 7 pourraient très bien être mises sur 2 et 3 de la même personne.
En revanche pour S21G0056001, iln'y a pas de numéro distinguant la deuxième occurrence.
là tu m'as perdu...
Tu parles de la ligne 80 '2ème occurrence art 2' ?
Déjà je ne vois pas de 1ère occurence... Pour moi que ce soit la 1ère ou 18ème ne change rien non ?
Peut-on résumer ainsi ?
Je prend le 7ème caractère, si c'est un 3 ce sont des données communes à répéter à chaque ligne. *
Le premier S21G003xxx apparu détermine le début d'un bloc individu.
Sinon c'est un bloc de données dont la 1ère ligne se termine toujours par S21G005x001
*Edit : y compris 31 donc
En fait, je voulais dire
S21G0050001 : article 1
S21G0056001 : article 2
(ce sont des commentaires perso, ça ne fait pas partie des données)
En gros, il y a 3 blocs d'informations :
- Bloc individu S21G003xxxx : ça commence par S21G0030001 OU S21G0030002 (c'est ce que je voulais dire dans l'exemple des courses, NumCL ouNom). ça se termine par S21G0031011.
- Bloc paiement S21G0050xxx : ça commence par S21G0050001 et se termine par S21G0050010.
- Bloc régularisation S21G0056xxx:ça commence par S21G0056001 et se termine par S21G0056007
Quand il n'y a pas d'info, on laisse à blanc.
Pour
"Tu parles de la ligne 80 '2ème occurrence art 2' ?
Déjà je ne vois pas de 1ère occurence... Pour moi que ce soit la 1ère ou 18ème ne change rien non ?"
Il faut changer de ligne même si c'est pour la même personne à chaque nouvelle occurrence de S21G0050001 (et ce qui suit) et recopier les données Individu (dans l'exemple des courses, si une personne a 3 fruits, on aura 3 lignes de fruits).
Pour une première occurrence de S21G0056001, on pourrait (ou pas) changer de ligne (dans l'exemple des courses, si une personne a acheté des fruits et de la viande, on peut rester sur la première ligne de la personne avec le premier fruit et la viande dans les colonnes correspondantes). Si'il y a une deuxième occurrence, on change de ligne pour la nouvelle viande.
Dis-moi si je t'embrouille en revenant sur l'exemple des courses
Oui, tu m'embrouilles avec tes bananes et ça me donne faim, restons sur le réel. Je vais manger
En résumé :
- si j'ai un 3 en 7ème position c'est une donnée individu à garder sur chaque ligne. RAZ à chaque nouvel individu
- sinon c'est un bloc de données et si les 3 derniers chiffres baissent (par exemple de 007 à 001 ou autre) je peux écrire la ligne. RAZ du bloc de données
Euh...je n'ai pas trop compris la 2ème phrase mais je supose que tu as compris...
Bon, pb
il y a des dates à convertir, seulement je trouve :
12041967
19871212
2102019
1102019
120919
et même 42019...
C'est vraiment le bazar comme ça ?
Je peux tenter d'en convertir quelques unes évidentes et sans ambiguïté .
Dans ce cas quelle est l'année la plus basse ?
Ou bien je retranscris à l'identique (entier et non date) pour ne pas risquer d'introduire des erreurs ?
Edit : et je vois que tu n'as toujours pas répondu clairement à cette question posée au moins 2 fois :
les S21.G00.31 sont-ils à répéter sur toutes les lignes de l'individu ?