Discussion :

[viaduc12]

Bonsoir à tous
J'ai confectionné un tableau avec les dates et les montants des loyers reçus.
exemple
12/05/15 400,00
10/06/15 400,00
19/06/15 1 578,99
10/07/15 400,00
Je souhaite totaliser les loyers du mois de juin puis supprimer une des deux lignes pour obtenir
12/05/15 400,00
19/06/15 1 978,99
10/07/15 400,00
Comment puis-je faire en vba sous excel 2013 ?
Je vous remercie
Bien cordialement

[Philippe Tulliez]

Bonjour,
Pour obtenir la somme des montants du mois de juin, nul besoin de code VBA, la fonction BDSOMME peut parfaitement le faire avec comme critère calculé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOIS(A2)=6

Dans cet exemple la liste de données se trouve en plage A1:B5 avec en première ligne les étiquettes de colonnes (Date, Montant) et la zone des critères en $D$1:$D$2 (En $D$1 l'étiquette _fn_ et en $D$2, la formule affichée ci-dessus.
La formule ci-dessous permet de calculé la somme des montants suivant le critère calculé par la formule en $D$2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=BDSOMME($A$1:$B$5;B1;$D$1:$D$2)

[viaduc12]

Bonsoir et merci
J'ai du mal à appliquer la fonction
Si je crée un fichier avec mon exemple que dois-je faire ?
A1 DATE B1 MONTANT
A2 12/05/15 B2 400,00
A3 10/06/15 B3 400,00
A4 19/06/15 B4 1 578,99
A5 10/07/15 B5 400,00
tout d'abord je voudrais savoir où vous glissez =MOIS(A2)=6
par ailleurs vous évoquez A1 B5 alors qu'en A1 il y a le mot date et pas de valeur
et enfin la formule comporte des $ ce qui veux dire qu'elle ne pourra s'appliquer aux lignes d'en dessous.

Je vous précise à l'aide des 2 fichiers joints ce que j'obtiens avec la macro (macroloyer) et ce que je voudrais obtenir.
Cela vous permettra d'y voir plus clair

Bien cordialement

[louis.nicolle]

Bonjour,
Si tu souhaites sommer toutes les dates de juin, commence par créer une colonne MOIS(A) qui donne les mois de la rangée A; je l'ai insérée en colonne C pour ma part.
Il te suffit ensuite d'appliquer le code suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Sub somme()

K=0 'variable de balayage du tableau
SommeJuin=0 'Variable ou l'on stock ta somme
HauteurJuin=-1 'Hauteur de la première case de juin
TailleJuin = 0 'Nombre de case en Juin

While Range("A1").Offset(K,0) <> ""   'je parcours toutes les cases loyers en rangée "A"
    If  Range("C1").Offset(K,0).Value = 6 Then
         If HauteurJuin = -1 Then
             HauteurJuin = K
         End If
     SommeJuin = SommeJuin + Range("A1").Offset(K,1).Value
     TailleJuin =TailleJuin+1
      End If
      K = K+1 
      Wend

'Maintenant tu as toutes les données pour réarranger ton tableau en ramplacant tout d'abord la ligne juin puis en raccolant la fin du tableau:

Range("A1").Offset(HauteurJuin,1).Value = SommeJuin

'Ensuite tu décales chaque données au dessus de Juin

K2=0

While Range("A1").Offset(HauteurJuin + TailleJuin + K2,0).Value <> ""
       Range("A1").Offset(HauteurJuin + 1 + K2,0).Value = Range("A1").Offset(HauteurJuin + TailleJuin + K2,0).Value
       Range("A1").Offset(HauteurJuin +1+ K2,1).Value = Range("A1").Offset(HauteurJuin + TailleJuin + K2,1).Value
       Range("A1").Offset(HauteurJuin + TailleJuin + K2,0).Value=""
        Range("A1").Offset(HauteurJuin + TailleJuin + K2,1).Value = ""
       K2 = K2 +1
Wend
End Sub

Et voila, bon courage .

[louis.nicolle]

Et si tu n'as pas la place pour ajouter une nouvelle colonne, tu peux toujours remplacer ma condition: MOIS =6 par deux conditions de comparaison avec DateSerial(00/06/15) et DateSerial(31/06/15), mais c'est un peu plus lourd à lire .

[viaduc12]

Bonjour et merci
Je vous adresse le fichier avec les modifications.
Le bouton mise à jour loyers met le fichier à jour avec ma macro loyers.
J'ai intégré la macro "somme" que vous avez rédigé.
J'ai ajouté un bouton mise à jour somme.
Vous pouvez ainsi voir le résultat obtenu grâce aux 2 macros.
Je pense que j'ai pas très bien su adapter votre texte à mon fichier.

En fait à partir de septembre 2015 je recevrai 2 virements par moi : l'un par le locataire et l'autre de la CAF en complément.

Le total devras donc se faire sur toutes les échéances et pas seulement sur juin ou même sur juin 2015.

Bien cordialement

[viaduc12]

Je vous remercie pour votre aide.
Je viens ici détailler un peu ma macro macroloyer
Au préalable j'ai inséré une formule dans la colonne G de chaque feuille de saisie des opérations
Cette formule est la suivante : SI((ESTERREUR(TROUVE("loyer";B4)))=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

[vinc_bilb]

Bonjour,

Mis à part l'intérêt de la programmation, un TCD le ferait très bien, non?

[Philippe Tulliez]

Bonjour,

tout d'abord je voudrais savoir où vous glissez =MOIS(A2)=6

Si tu lis l'ensemble de ma réponse, tu pourras remarquer que je l'ai indiqué

Dans cet exemple la liste de données se trouve en plage A1:B5 avec en première ligne les étiquettes de colonnes (Date, Montant) et la zone des critères en $D$1:$D$2 (En $D$1 l'étiquette _fn_ et en $D$2, la formule affichée ci-dessus.

par ailleurs vous évoquez A1 B5 alors qu'en A1 il y a le mot date et pas de valeur
et enfin la formule comporte des $ ce qui veux dire qu'elle ne pourra s'appliquer aux lignes d'en dessous.

Il existe une aide pour chaque fonction dans Excel qui indique la syntaxe, l'explication de chaque paramètre et contient également des exemples. Il faudrait s'en servir.

[viaduc12]

Bonjour
J'ai résolu mon problème
En fait mon code était bon il fallait juste passer par une feuille que j'ai appelé "RELAIS" dans laquelle j'ai copié les données, ensuite en décalant mes échéances d'une case vers le bas, le total des échéances s'est effectué horizontalement et cela m'a permis d'affecter la valeur 0 à la ligne qui était en double et ainsi lui affecter le texte "SUPPRIMES" Ce n'est qu'en recopiant toutes les échéances dans la feuilles que j'avait appelé "LOYERS" que j'ai pu supprimer les lignes qui contenaient le texte "SUPPRIMES"
Je penses qu'il y a plus simple. Mais du fait que je ne suis pas du tout du métier de l'informatique mais que ce dernier fait travailler ma logique le compliqué ne me dérange pas.
Le sujet est donc clos puisque le problème est résolu.
A bientôt dans une nouvelle discussion
Bien à vous

le code s'établie ainsi.
Macroloyer Macro
Application.ScreenUpdating = False
'copier les opérations du CCP effectuées en 2013 2014 2015
Sheets("LOYERS").Select
Range("A15:S1000").Select
Sheets("RELAIS").Visible = True
Sheets("RELAIS").Select
Range("A15:S1000").Select
Selection.ClearContents
Sheets("SAISIE DES OPERATIONS").Select
Range("A47:A319,D47319,AP47:AP319").Select
Selection.Copy
Sheets("RELAIS").Select
Range("J15").Select
ActiveSheet.Paste

'suppression des lignes qui ne sont pas des loyers
Dim i As Integer
With ThisWorkbook.Sheets("RELAIS")
'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("RELAIS").Select

'classer par date croissante
Range("J15:K100").Select
ActiveWorkbook.Worksheets("RELAIS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RELAIS").Sort.SortFields.Add Key:=Range("J15"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RELAIS").Sort
.SetRange Range("J15:K100")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'copier dates et montants en décallé
Sheets("RELAIS").Select
Range("O15100").Select
Selection.ClearContents
Sheets("RELAIS").Select
Range("J15:K100").Select
Selection.Copy
Sheets("RELAIS").Select
Range("O16").Select
ActiveSheet.Paste

'restiter mois et années des dates

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("M15").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[2])"
Range("N15").Select
ActiveCell.FormulaR1C1 = "=YEAR(RC[1])"
Range("M15:N15").Select
Selection.AutoFill Destination:=Range("M15:N100"), Type:=xlFillDefault
Range("M15:N100").Select

'totaliser les loyers de chaque mois
Range("Q15").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-9]=RC[-4],RC[-4]=R[1]C[-4],RC[-8]=RC[-3],RC[-3]=R[1]C[-3])=TRUE,(RC[-6]+RC[-1]),RC[-6])"
Range("R15").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(R[1]C[-5]=R[2]C[-5],R[1]C[-4]=R[2]C[-4])=TRUE,0,RC[-1])"
Range("Q15:R15").Select
Selection.AutoFill Destination:=Range("Q15:R100"), Type:=xlFillDefault
Range("Q15:R100").Select

