Opérations dans les colonnes d'une feuille Excel - VBA

Version imprimable

Bonjour a tous
j'ai besoin de votre aide pour résoudre un problème qui me préoccupe.
voici mon problème:

soit la colonne J d'une feuille de calcul Excel contenant les valeurs suivantes: 1, 7, 12, 17, 20 et une ligne de sous-total dont la valeur est égal a 57.
soit une variable Resultat de type Double et une variable Ligne de type Long représentant les ligne de la feuille de calcul excel.
on se propose de soustraire la variable Resultat dont la valeur est 9 au plus grand élément de la colonne J.
Il faut signaler que mon fichier comporte plusieurs plages semblables a celle décrite plus haut mais de taille différente et separees par 02 lignes entières vides.
ceci n’étant qu'un cas particulier, cela est très évident il suffit de faire l’opération : 20-9. mais si on décide de généraliser, cela devient un peu compliqué a mon sens puisque cela ne marche pas.
voici un bout de code :
Code:

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 Dim Resultat as Double, Ligne as Long, DerniereLigne as Double, MaxColonneJ as Double Dim O as Worksheets Resultat = 9 Ligne = 3 Set O = Worksheets("Feuil5") DerniereLigne = O.Range("A"&Ligne).Find("*",,,,,XlPrevious).Row For Ligne = 3 To DerniereLigne MaxColonneJ = WorksheetFunction.Max(Range("J"&Ligne)) If Range("A"&Ligne)<>"" Then If Range("J"&Ligne)<>"" Then If Resultat <>"" Then If Resultat < MaxColonneJ Then Range("M"&Ligne) = MaxColonneJ - Resultat End If End If End If End If Next Ligne

09/07/2020, 16h44
mat955

Bonjour,

Je ne comprends pas l'intérêt de faire une boucle si tu utilises la fonction MAX.

A mon sens, c'est la bonne fonction à utiliser, sauf qu'il faut lui donner une plage et non une cellule comme dans ton code.

C'est comme si sur Excel tu faisais = MAX(A1), cela te renverra toujours la valeur de A1.
Alors que si tu fais = MAX(A1:A1000), là tu obtiens la plus grande valeur, et il n'y a pas besoin de boucle.

Code:

MaxColonneJ = WorksheetFunction.Max(Range(Range("J"&Ligne), Range("J"&DerniereLigne)))
09/07/2020, 20h05
JeanDechanel

Réponse a MAT955

salut Mat955 et merci pour ton aide.
J'ai mis une boucle parce-que la feuille de calcul comporte en réalité plusieurs plages semblables à celle de l'exemple mais de hauteurs différentes et séparées par 02 lignes entières vides. Donc il faut pour utiliser ta formule créer une autre variable Derlig de type Double et la faire varier a chaque changement de plage et je crois que c'est là où ça coince pour moi.

Bonjour,

Essayez ceci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
    Dim Resultat As Double, Ligne As Long, DerniereLigne As Double, MaxColonneJ As Double
    Dim O As Worksheet
 
    Resultat = 9
    Ligne = 3
 
    Set O = Worksheets("Feuil5")
    DerniereLigne = O.Range("A" & Rows.Count).End(xlUp).Row
 
    For Ligne = 3 To DerniereLigne
        MaxColonneJ = WorksheetFunction.Max(Range("J" & Ligne))
        If Range("A" & Ligne) <> "" And Range("J" & Ligne) <> "" And Resultat < MaxColonneJ Then Range("M" & Ligne) = MaxColonneJ - Resultat
    Next Ligne

Cdlt

10/07/2020, 13h18
JeanDechanel

Reponse a ARTURO83

Bonjour ARTURO83

la ligne de code

Code:

DerniereLigne = O.Range("A" & Rows.Count).End(xlUp).Row

recupere plutôt la dernière ligne de la feuille 5 et non la dernière ligne de chaque plage puisqu'il y'en a plusieurs.
En plus la ligne de code

Code:

MaxColonneJ = WorksheetFunction.Max(Range("J" & Ligne))

recupere la valeur de chaque ligne et non la valeur maximale de la colonne J.

