Totaliser des loyers verses un mois donné
Bonsoir à toutes et tous.
Je suis nouveau dans ce forum et je n'ai pas trouvé matière à résoudre mon problème.
Je gère une tutelle mon majeur protégé est propriétaire d'un bien mis en location.
Les loyers arrivent sur son compte. Cependant certaines échéances n'ont pas été réglées.
Le locataire verse donc 400 par mois pour un loyer de 350 et maintenant la Caisse d'Allocation Familiale me verse un complément.
Pour rendre des comptes au juge des tutelles, j'ai imaginé faire un tableau sous la forme mois année.
dans la colonne 1 figure le mois, dans la 2 l'année, dans la 3 le loyer principal de 350 € (à 0 lorsqu'il n'est pas réglé, dans la 4 la partie réglée excédent 350 € (à 50 quand il règle 400), dans la 5 le montant total réglé et dans la 6 le montant restant dù mis à jour tous les mois, en colonne J et K se trouvent les dates et les montants réglés.
Mon code est le suivant : il y a un mélange de formules rédigées en Vba. Il fonctionne très bien si le loyer n'est pas payé ou s'il est réglé en une seule fois dans un mois. mais ne fonctionne plus lorsque le règlement est effectué en 2 fois au cours d'un même mois.
Je voudrais donc totaliser 300 et 1678,99 pour le mois de juin 2015
Pourriez vous m'aider s'il vous plait.
Je vous joint le fichier
Je vous remercie par avance
Sub Macroloyer()
'
' Macroloyer Macro
Application.ScreenUpdating = False
'copier les opérations du CCP effectuées en 2013 2014 2015
Sheets("LOYERS").Select
Range("J15:L1000").Select
Selection.ClearContents
Sheets("SAISIE DES OPERATIONS 2013").Visible = True
Sheets("SAISIE DES OPERATIONS 2013").Select
Range("A47:A319,D47:D319,AP47:AP319").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J15").Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2013").Select
Range("A492:A765,D492:D765,AP492:AP765").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J" & Rows.Count).End(xlUp).Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2013").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SAISIE DES OPERATIONS 2014").Visible = True
Sheets("SAISIE DES OPERATIONS 2014").Select
Range("A47:A319,D47:D319,AP47:AP319").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J" & Rows.Count).End(xlUp).Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2014").Select
Range("A492:A765,D492:D765,AP492:AP765").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J" & Rows.Count).End(xlUp).Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2015").Visible = True
Sheets("SAISIE DES OPERATIONS 2015").Select
Range("A47:A319,D47:D319,AP47:AP319").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J" & Rows.Count).End(xlUp).Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2015").Select
Range("A492:A765,D492:D765,AP492:AP765").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J" & Rows.Count).End(xlUp).Select
ActiveSheet.Paste
Sheets("SAISIE DES OPERATIONS 2015").Select
Range("A1").Select
Sheets("LOYERS").Select
'suppression des lignes qui ne sont pas des loyers
Dim i As Integer
With ThisWorkbook.Sheets("LOYERS")
'Précisez le nom de votre feuille
For i = .Range("L" & .Rows.Count).End(xlUp).Row To 15 Step -1
'je travaille sur la colonne f
'Rows.count permet de retourner le nombre de ligne de la plage range
If .Range("L" & i).Value = "A SUPPRIMER" Then
.Rows(i).Delete
End If
Next i
End With
Sheets("LOYERS").Select
'classer par date croissante
Range("J15:K100").Select
ActiveWorkbook.Worksheets("LOYERS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("LOYERS").Sort.SortFields.Add Key:=Range("J15"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("LOYERS").Sort
.SetRange Range("J15:K100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'création du callendrier avec des formules
Range("H15").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[2])"
Range("I15").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[1])"
Range("H15:I15").Select
Selection.AutoFill Destination:=Range("H15:I100"), Type:=xlFillDefault
Range("H15:I100").Select
Range("A15").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[9])"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[8])"
Range("A16").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C<12,R[-1]C+1,1)"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=1,R[-1]C+1,R[-1]C)"
Range("A16:B16").Select
Selection.AutoFill Destination:=Range("A16:B100"), Type:=xlFillDefault
Range("A16:B100").Select
'affectation des montants
Dim cycleA
Dim cycleB
Dim cellule1
Dim cellule2
Dim cellule3
Dim cellule4
Dim cellule5
cycleA = 15
cycleB = 15
For cycleA = 15 To 100
cellule1 = Range("A" & cycleA).Value
cellule2 = Range("B" & cycleA).Value
cellule3 = Range("H" & cycleB).Value
cellule4 = Range("I" & cycleB).Value
cellule5 = Range("K" & cycleB).Value
If cellule1 = cellule3 And cellule2 = cellule4 And cellule5 > 0 Then
Range("K" & cycleB).Select
Selection.Copy
Range("E" & cycleA).Select
ActiveSheet.Paste
cycleB = cycleB + 1
Else
Range("E" & cycleA).Value = 0
End If
Next cycleA
'confection du tableau
Range("C15").Select
ActiveCell.FormulaR1C1 = "=IF(RC[2]=350,350,IF(RC[2]>350,350,0))"
Selection.AutoFill Destination:=Range("C15:C100"), Type:=xlFillDefault
Range("C15:C100").Select
Range("D15").Select
ActiveCell.FormulaR1C1 = "=RC[1]-RC[-1]"
Selection.AutoFill Destination:=Range("D15:D100"), Type:=xlFillDefault
Range("D15:D100").Select
Range("F15").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,350,IF(RC[-1]>=350,350-RC[-1],0))"
Range("F15").Select
Selection.AutoFill Destination:=Range("F15:F100"), Type:=xlFillDefault
Range("F15:F100").Select
'création de la ligne total
Range("F101").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-86]C:R[-1]C)"
Range("B101").Select
ActiveCell.FormulaR1C1 = "total restant dû au"
Range("D101").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "[$-40C]d mmmm yyyy;@"
'effacer toutes les lignes au dela d'aujourd'hui
Range("N15").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("N15").Select
Selection.AutoFill Destination:=Range("N15:N100"), Type:=xlFillDefault
Range("N15:N100").Select
Range("O15").Select
ActiveCell.FormulaR1C1 = "=DATE(RC[-13],RC[-14],DAY(TODAY()))"
Range("O15").Select
Selection.AutoFill Destination:=Range("O15:O100"), Type:=xlFillDefault
Range("O15:O100").Select
Range("P15").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=RC[-2],""CONSERVER"",""SUPPRIMER"")"
Range("P15").Select
Selection.AutoFill Destination:=Range("P15:P100"), Type:=xlFillDefault
Range("P15:P100").Select
Dim j As Integer
With ThisWorkbook.Sheets("LOYERS")
'Précisez le nom de votre feuille
For j = .Range("P" & .Rows.Count).End(xlUp).Row To 15 Step -1
'je travaille sur la colonne p
'Rows.count permet de retourner le nombre de ligne de la plage range
If .Range("P" & j).Value = "SUPPRIMER" Then
.Rows(j).Delete
End If
Next j
End With
Range("L15:L100").Select
Selection.ClearContents
Sheets("Gestion 2015 (1)").Select
Range("J371:J372").Select
ActiveCell.FormulaR1C1 = "=LARGE(LOYERS!R15C6:R[-171]C6,1)"
Sheets("LOYERS").Select
Range("A1:F96").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$96"
Range("A1").Select
End Sub
Bonjour, pour ma part en tant que personne extérieure au fichier, je n'ai pas su me repérer ni dans votre fichier ni dans la macro. Comment vous obtenez 1678,99 avec des valeurs se terminant toutes par 50 ou 0 ? Si vous n'obtenez pas de réponse d'ici 3 jours je pense qu'il faudra revoir la présentation du problème voire fournir un exemple du résultat attendu..
Cordialement
Bonsoir
Bonjour Force rouge
Je ne sais pas trop ce que tu cherches à faire, tu as une macro mais tu fournis un fichier sans macro, dans ta macro il y fait mention de la page ("SAISIE DES OPERATIONS 2013"), page inconnue dans le fichier
Je t'ai fais une formule en colonne E pour calculer le montant payé par mois, à toi de dire si c'est ça que tu cherches
Salutation banzaï, je vais m'inspirer de ta méthode pour lire dans les fichiers texte (que j'ai trouvé dans un vieux post). Ce que je lis dans les livres généraux est nettement plus..".dense" comme méthode...il faut passer par EOF(1) line input etc... alors que toi avec querytable en deux trois lignes le contenu est importé, c'est plus simple je trouve.
Merci
Bonjour
@ Force rouge
Force rouge a écrit :Salutation banzaï, je vais m'inspirer de ta méthode pour lire dans les fichiers texte (que j'ai trouvé dans un vieux post). Ce que je lis dans les livres généraux est nettement plus..".dense" comme méthode...il faut passer par EOF(1) line input etc... alors que toi avec querytable en deux trois lignes le contenu est importé, c'est plus simple je trouve.
Je ne vois pas trop le rapport entre ce post et ce que tu dis
Je ne suis pas assez subtil il faut me dire les choses telles quelles sont
Amicalement
Bonjour Banzai64 et Force rouge
Je vous remercie pour votre aide.
En fait je n'ai pas souhaité publier les feuilles saisie des opérations 2013 2014 et 2015 car il s'agit de données de dépenses et de recettes personnelles de la personne protégée.
J'ai essayé la formule proposée et elle fonctionne très bien. Cependant elle ne répond pas tout à fait à mes attentes.
Je vais essayer de détailler mon code paragraphe par paragraphe :
Au préalable j'ai inséré une formule dans la colonne AP de chaque feuille se saisie des opérations
Cette formule est la suivante : SI((ESTERREUR(TROUVE("loyer";B47)))=VRAI;"A SUPPRIMER";"A GARDER")
S'il trouve le mot "loyer" il inscrit "A GARDER" si non "A SUPPRIMER"
Le code débute ainsi :
1)'copier les opérations du CCP effectuées en 2013 2014 2015
L'ensemble des opérations effectuées sur le compte sont copiées de la façon suivante : date en J, montant en K et les mots à garder ou à supprimer en L
2)'suppression des lignes qui ne sont pas des loyers
Les lignes qui contiennent les mots A SUPPRIMER sont effacées de telle sorte qu'il ne reste que les dates et les montants des loyers reçus.
Ensuite je supprime la colonne L pour ne plus voir les mots A GARDER
3)'classer par date croissante
Jusqu'en juin 2015 les loyers étaient reçus sur un seul compte mais la CAF a versé sur un deuxième compte un complément de loyer indemnisant en partie les loyers impayés. De ce fait les dates ne se suivaient plus donc j'ai du faire en sorte que les dates se classent dans l'ordre chronologique.
4)'création du calendrier avec des formules
le but de cette opération est de créer le calendrier sous la forme mois et année en colonnes A et B
Je crée 100 lignes de ce fait cela dépasse la date d'aujourd'hui
5) 'affectation des montants
Ce paragraphe est le paragraphe clé : En fait le but est de comparer les mois et années du calendrier crée avec les mois et années des échéances réellement versées. Il inscrit les montants colonne E Je n'arrive pas à lui faire totaliser les 2 montants reçus en juin 2015. En effet il tient compte du premier montant 400 € puis passe à la ligne suivante. Si bien qu'il ne tient plus compte du mois de juillet, lui affectant la valeur 0.
Je n'arrive pas à trouver une autre méthode. Peut-être un problème de compteur du cycleB
6)'confection du tableau
il crée les colonnes C, D et F
7)'création de la ligne total
il crée la ligne total en ligne 101
8)'effacer toutes les lignes au delà d'aujourd'hui
Il enlève toutes les lignes qui dépassent la date d'aujourd'hui
la suite est la création de la zone d’impression de la page
Voilà j'espère que c'est plus clair pour vous.
Je vous remercie de l'attention que vous porterez à mon problème.
Bien cordialement
Bonjour
Pour être plus complet voici 2 nouveaux fichiers.
Bien à vous
Bonjour
Indiques quelles sont les différences entre le fichier avec la formule et le fichier du résultat obtenu
En pointant ces différences je verrai peut -être une solution
Bonjour et merci
Je n'ai rien changé.
Simplement lorsque j'ai saisis dans les comptes le deuxième virement pour le même mois lorsque j'ai mis a jour j'ai obtenu ce que tu vois sur le fichier "ce que j'obtiens".
J'ai bricolé le résultat et confectionné le fichier "ce que je veux obtenir" pour te montrer ce que je veux obtenir.
Je m'excuse d'avoir mis un très long commentaire.
Je me suis peut être mal fait comprendre. Dans mon paragraphe que j'appelle 5 j'utilise 2 cycles :
Le cycle A est le compteur du calendrier colonne A et B
Le cycle B est le compteur du calendrier colonne H et I
Le cycle A prend la valeur 15 puis augmente de 1 à chaque fois
Le cycle B prend également la valeur 15 puis augmente d'1 à chaque fois. comme je le disais dans mon commentaire il faudrait peut être modifier le compteur de cycle B en intégrant une condition le faisant augmenter d'1 si on change de mois et le figeant si on ne change pas de mois.
Je ne sais pas faire. Merci encore
Bien cordialement
Bonjour
Banzai64 a écrit :Bonjour
Indiques quelles sont les différences entre le fichier avec la formule et le fichier du résultat obtenu
En pointant ces différences je verrai peut -être une solution
Bonjour
Je suis désolé de me répéter mais il n'y a pas de changement.
Pour te montrer j'ai enlevé la ligne du virement de la CAF dans ma grille de saisie.
Je joins le fichier ainsi obtenu.
Cela fonctionne parfaitement bien.
Saches tout de même qu'à l'impossible nul n'est tenu.
Bien cordialement
Bonjour
On ne va trop continuer à ce jeu
Dans un de tes post tu as fournis un fichier intitulé "LOYERS CE QUE JE SOUHAITE OBTENIR.xlsx"
Donc pour moi c'est comme cela que tu veux avoir tes données
Moi je t'ai fourni un fichier " viaduc12 Formule V001.xlsx" qui est identique en tout point à ton fichier
Maintenant tu joins un fichier complétement différent
Moi j'abandonne dans ces conditions
Bonsoir et merci
Je ne vois pas en quoi il est totalement différent mais bon je te remercie d'avoir pris un peu de ton temps.
A bientôt dans une autre discussion qui sait ?
Bonne soirée
Bonjour à tous
Je me permets de relancer le sujet.
Oubliez tout ce que vous avez pu lire précedement.
Je poste 2 nouveaux fichiers simplifiés pour vous permettre d'y voir plus clair.
Je vous laisse le soin d'aller dans rechercher la macro dans par le menu AFFICHAGE
Je vous remercie.
Bien cordialement
Bonjour