'identifier les loyers verses 2 fois dans un meme mois
Range("S15").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,""CONSERVES"",""SUPPRIMES"")"
Range("S15").Select
Selection.AutoFill Destination:=Range("S15:S100"), Type:=xlFillDefault
Range("S15:S100").Select

' report des loyers dans l'onglet loyers
Sheets("LOYERS").Select
Range("A15:G100").Select
Selection.ClearContents
Sheets("RELAIS").Select
Range("J15:J100,R15:S100").Select
Selection.Copy
Sheets("LOYERS").Select
Range("J15").Select
ActiveSheet.Paste
' restitution mois et annee

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

' supprimer les lignes dont le montant corrigé est nul

Dim j As Integer
With ThisWorkbook.Sheets("LOYERS")
'Précisez le nom de votre feuille
For j = .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" & j).Value = "SUPPRIMES" Then
.Rows(j).Delete
End If
Next j
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("D15100"), Type:=xlFillDefault
Range("D15100").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("P15100"), Type:=xlFillDefault
Range("P15100").Select
Dim k As Integer
With ThisWorkbook.Sheets("LOYERS")
'Précisez le nom de votre feuille
For k = .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" & k).Value = "SUPPRIMER" Then
.Rows(k).Delete
End If
Next k
End With
Range("L15:L100").Select
Selection.ClearContents
'Sheets("RELAIS").Select
'ActiveWindow.SelectedSheets.Visible = False
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