cordialement.

Ceci alors!

Code:

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
Sub n()
    Dim Resultat As Double, Ligne As Long, DerLig As Double, MaxColonneJ As Double, L As Range
    Dim O As Worksheet
 
    Resultat = 9
    Ligne = 3
 
    Set O = Worksheets("Feuil5")
    DerLig = O.Range("A" & Ligne).End(xlDown).Row
 
    Do While Range("A" & Ligne) <> "" And Range("J" & DerLig) <> ""
        MaxColonneJ = WorksheetFunction.Max(Range(Cells(Ligne, "J"), Cells(DerLig, "J")))
 
        If Resultat < MaxColonneJ Then
            'sur quelle ligne se trouve la valeur?
            Set L = Sheets("Feuil5").Range(Cells(1, "J"), Cells(DerLig, "J")).Find(MaxColonneJ)
            If Not L Is Nothing Then
                Range("M" & L.Row) = MaxColonneJ - Resultat
            End If
        End If
 
        'Plage suivante
        Ligne = DerLig + 3
        DerLig = O.Range("A" & Ligne).End(xlDown).Row
    Loop
End Sub

Cdlt

10/07/2020, 18h04
JeanDechanel

Reponse a ARTURO83
Bonsoir ARTURO83

merci pour ton aide mais ton code de marche pas. le probleme semble provenir de cette ligne de code:
Code:

1 2 3 Set L = Sheets("Feuil5").Range(Cells(1, "J"), Cells(DerLig, "J")).Find(MaxColonneJ) If Not L Is Nothing Then Range("M" & L.Row) = MaxColonneJ - Resultat
car la valeur maximum est bien recuperee et la variable derlig recupere bien la valeur de la dernière cellule
de la premiere plage mais pas des autres plages du fichier.
enfin la boucle appropriee pour mon probleme c'est la boucle For ... Next puisqu'on parcours la colonne J ligne par
ligne.

Cordialement.
10/07/2020, 18h15
ARTURO83

C'est bizarre, j'ai fit un fichier bidon qui devrait ressembler à ce que vous demandez, testez-le et dites-moi ce qui ne vas pas.
Pièce jointe 574818

Salut le fil....
Un truc dans le genre :

Code:

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
36
37
38
39
40
41
42
43
44
45
46
 
Sub test3()
' // Resultat pourrait être un tableau pour y stocker toutes les valeurs
    Dim Resultat As Double, Ligne As Long, LigneDeSomme As Range, MaxColonneJ As Long
    Dim plg As Range, i As Long
 
    Resultat = 0
    Ligne = 1
 
    ' // On définit la plage de recherche
    Set plg = Worksheets("Feuil5").Range("J" & Ligne & ":J" & Range("J" & Rows.Count).End(xlUp).Row)
    ' // The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method.
    ' // If you do not specify values for these arguments the next time you call the method,
    ' // the saved values are used. Setting these arguments changes the settings in the Find dialog box,
    ' // and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.
    ' // To avoid problems, set these arguments explicitly each time you use this method.
 
    ' // On recherche la première somme
    With plg
Debug.Print "Adresse de la plage : " & .Address
        ' // On prends le problème à l'envers
        ' // je recherche la première formule de somme c'est une méthode comme une autre
        Set LigneDeSomme = .Find(What:="=SUM", LookAt:=xlPart, Lookin:=xlFormulas, SearchDirection:=xlNext)
        ' // DerniereLigne peut être  = a Nothing donc tu dois faire un test
        ' // If Not DerniereLigne Is Nothing Then
 
        If Not LigneDeSomme Is Nothing Then
            Dim FirstAddress As String
            FirstAddress = LigneDeSomme.Address
Debug.Print "Première adresse trouvée : " & FirstAddress
 
            Do
                Resultat = LigneDeSomme.Value - WorksheetFunction.Max(Range("J" & Ligne & ":J" & LigneDeSomme.Offset(-1, 0).Row))
