Formule classique 2016 ou/et VBA

bonjour,

Comme à mon habitude je vous soumet une question pas simple à formuler.

Aussi je vous donne la réponse en mode paluché, à vous de trouver la réponse en mode Formulé.

En gros le problème est de reporter la date de la colonne 3 vers la colonne 2 sur la première ligne suivante dont la colonne 3 est identique.

Cela doit se faire avec des formules natives, pas de fonction perso ni de 365 donc pas de (MAXIFS)

Cerise sur le gâteau, si vous êtes capable de me faire ça tout en VBA donc avoir le résultat directement "en dur" ce serait encore mieux. Dans ce cas la soluce devrait également calculer la colonne 3. Pour un fortiche du VBA, ça doit pas être trop sorcier. Pour moi j'avoue que mes neurones commencent à saturer un peu : J'ai un peu de mal à optimiser le déroulement de la macro.

Mais ça au pire je dois être capable d'y arriver si j'ai la formule KIVABIEN pour la colonne 2

Comme vous le voyez il s'agit d'un tableau structuré. Cependant il n'est pas possible de le formuler classiquement : Nécessairement les 2 premières lignes doivent être posées "en dur" ce n'est qu'une fois ces 2 lignes posées qu'on peut faire les calculs.

En résumé la formule n'est pas indispensable : Elle n'est qu'une manière de visualiser le résultat à calculer avant de figer le tout "en dur".

Merci

A+

Bonjour Galopin, bonjour le forum,

En transformant le tableau en tableau structuré et avec le code suivant :

Option Explicit

Sub Macro1()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim TS As ListObject 'déclare la variable TS (Tableau Structuré)
Dim I As Integer 'déclare la variale I (Incrément)
Dim R As Range 'déclare la variable R (Recherche)

Set O = Worksheets("Feuil1") 'définit l'onglet O
Set TS = O.ListObjects(1) 'définit le tableau structuré TS
For I = 3 To TS.ListRows.Count 'boucle sur toutes les lignes I du tabelau structuré TS (en partant de la troisième)
    'définit la recherche R (recherche le numéro de ligne dans la colonne 4 de TS en partant de la ligne I et en remontant)
    Set R = TS.ListColumns(4).Range.Find(TS.DataBodyRange(I, 4), TS.DataBodyRange(I, 4), xlValues, xlWhole, , 2)
    TS.DataBodyRange(I, 2) = TS.DataBodyRange(R.Row - TS.HeaderRowRange.Row, 3) 'récupère la date de fin de la première occurrence trouvée
Next I 'prochaine ligne de la boucle
End Sub

Il y a, je pense, une petite erreur dans ta requête :

En gros le problème est de reporter la date de la colonne 3 vers la colonne 2 sur la première ligne suivante dont la colonne 3 est identique.

4 UTT mieux non ?...

ThauThème a écrit :

4 UTT mieux non ?...

Je confirme !

J'ai regardé ça.

Je n'aime pas Find : On est bien sur des tableaux structurés mais j'étais parti sur des Array en Value2 parce que ces histoires de dates je me méfie toujours... Mais je me suis un peu noyé dans mes boucles.

De plus ce n'est que la façade émergée de l'iceberg dans la réalité j'ai 7 autres colonnes à calculer ensuite mais c'est plus simple

Donc l'idée est d'écraser toute la ligne avec des valeurs en dur.

Comme tu es parti je vais être obligé de faire

TS.DataBodyRange(I, 2) =

TS.DataBodyRange(I, 3) =

TS.DataBodyRange(I, 4) =

Bon tu me diras que c'est déjà mieux que de formuler puis d'écraser avec un Collage Spécial Valeur, mébon l'idée de charger tout le tableau dans un Array ligne me paraissait bien séduisante et très optimisée, mais je me perds un peu...

Bon la balle est dans mon camp, je vais travailler un peu la dessus et puis j'arbitrerai...

Merci A+

Re,

Avec un tableau classique, sans Find et avec Value2 :

Sub Macro1()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)

Set O = Worksheets("Feuil1") 'définit l'onglet O
TV = O.Range("G1").CurrentRegion 'définit la tableau des valeurs TV
For I = 4 To UBound(TV, 1) 'boucle 1 : sur toutes les lignes I du tableau des valeurs (en partant de la quatrième)
    For J = I - 1 To 2 Step -1 'boucle inversée sur toutes les lignes J du tableau des valeurs TV (en partant de I-1 et en remontant)
        'si la donnée ligne J colonne 4 de TV est égale à la donnée ligne I colonne 4 de TV
        'récupère la valeur de la cellule ligne J colonne "I" de l'onglet O dans la cellule ligne I colonne "H" de l'onglet O, sort de la boucle
        If TV(J, 4) = TV(I, 4) Then O.Cells(I, "H").Value2 = O.Cells(J, "I").Value2: Exit For
    Next J 'prochaine ligne de la boucle 2
Next I 'prochaine ligne de la boucle 1
End Sub

Sympa !

Je regarde ça demain... Il se fait un peu tard pour moi.

EDIT : Heu j'ai pas résisté à Tester mais ça marche pas " la macro ne fait rien "

A première vue pense que tu ne cibles pas la bonne colonne.

De plus au lieu d'écrire en dur sur la feuille il faut écrire dans ton TV parce que colonne C ou H comme tu veux la aussi il faudra que je reprenne la somme "en dur" ainsi que les 6 autres colonnes donc inutile d'écrire sur les cellules au fur et à mesure. Fait une RAZ sur la plage B4:B15 au lieu de G4:G15 et tu comprendras :
C'est plus évident sur le tableau de gauche si tu considère que tu dois retrouver le résultat des colonnes B ET C "en dur"

Une fois trouvé TV(J,2) la somme colonne C s'affiche mais ton TV(J,2) est vide lui : il faut donc le recalculer en dur, avant de pouvoir coller tout le tableau...

En fait sur le TV "in fine" toutes les valeurs que je dois inscrire dépende de la valeur que tu vas donner en TV(J,2)

Bon de toute façon pour une journée comme aujourd'hui ça sera bien suffisant, je réexaminerai ça demain.

A+

Finalement il n'est pas possible pour moi d'aller dormir quand j'ai un truc comme ça qui me trotte sous par la tête.

Ça me paraissait tellement évident maintenant que j'ai pas résisté à me lancer dans la finition.

La macro corrigée :

Sub Macro2()
Dim i%, j%, Arr, Ws As Worksheet
Set Ws = Worksheets("Feuil1")
Arr = Ws.Range("A1").CurrentRegion.Value2
For i = 4 To UBound(Arr, 1)
    For j = i - 1 To 2 Step -1 ' Fameux !
        If Arr(j, 4) = Arr(i, 4) Then
         Arr(i, 2) = Arr(j, 3)
         Arr(i, 3) = Arr(i, 1) + Arr(i, 2)
         Exit For
        End If
    Next j
Next i
Range("A1").Resize(UBound(Arr), UBound(Arr, 2)) = Arr
End Sub

Le résultat en pièce jointe

Merci encore.

A+ ...pour de nouvelle aventures !

Re,

Bien vu ! Quand j'écrivais dans un tableau je n'avais que du vide dans la cellule tant que je navet pas renvoyé le tableau et ça plantait...

Bonne nuit .