Remplir des cellules entre deux colonnes

Bonjour à vous,

j'ai cherché un peu partout et je ne trouve pas de solution à ma question.Je fais un peu de VBA( pas à un très haut niveau mais j'essaye) et je ne trouve rien qui puisse résoudre ce problème.

J'ai un fichier en pièce jointe.

Dans ce fichier,le montant en colonne J est divisé par le temps de contrat de la maintenance, afin de voir les revenus mensuels.

J'ai fait une macro qui récupère la date la plus ancienne de la colonne Q, la plus éloignée de la colonne R afin de les mettre à partir de la colonne AA.à partir de là, j'ai la formule que vous voyez qui met le montant mensuel du début du contrat et de la fin de contrat( ligne 7 par exemple).

La question est: comment puis-je détecter les montants supérieurs à 0 les plus à droite de mon tableau( qui correspond à la fin de mon contrat),et copier la valeur sur toutes les cellules de gauche jusque la valeur supérieure à 0 sur la ligne( qui correspond au début de mon contrat). Pour reprendre l'exemple de la ligne 7, je devrais avoir 55,8 de Décembre 2017 à décembre 2018 dans toutes les cellules.. Je ne suis pas convaincu de m'y être pris de la manière la plus facile.. Donc, si vous voyez une autre méthode, n'hésitez pas.

Bien entendu, les dates en ligne 1 changeront potentiellement à chaque extrait ( plus long ou plus court)

Voilà le problème.

Merci

Fred

Salut,

En premier lieu, je te conseille, quand tu veux travailler avec des dates, de saisir des dates au format date (par ex : 01/12/2017 et pas Dec 2017)

Après, pour correspondre à ton fichier, tu peux modifier le format d'affichage afin d'afficher Dec. 2017.

Pour la suite, tu peux essayer cette formule dans la cellule AB7 par exemple :

=SI(ET(AB$1>=$Q7;AB$1<=$R7);$AA7;0)

Voir le fichier joint pour exemple.

J'ai mis des dates en ligne 1 (colonne AA,AB, ...) et en colonne Q2 à R7

Il te reste à modifier les dates des colonnes Q8 et R257

A+

Bonjour,

Dater sous forme texte ne va pas sans quelque problème...

Mais sérions les problèmes :

exemple : début : avril2018, fin : avril2019, durée calculée =12, montant mensuel mis pour avril2018 et pour avril2019, et ton problème c'est de l'avoir dans les colonnes intermédiaires...

...mais si tu le mets dans les colonnes intermédiaires tu l'auras mis dans 13 colonnes, alors que la durée est supposée 12 !

Si on dit 01/04/18 à 01/04/19, la différence sera 12 mois, mais avril2019 n'est pas inclus dans le contrat. Le premier problème est donc la signification des bornes. Si le premier mois est le mois d'effet et le second le mois où l'effet cesse, le contrat est bien 12 mois et ira de avril18 à mars19.

Si les deux bornes sont incluses par contre le contrat sera de 13 mois...

Cordialement.

Bonjour,

merci beaucoup pour vos réponses. J'ai transformé les dates en format texte car: dans mon fichier initial, les dates sont au format:

jj/mm/aaaa.

Lorsque j'ai bricolé la macro pour récupérer la date la plus ancienne en colonne Q et la date la plus éloignée en colonne R, afin de faire le tableau de AA à on ne sait pas, je me suis retrouvé avec la liste de toutes les dates incluant les jours, et non pas seulement les mois à l'horizontal.il me semblait plus simple de tout transformer en texte, puis de supprimer les doublons (mm-aaaa) car je ne voyais pas comment me débarasser du reste.