Debug.Print "Valeur max de la plage = " & WorksheetFunction.Max(Range("J" & Ligne & ":J" & LigneDeSomme.Offset(-1, 0).Row))
Debug.Print "Résultat de la somme moins la valeur max pour la plage J" & Ligne & ":J" & LigneDeSomme.Offset(-1, 0).Row & " = " & Resultat
                Ligne = LigneDeSomme.Offset(3, 0).Row
                Set LigneDeSomme = .FindNext(LigneDeSomme)
 
            Loop While Not (LigneDeSomme Is Nothing) And FirstAddress <> LigneDeSomme.Address
 
        Else
            MsgBox "Pas de Données à traiter"
        End If
    End With
 
End Sub

C'est une méthode parmi tant d'autres, je recherche une formule de somme dans la colonne "J".
Ca te donnera un aperçu de la méthode pour la recherche.

Bonne prog.

12/07/2020, 12h17
JeanDechanel

Réponse a VALTRASE

Bonjour VALTRASE
J'aime votre approche mais il s'agit plutôt de soustraire la valeur de la variable Resulat a la plus grande valeur de la plage J étant entendu que la ligne de sous-total est exclue de ladite plage.
Ceci dit, le bout de code que j'ai proposé comme exemple ne gère qu'une partie du problème.
Je vous soumet donc l'algorithme complet de mon programme ci-dessous :

ALGORITHME

on declare les sous totaux des plages des colonnes J et K
SousTotal1 pour la colonne J
SousTotal2 pour la colonne K

si SousTotal1 est inferieur a SousTotal2 alors
si SousTotal1 et SousTotal2 sont non nulles alors
on calcule la difference (SousTotal2-SousTotal1) qu'on affecte a la variable Resultat

Si la colonne A est non vide alors

si la colonne K est non vide alors
si Resultat est inferieur ou egal a tous les elements de la plage de la colonne K alors
on recherche la valeur max (MaxColloneK) de cette plage et on soustrait Resultat a cette valeur la (MaxColloneK - Resultat) et on affecte le resultat a la plage correspondante de la colonne M
sinon si Resultat est superieur a tous les elements
de la plage de la colonne K alors
on copie tous les elements de cette plage et on les colle dans la plage correspondante de la colonne M

sinon fin
sinon fin
sinon fin
sinon fin

si SousTotal1 est superieur a SousTotal2 alors
si SousTotal1 et SousTotal2 sont non nulles alors
on calcule la difference (SousTotal1-SousTotal2) qu on affecte a la variable Resultat

si la colonne J est non vide alors
si Resultat est inferieur ou egal aux elements de la plage de la colonne J alors
on recherche la valeur max (MaxColloneJ) de cette plage et on soustrait Resultat a cette valeur max (MaxColloneJ - Resultat) et on affecte le resultat a la plage correspondante de la colonne L
sinon si Resultat est superieur a tous les elements
de la plage de la colonne J alors
on copie tous les elements de cette plage et on les colle dans la plage correspondante de la colonne L

sinon fin
sinon fin
sinon fin
sinon fin

Sinon fin

Nota: le fichier comporte plusieurs plages separees par 02 lignes entieres vides.
Cordialement.

Salut le fil
Oui là les critères ont changés...
Donc regardes si ce bout de code peut te convenir
Tu mets tout dans un module, tu cliques n’importe ou dans la SUB test et tu fais F5

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Option Explicit
 
