[RESOLU] Dans VBA Définir plage de données "variable"

[ironmano]

Bonjour le forum !

Depuis le temps que je viens chercher (et trouver !) des infos pour réaliser des petites macro pour le bureau, c'est maintenant mon tour de poser une question ... ce qui sous-entend que je n'ai pas trouvé de réponse avant, donc, que j'ai cherché ... En même temps, j'ai du mal à mettre des mots sur ce que je voudrais faire, j'essaierai donc d'être le plus précis possible.

Je dispose d'un fichier plat extrait à partir d'un logiciel métier externe. Le nombre de données contenues dans ce fichier change suivant le type d'export réalisé précédemment, la plage de dates, etc ... mais le format des colonnes est toujours le même.

Une fois ce fichier importé dans XLS, j'obtiens un fichier du type de celui qui est joint.

Ce que je voudrais faire [Edit](Via une Macro, donc, je cherche des astuces VBA ...)[/Edit] :
- dessiner un graphique des variations des valeurs en colonne F par rapport à la date (colonne E)
- avec une courbe pour chaque changement de Code 2 (Colonne B).

En d'autres avoir, pour cet exemple, sur le même graphique, une courbe A1, une courbe A2, une B1 et une B2 pour pouvoir les comparer entre elles.

Je pensais définir des plages a, b, c, d, e, ... qui contiendraient l'adresse de la première et de la dernière cellule pour lesquelles "Code 2" est identique, puis définir un graphique à partir de ces plages, mais comment définir les plages et ensuite, définir le nombre de séries ?

Merci d'avance pour votre aide.

 

[Dranreb]

Re : Définir plage de données "variable"

Bonjour.
Je pense qu'une macro serait le plus simple pour faire ça. Encore faut-il être sûr que les caractéristiques de l'exemple sont généralisables:
Les "Code 2" se suivent-ils toujours, groupés et classés ?
Portent-ils tous les mêmes dates ? Voire la même période de dates se suivant chaque jour, donc en même nombre de lignes ?
Il pourrait être plus simple de réserver un nombre suffisant de séries quitte à faire en sorte que celles en trop ne s'affichent pas.
À +

 

[ironmano]

Re : Définir plage de données "variable"

Bonjour Dranreb et merci pour ta réponse.

Je pense qu'une macro serait le plus simple pour faire ça

Autant pour moi, je n'avais pas précisé ce point, mais je pensais bien faire celà via Macro et je cherche donc des astuces VBA ... J'en profite pour faire un petit Edit de mon message initial pour les éventuels lecteurs suivants.
Je sais m'occuper de la partie UserForm pour inviter l'utilisateur à renseigner le chemin du fichier, etc ..., mais je cale sur la définition des plages et des séries pour le graphique.

Les "Code 2" se suivent-ils toujours, groupés et classés ?

Non, les "Code 2" changent et ne sont pas forcément contigus. Je peux n'avoir que des A1, parfois que des I1, etc ... et parfois du A1 à Ax et des Bx ... Pour le classement et le regroupement, je le gèrerais après l'import du fichier.

Portent-ils tous les mêmes dates ?