Je vous montre ce que j'ai fait ( mélange de trucs que je connaissais déjà, de trucs trouvés sur internet et d'enregistrement( attention,c'est agressif à voir pour les pros)....

Sub maintenance()

Dim DernLigne As Long
Dim DernLigne2 As Long
Dim LR As Long
Dim LR2 As Long
Dim sc As Range
Dim DernLigne3 As Long
sd = Range("AB1")  ' start date
ed = Range("AB2")  ' end date
Set sc = Range("AE1")  ' start cell
' check dates
Dim DerniereLigne As Integer
Dim DernLigne4 As Long
Dim DernLigne5 As Long

Application.ScreenUpdating = False

Sheets("Feuil1").Select
 Columns("Q:Q").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

 Columns("R:R").Select
    Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

LR = Cells(Rows.Count, "Q").End(xlUp).Row
Range("Q2:Q" & LR).NumberFormat = "mm/yyyy"
LR2 = Cells(Rows.Count, "R").End(xlUp).Row
Range("R2:R" & LR).NumberFormat = "mm/yyyy"

Range("Z1").Select
ActiveCell.FormulaR1C1 = "Number of months"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=DATEDIF(RC[-9],RC[-8],""m"")"
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
Range("Z2").AutoFill Destination:=Range("Z2:Z" & DernLigne)
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Prix par mois"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=RC[-17]/RC[-1]"
DernLigne2 = Range("A" & Rows.Count).End(xlUp).Row
Range("AA2").AutoFill Destination:=Range("AA2:AA" & DernLigne)
Range("AB1").Select
Selection.Value = Now
Selection.NumberFormat = "mm/yyyy"
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=MAX(RC[-10]:R[5000]C[-10])"
   Range("AB1:AB2").Select
    Application.CutCopyMode = False
    Selection.Copy
Range("AC1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

If ed - sd <= 0 Then Exit Sub
j = 0
For i = sd To ed
   sc.Offset(j, 0) = i
   j = j + 1
Next i

    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mmm-aaaa"")"
    DernLigne = Range("AE" & Rows.Count).End(xlUp).Row
    Range("AF1").AutoFill Destination:=Range("AF1:AF" & DernLigne)
    Range("AF:AF").Select
    Selection.Copy
    Range("AG:AG").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Columns("AG:AG").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$AG$1:$AG$43067").RemoveDuplicates Columns:=1, Header:= _
        xlNo

DerniereLigne = Range("AG65536").End(xlUp).Row

Range("AG1:AG" & DerniereLigne).Copy
Range("AI1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-12],""mmm-aaaa"")"
DernLigne4 = Range("A" & Rows.Count).End(xlUp).Row
Range("AC2").AutoFill Destination:=Range("AC2:AC" & DernLigne4)
Range("AC:AC").Select
    Selection.Copy
    Range("Q:Q").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Range("Q1").Select
ActiveCell.FormulaR1C1 = "Contract Start"

Range("AC2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-11],""mmm-aaaa"")"
DernLigne5 = Range("A" & Rows.Count).End(xlUp).Row
Range("AC2").AutoFill Destination:=Range("AC2:AC" & DernLigne5)
Range("AC:AC").Select
    Selection.Copy
    Range("R:R").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Range("R1").Select
ActiveCell.FormulaR1C1 = "Contract End"

Columns("AB:AG").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToRight

Application.ScreenUpdating = True

End Sub

Je ne vois pas comment faire autrement pour récupérer la date début fin, lister tout ce qu'il y a entre deux et les mettre à l'horizontal en retirant la problématique jour et en gardant le format date..

On dispose de diverses fonctions pour cela... mais tu as la chance que DATEDIF et, plus intéressant (car DATEDIF est une fonction que Microsoft recommande de ne plus utiliser), FRACTION.ANNEE convertissent ces valeurs texte et renvoient le résultat voulu...

Mais pour la suite ça ne marche plus, mais on a le moyen d'utiliser tes textes pour renvoyer le bon résultat dans les colonnes... Il conviendrait cependant que tu répondes à la question des bornes ! L'évaluation de la durée d'un contrat est tout de même un élément vital en la matière !

Cordialement.

Bonsoir,

Vous avez raison! Si un contrat se commence en avril 2017, il se termine en mars 2018. Ce qui signiifie que ce que j' ai fait est incorrect..

Fred

Re,

Mais d'avril18 à mars19, ça fait bien 12 mois. Juste un problème de définition de la borne de sortie, il faut stipuler qu'elle n'est pas incluse...

Voilà une formule à placer en AB2.

Elle utilise le mois de début (en Q), le nombre de mois (en Z) et le prix mensuel (en AA), mais pas la colonne R, donc si tu modifies cette dernière colonne cela n'aura pas d'effet sur la formule.

=SI(ET(EQUIV($Q2;$1:$1;0)-COLONNE()<=0;EQUIV($Q2;$1:$1;0)-COLONNE()+$Z2>0);$AA2;"")

Mais cette formule ne peut plus fonctionner si le mois en Q est antérieur au premier mois du tableau en AB1.

Il faut donc une autre formule pour ce cas qui fera démarrer le marquage en AB et réduira le nombre de mois à marquer figurant en Z de l'écart entre le mois en Q et celui en AB1.

=SI(ET(COLONNE($AB$1)-COLONNE()<=0;COLONNE($AB$1)-COLONNE()+$Z2-DATEDIF($Q2;$AB$1;"m")>0);$AA2;"")

On réunit ces deux formules en une seule, sous condition : si on est dans le cas normal (mois postérieur ou égal à AB1) on applique la première, sinon la seconde.

=SI($Q2-$AB$1>=0;SI(ET(EQUIV($Q2;$1:$1;0)-COLONNE()<=0;EQUIV($Q2;$1:$1;0)-COLONNE()+$Z2>0);$AA2;"");SI(ET(COLONNE($AB$1)-COLONNE()<=0;COLONNE($AB$1)-COLONNE()+$Z2-DATEDIF($Q2;$AB$1;"m")>0);$AA2;""))

Formule à étendre sur tout le tableau : AB2 à BD257 en l'état actuel de ton fichier.

Elle te placera le montant mensuel sur les mois concernés...

Cordialement.