Sub test()
    Dim rngSt1 As Range, rngSt2 As Range, Inferieur1 As Boolean, Max As Double
    Dim el As Range
    Dim St1 As Double, St2 As Double, Resultat As Double
 
    ' // On renseigne les deux plages de valeurs, en prenant en compte le sous-total
    Set rngSt1 = Range("J1:J" & Range("J" & Cells.Rows.Count).End(xlUp).Row - 1)
    Set rngSt2 = Range("K1:K" & Range("K" & Cells.Rows.Count).End(xlUp).Row - 1)
 
    ' // On renseigne les sommes des plages
    St1 = WorksheetFunction.Sum(rngSt1)
    St2 = WorksheetFunction.Sum(rngSt2)
 
    ' //Si Sous-total 1 < à sous-total 2
    If St1 < St2 And St1 <> 0 And St2 <> 0 Then
        Resultat = St2 - St1
        ' // si Colonne "A" est non vide
        If getLastRC([A:A]) = True Then
            ' // si Resultat est inferieur ou egal a tous les elements de la plage de la colonne K alors
            If Resultat <= WorksheetFunction.Min(rngSt2) Then
                Max = WorksheetFunction.Max(rngSt2)
                For Each el In rngSt2
                    If el = Max Then el.Offset(0, 2).Value = Max - Resultat
                    'Exit For ' // Si plusieurs max identiques acceptés
                Next
            Else
                rngSt2.Offset(0, 2).Value = rngSt2.Value
            End If
        End If
    ElseIf St1 > St2 And St1 <> 0 And St2 <> 0 Then
        Resultat = St2 - St1
        If getLastRC([J:J]) = True Then
            ' // si Resultat est inferieur ou egal a tous les elements de la plage de la colonne J alors
            If Resultat <= WorksheetFunction.Min(rngSt1) Then
                Max = WorksheetFunction.Max(rngSt1)
                For Each el In rngSt1
                    If el = Max Then el.Offset(0, 2).Value = Max - Resultat
                    'Exit For ' // Si plusieurs max acceptés
                Next
            Else
                rngSt2.Offset(0, 2).Value = rngSt2.Value
            End If
        End If
 
    End If
End Sub
 
Function getLastRC(Plage As Range) As Boolean
    Dim C
    Set C = Plage.Find("*", LookIn:=xlValues, searchorder:=xlByRows, SearchDirection:=xlPrevious)
    If Not C Is Nothing Then
         getLastRC = True
    Else
        getLastRC = False
    End If
End Function

13/07/2020, 10h28
JeanDechanel

Reponse a VALTRASE

Bonjour Valtrase

je vous remercie beaucoup pour les efforts que vous fournissez pour m'aider.
j'ai testé votre bout de code mais hélas il ne fonctionne pas parceque les comparaisons dans les plages doivent se faire ligne par ligne et plage par plage. mon fichier comporte plusieurs plages et donc le traitement doit se faire plage par plage et ligne par ligne. je vais donc vous mettre un lien vers mon fichier qui s'appelle "JeanDechanel".
dans ce fichier vous trouverez a la feuille 5 le travail demandé et a la feuille 6 le résultat attendu.
voici le lien: https://drive.google.com/file/d/12gV...ew?usp=sharing

Cordialement.
13/07/2020, 21h12
Valtrase

Citation:

Envoyé par JeanDechanel

Bonjour VALTRASE
J'aime votre approche mais il s'agit plutôt de soustraire la valeur de la variable Resulat a la plus grande valeur de la plage J étant entendu que la ligne de sous-total est exclue de ladite plage.
Ceci dit, le bout de code que j'ai proposé comme exemple ne gère qu'une partie du problème.
Je vous soumet donc l'algorithme complet de mon programme ci-dessous :

ALGORITHME

on declare les sous totaux des plages des colonnes J et K
SousTotal1 pour la colonne J
SousTotal2 pour la colonne K

si SousTotal1 est inferieur a SousTotal2 alors
si SousTotal1 et SousTotal2 sont non nulles alors
on calcule la difference (SousTotal2-SousTotal1) qu'on affecte a la variable Resultat

Si la colonne A est non vide alors

si la colonne K est non vide alors
si Resultat est inferieur ou egal a tous les elements de la plage de la colonne K alors
on recherche la valeur max (MaxColloneK) de cette plage et on soustrait Resultat a cette valeur la (MaxColloneK - Resultat) et on affecte le resultat a la plage correspondante de la colonne M
sinon si Resultat est superieur a tous les elements
de la plage de la colonne K alors
on copie tous les elements de cette plage et on les colle dans la plage correspondante de la colonne M

sinon fin
sinon fin
sinon fin
sinon fin

si SousTotal1 est superieur a SousTotal2 alors
si SousTotal1 et SousTotal2 sont non nulles alors
on calcule la difference (SousTotal1-SousTotal2) qu on affecte a la variable Resultat