Pour les dates, c'est tout aussi complexe, puisque pour une période de référence d'extraction du fichier plat initial (ex ici du 3 au 5/10/2011), certains "Code 2" peuvent avoir une plage de dates différentes (ex A1 du 3 au 5/10, mais B1 aurait pu n'avoir des valeurs que du 4 au 5/10 et B2 du 3 au 4/10) ...
J'espère que je suis assez clair ...
Voilà pourquoi je voulais vraiment être en mesure de créer cette boucle pour définir toutes les plages d'abord, puis, de ces plages mémorisées, créer des séries dans 1 graphique ...

 

[Dranreb]

Re : Dans VBA Définir plage de données "variable"

J'ai mis un graphique en courbe sur une nouvelle feuille graphique.
Je suis en train de tester ça:

Option Explicit

Private Sub Chart_Activate()
Dim T() As Variant, L As Long, S As Long, Sér As Series, LDéb As Long, Plage As Range
'T = Feuil1.Range("A2:G" & Feuil1.Cells(65526, 1).End(xlUp).Row).Value
For Each Sér In Me.SeriesCollection: Sér.Delete: Next Sér
T = Feuil1.UsedRange.Value
L = 2
Do
   Rem Début Série
   S = S + 1
   LDéb = L
   Do
      L = L + 1: If L > UBound(T) Then Exit Do
      Loop Until T(L, 2) <> T(LDéb, 2)
   Rem Fin Série
   Set Plage = Feuil1.Rows(LDéb).Resize(L - LDéb)
   Set Sér = Me.SeriesCollection.NewSeries
   Sér.ChartType = xlXYScatterLines
   Sér.Formula = "=SERIES(""" & T(LDéb, 2) & """," & Plage.Columns(5).Address(True, True, xlA1, True) _
                                             & "," & Plage.Columns(6).Address(True, True, xlA1, True) & "," & S & ")"
'   Sér.XValues = Plage.Columns(5) ' "=" & Plage.Columns(5).Address(True, True, xlR1C1, True)
'   Sér.Values = Plage.Columns(6) 'Feuil1.Cells(LDéb, "F").Resize(L - LDéb)
'   Sér.Name = T(LDéb, 2)
   Loop Until L > UBound(T)
End Sub

Il reste des détails à régler, notamment rectifier l'échelle de l'axe courbe.

P.S. Je m'aperçois qu'on se heurte à plein de bug dans Excel 2003 en essayant de faire cela.
Les nouvelles version d'Excel acceptent-elles enfin les échelles chronologiques pour les graphiques en nuages de points ?
P.S.2 Je crois que je vais devoir laisser tomber cette voie et faire un graphique à peu près fixe mais qui se basera sur une feuille de séries automatiquement remises en forme.

Oui, ça va nettement mieux comme ça. Encore un petit bug d'Excel 2003 sur les dates, mais qu'on a appris à contourner et c'est bon.

À +

 

[ironmano]

Re : Dans VBA Définir plage de données "variable"

Re-bonjour Dranreb, le forum,

Désolé pour la latence de ma réponse ... plus au bureau depuis hier midi.

J'ai mis un graphique en courbe sur une nouvelle feuille graphique.
Je suis en train de tester ça.
Il reste des détails à régler, notamment rectifier l'échelle de l'axe courbe.
P.S. Je m'aperçois qu'on se heurte à plein de bug dans Excel 2003 en essayant de faire cela.
Les nouvelles version d'Excel acceptent-elles enfin les échelles chronologiques pour les graphiques en nuages de points ?
P.S.2 Je crois que je vais devoir laisser tomber cette voie et faire un graphique à peu près fixe mais qui se basera sur une feuille de séries automatiquement remises en forme.
Oui, ça va nettement mieux comme ça. Encore un petit bug d'Excel 2003 sur les dates, mais qu'on a appris à contourner et c'est bon.
À +

Merci beaucoup pour ces suggestions.

Je teste et revient vous dire ce que ça donne.

Ermanno

 

[ironmano]

Re : Dans VBA Définir plage de données "variable"

Re-

Je teste et revient vous dire ce que ça donne.

Finalement, ça aura été plsu rapide que prévu mon retour ... je n'arrive pas à me dépatouiller de ton code (ai-je précisé que je suis un pur novice et que j'apprends VBA selon mes "besoins pro" ?) ...

J'ai bien compris que tu avais tarnsformé mon tableau avec la mutltitude des données sous forme de séries (onlget "LesSéries"), mais à part ça, je suis perdu ...

Ermanno

 

[Dranreb]

Re : Dans VBA Définir plage de données "variable"

Bonjour.
Faut il que j'ajoute des commentaires explicatifs dans la Private Sub Chart_Activate() de la feuille LeGraph ?
Oups… j'ai laissé trainer un tas de procédures inutiles sorties de l'enregistreur. Le Module1 est à supprimer, tout est dans Graph (LeGraph)
À +

 

[ironmano]

Re : Dans VBA Définir plage de données "variable"

Re-
Merci pour ton retour.
Je dois filer, mais je reviens demain matin pour te faire part de mon retour .
Ermanno

 

[Dranreb]

Re : Dans VBA Définir plage de données "variable"

Finalement j'ai estimé qu'il était intéressant que la tableau soit aussi mis à jour en activant LesSéries.
J'ai donc déplacé (et commenté) le code vers une procédure RevoirSéries d'un module ordinaire.
Elle est simplement invoquée aussi bien dans la Chart_Activate de Graph (LeGraph) que dans la Worksheet_Activate de Séries (LesSéries). Et comme ça le module peut être glissé déplacé vers le vrai classeur.
Cordialement.

 

[ironmano]

Re : Dans VBA Définir plage de données "variable"

Merci à toi.

Je ne serais pas là ces prochains jours, je te fais un retour Lundi ou Mardi.

 

[ironmano]

Re : Dans VBA Définir plage de données "variable"

Finalement j'ai estimé qu'il était intéressant que la tableau soit aussi mis à jour en activant LesSéries.

Salut Dranreb !

Je viens de checker ce fichier et ... c'est exactement ce que je voulais faire !
Merci beaucoup.
Par contre, je suis complètement perdu parce que je n'utilise jamais VBA de cette façon et je ne comprends pas grand chose au code. Une petite explication de texte ?

 

[Dranreb]

Re : [RESOLU] Dans VBA Définir plage de données "variable"

Bonjour.
Je peux difficilement mettre plus de commentaires !
Peut être vous manque-t-il des bases ?
Qu'est-ce que vous ne comprenez pas par exemple ?
À +

 

[ironmano]

Re : [RESOLU] Dans VBA Définir plage de données "variable"

Peut être vous manque-t-il des bases ?
Qu'est-ce que vous ne comprenez pas par exemple ?

Je confirme, il me manque BEAUCOUP de base, en général, j'utilise l'enregistreur de macro et je "peaufine" le code, alor sles tableaux et autres fonctions, je ne connais vraiment pas :-(.
C'est vrai que me demande est trop générale ... je cherche des infos sur les forums.
Je cherche aussi comment contourner ce soucis avec les dates. J'ai tenté de forcer le format de la colonne des dates de l'onglet "test" à NumberFormat = "dd/MM/yyyy;@", mais ne fonctionne toujours pas ...

 

[Dranreb]

Re : [RESOLU] Dans VBA Définir plage de données "variable"

je cherche des infos sur les forums

Je pense qu'il y a toute l'information souhaitable dans l'aide.
L'aide sur l'instruction Redim donne déjà un paquet d'information, et le "voir aussi" débouche sur "Déclaration de tableaux"
Un tableau de Variant à 2 dimensions commençant à 1 peut être affecté à (et renvoyé d') une plage de plusieurs cellules contiguës en une seule instruction. Mais un bug empêche l'enregistrement correct des dates (ça semble avoir un vague rapport avec une mauvaise mise à jour de la propriété Formula). J'ai découvert, je ne sais comment, que la WorksheetFunction.Index avec 0 en guise de ligne et colonne, qui devrait simplement représenter le tableau tel quel dans son intégralité, sans aucun changement, conduit à leur enregistrement correct, ce qui est complètement inexplicable.
Cordialement

 

[ironmano]

Re : [RESOLU] Dans VBA Définir plage de données "variable"

J'ai découvert, je ne sais comment, que la WorksheetFunction.Index avec 0 en guise de ligne et colonne, qui devrait simplement représenter le tableau tel quel dans son intégralité, sans aucun changement, conduit à leur enregistrement correct, ce qui est complètement inexplicable.

Pas chez moi, j'ai justement le problème de date qui reste d'actualité : toutes mes dates avec un jour < 10 de jj/mm/aaaa se transforme dans la feuille "Les Séries" en dates au format mm/jj/aaaa et les autres n'ont pour valeur que du texte (01/09/2011 dans "test" donne 09/01/2011 dans "LesSéries", alors que 10/09/2011 reste identique ...)
Pour illustrer :
Regarde la pièce jointe 868267