si la colonne J est non vide alors
si Resultat est inferieur ou egal aux elements de la plage de la colonne J alors
on recherche la valeur max (MaxColloneJ) de cette plage et on soustrait Resultat a cette valeur max (MaxColloneJ - Resultat) et on affecte le resultat a la plage correspondante de la colonne L
sinon si Resultat est superieur a tous les elements
de la plage de la colonne J alors
on copie tous les elements de cette plage et on les colle dans la plage correspondante de la colonne L

sinon fin
sinon fin
sinon fin
sinon fin

Sinon fin

Nota: le fichier comporte plusieurs plages separees par 02 lignes entieres vides.
Cordialement.

Salut Jean

J'ai besoin de quelques précisions supplémentaires...

Citation:

si la colonne K est non vide alors
si Resultat est inferieur ou egal a tous les elements de la plage de la colonne K alors
on recherche la valeur max (MaxColloneK) de cette plage et on soustrait Resultat a cette valeur la (MaxColloneK - Resultat) et on affecte le resultat a la plage correspondante de la colonne M

Moi j’interprète : Si Résultat est inférieur ou égal au Min("Kx:Ky") c'est bon pour toi ? (Si c'est inférieur au Min() c'est inférieur ou égal à tous)
et quand tu dis :

Citation:

on affecte le resultat a la plage correspondante de la colonne M

moi j'interprète (Cellule qui contient le nombre max ).Offset(0,2).value = résultat c'est bon pour toi aussi ?
14/07/2020, 09h45
JeanDechanel

Réponse a Valtrase

Citation:

Envoyé par Valtrase

Salut Jean

J'ai besoin de quelques précisions supplémentaires...

Moi j’interprète : Si Résultat est inférieur ou égal au Min("Kx:Ky") c'est bon pour toi ? (Si c'est inférieur au Min() c'est inférieur ou égal à tous)
et quand tu dis : moi j'interprète (Cellule qui contient le nombre max ).Offset(0,2).value = résultat c'est bon pour toi aussi ?

Salut,
Ton interprétation est bonne mais il faut garder a l'esprit que les comparaisons doivent se faire ligne par ligne : ex. If Resultat <= Range("K"&ligne) then ... Ici Resultat est comparé a tous les éléments de la première plage de la colonne K et non de toute la colonne K.
Le bout de code que j'ai proposé dans mon premier exposé marche mais Resultat est soustrait plusieurs fois.
Cordialement.
14/07/2020, 21h10
Valtrase
Salut Jean,
Bon j'ai modifié le code pour prendre en compte les plages et les sous totaux.
Dans l'onglet personnel cliques sur [lance le test], et dis moi si c'est le résultat que tu attends.
Le bouton lance la sub : modValtrase.Home() à chaque plage de sous-totaux la sub modValtrase.CalcM() est appelée.
nb Pour l'instant l'EditBox de l'onglet personnel n'est pas opérationnelle à cent pour cent.

Néanmoins si ce n'est pas ce que tu recherche fait comme suit :
1. Fait une copie de la feuille 7
2. Renommes la en feuille 70
3. Insères les bonnes valeurs dans les cellules concernées des deux ou trois premières plages à traitées
en faisant cela je pourrais mieux appréhender ta demande
15/07/2020, 10h57
JeanDechanel

Réponse a Valtrase

Salut Valtrase,
Voici le lien vers le fichier valtrase.xlm : https://drive.google.com/file/d/1Y6P...w?usp=drivesdk

Je vous invite par ailleurs a tester mon programme qui s'appelle RegulAutoPlus pour avoir une idée du problème que je rencontre .

Cordialement
15/07/2020, 12h52
JeanDechanel

Citation:

Envoyé par JeanDechanel

Salut Valtrase,
Voici le lien vers le fichier valtrase.xlm : https://drive.google.com/file/d/1Y6P...w?usp=drivesdk

Je vous invite par ailleurs a tester mon programme qui s'appelle RegulAutoPlus pour avoir une idée du problème que je rencontre .

Cordialement

Autorisation accordée donc lien disponible.
15/07/2020, 14h16
Valtrase

Salut Jean,
J'ai un peu regardé, mais étant au travail je ne peux y consacrer du temps.
Jettes un oeil sur la dernière mouture on ne devrait pas en être loin.

J'attends ton retour
15/07/2020, 16h44
JeanDechanel

Citation:

Envoyé par Valtrase

Salut Jean,
J'ai un peu regardé, mais étant au travail je ne peux y consacrer du temps.
Jettes un oeil sur la dernière mouture on ne devrait pas en être loin.

J'attends ton retour

Bonsoir Valtrase
J'ai jeté un coup d'oeil a ton programme et je constate qu'il reproduit les même erreurs que mon programme RegulAutoPlus.
Voici les améliorations a y apportées :
*Lorsque la variable Resultat est inférieur ou egal a tous les éléments d'une plage, la soustraction doit se faire une seule fois (Vmax-Resultat)
* Lorsque la variable Resultat est supérieur a tous les éléments d'une plage, on fait un copier coller et on s'arrange pour que la valeur de la Variable Resultat soit entièrement retranchée aux éléments de cette plage.
Ex : Resultat-a; (Resultat-a)-b; ((Resultat-a)-b)-c etc jusqu'à ce que Resultat=0. a,b et c étant les éléments de ladite plage.
Merci pour ta sollicitude.

Cordialement
17/07/2020, 19h00
Valtrase
Bonjour Jean,
Désolé pour ce retard, famille oblige.

Je reviens sur la demande car à mon avis il y a problème, dans les premier échange il était demandé si sous total J > sous total K et que Résultat > Max(RngstJ) de copier les données sur la colonne, maintenant il faut déduire (((résultat-a)-b)-c)...
Ensuite quand je regarde l'exemple que tu as envoyé cela ne correspond pas à la demande:roll:
- J'ai mis un fichier vierge pour y mettre des valeurs exemples
- Que fait-on quand plusieurs max dans une colonne
- Si résultat = 100; a=50; b=40; c=30 on le traite comment?
17/07/2020, 23h37
JeanDechanel
Citation:
Envoyé par Valtrase

Bonjour Jean,
Désolé pour ce retard, famille oblige.

Je reviens sur la demande car à mon avis il y a problème, dans les premier échange il était demandé si sous total J > sous total K et que Résultat > Max(RngstJ) de copier les données sur la colonne, maintenant il faut déduire (((résultat-a)-b)-c)...
Ensuite quand je regarde l'exemple que tu as envoyé cela ne correspond pas à la demande:roll:

J'ai mis un fichier vierge pour y mettre des valeurs exemples
Que fait-on quand plusieurs max dans une colonne
Si résultat = 100; 100; b=40; c=30 on le traite comment?
Salut Valtrase,
S'il ya plusieurs max on fait la soustraction une seule fois. Ex Résultat = 20, max1= 19, max2=17, max3=15 on fait 20-19 point final.
Si Resultat=100; a=40; b=30;
On fait successivement:
100-40=60
60-30=30
30-...=0
Si a=100; b=40; c=30 et Resultat= 100
On fait:
100-100=0 point final
Cordialement
18/07/2020, 10h03
JeanDechanel

Réponse a Valtrase
Citation:
Envoyé par Valtrase

Bonjour Jean,
Désolé pour ce retard, famille oblige.

Je reviens sur la demande car à mon avis il y a problème, dans les premier échange il était demandé si sous total J > sous total K et que Résultat > Max(RngstJ) de copier les données sur la colonne, maintenant il faut déduire (((résultat-a)-b)-c)...
Ensuite quand je regarde l'exemple que tu as envoyé cela ne correspond pas à la demande:roll:

J'ai mis un fichier vierge pour y mettre des valeurs exemples
Que fait-on quand plusieurs max dans une colonne
Si résultat = 100; a=50; b=40; c=30 on le traite comment?
Salut Valtrase

Voici le lien vers ton fichier test:

https://drive.google.com/file/d/1_6s...w?usp=drivesdk