UserForm Selection de plage

Bonjour le Forum,

Je viens de nouveau solliciter votre aide. Je dispose de la macro suivante:

Sub rer()

    Dim S As Integer, nbS As Integer, nbSPrec As Integer, derLig As Integer, i As Integer, f As Worksheet
    Set f = Sheets("Feuil1")

    With f
        'Définit la limite du tableau
       derLig = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 3 To derLig 'Definit la limite du boucle
           S = WorksheetFunction.CountIf(.Range(.Cells(3, 4), .Cells(i, 4)), "Super")

     Next i

    End With

 With f
        derLig = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 3 To derLig 'Definit lignes
           nbS = WorksheetFunction.CountIf(.Range(.Cells(3, 4), .Cells(i, 4)), "Super")
            If nbS = 1 Then
                .Cells(i, 3) = .Cells(i, 3) + (1 / (S * 3))
            'Modif 2ème tache de super
           ElseIf nbS > 1 And nbSPrec < nbS Then
                .Cells(i, 2) = .Cells(i, 2) + (nbSPrec / (S * 3))
                .Cells(i, 3) = .Cells(i, 3) + (nbS / (S * 3))
            End If
            nbSPrec = nbS
        Next i

    End With

    Set f = Nothing

End Sub

Cette macro s'applique de la ligne 3 à la dernière ligne de mon classeur. Mon but c'est de pouvoir choisir manuellement la plage sur laquelle s'applique cette macro.

J'étais parti sur un UserForm avec la fonction "RefEdit" pour pouvoir sélectionner la plage sur laquelle ma macro allait s'appliquer. J'ai donc modifié mon code et obtenu le suivant:

Sub rer()

    Dim S As Integer, nbS As Integer, nbSPrec As Integer, derLig As Integer, i As Integer, f As Worksheet
    Set f = Sheets("Feuil1")

    With f
        'Définit la limite du tableau
       derLig = .Row + .Rows.Count - 1
        For i = .Row To derLig 'Definit la limite du boucle
           S = WorksheetFunction.CountIf(.Range(.Cells(.Row, 4), .Cells(.Row + .Rows.Count - 1, 4)), "Super")

     Next i

    End With

 With f
        derLig = .Row + .Rows.Count - 1
        For i = .Row To derLig 'Definit lignes
           nbS = WorksheetFunction.CountIf(.Range(.Cells(.Row, 4), .Cells(.Row + .Rows.Count - 1, 4)), "Super")
            If nbS = 1 Then
                .Cells(i, 3) = .Cells(i, 3) + (1 / (S * 3))
            'Modif 2ème tache de super
           ElseIf nbS > 1 And nbSPrec < nbS Then
                .Cells(i, 2) = .Cells(i, 2) + (nbSPrec / (S * 3))
                .Cells(i, 3) = .Cells(i, 3) + (nbS / (S * 3))
            End If
            nbSPrec = nbS
        Next i

    End With

    Set f = Nothing

End Sub

Pour le moment ce code ne marche pas, j'ai une erreur de syntaxe que je n'arrive pas à résoudre.

Mon but c'est de remplace mon

i =3

de ma première macro par la première ligne de la plage sélectionnée grâce à l'userForm et de remplacer

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

par la dernière ligne de la plage selectionnée.

Ci-joint mon fichier Excel.

Je vous remercie d'avance de votre aide.

Barrym

18test1.xlsm (23.08 Ko)

Re le forum,

J'ai réussi à lancer la macro avec le code suivant:

Private Sub CommandButton1_Click()

Dim S As Integer, nbS As Integer, nbSPrec As Integer, derLig As Integer, i As Integer, f As Worksheet
Dim zone As String
Dim a As String
Dim b As String

     a = Range(RefEdit1.Value).Row
     b = Range(RefEdit1.Value).Row + Range(RefEdit1.Value).Rows.Count - 1

    Set f = Sheets("Feuil1")

    With f
        'Définit la limite du tableau

        derLig = b

        For i = a To derLig 'Definit la limite du boucle

           S = WorksheetFunction.CountIf(.Range(.Cells(a, 4), .Cells(b, 4)), "Super")

     Next i

    End With

 With f
        derLig = b

        For i = a To derLig 'Definit lignes

           nbS = WorksheetFunction.CountIf(.Range(.Cells(a, 4), .Cells(b, 4)), "Super")
            If nbS = 1 Then
                .Cells(i, 3) = .Cells(i, 3) + (1 / (S * 3))
            'Modif 2ème tache de super
           ElseIf nbS > 1 And nbSPrec < nbS Then
                .Cells(i, 2) = .Cells(i, 2) + (nbSPrec / (S * 3))
                .Cells(i, 3) = .Cells(i, 3) + (nbS / (S * 3))
            End If
            nbSPrec = nbS
        Next i

    End With

    Set f = Nothing

End Sub

Le problème est que la macro (qui est censée agir sur toutes les lignes possédant le mot "Super" sur les colonnes 4) ne fonctionne que sur la première ligne.

Or quand je fais un test avec MsgBox (a) et MsgBox (b), la valeur a indique bien la première ligne sélectionnée et la valeur (b) la dernière. Donc logiquement la macro devrait agir sur toutes les lignes de "a" à "b" possédant le mot "Super" sur la colonne 4.

Auriez-vous une idée d'où pourrait venir le problème?

Ci-joint le nouveau fichier.

Merci d'avance !

Barrym

7test1.xlsm (26.21 Ko)

Bonjour,

Les numéros de ligne sont des entiers, tes variables a et b sont du texte.

Cordialement.

Bonjour MFerrand,

Merci pour ta réponse effectivement tu as raison.

J'ai donc remplacé

dim a as String

et

 dim b as String

par

dim a as Integer

et

dim b as Integer 

Cependant le problème persiste.

Aurais tu une autre idée ?

Je ne comprends pas bien ce que tu fais !

Boucle sur les lignes, de a à b mais tu calcules le nombre de "Super" en colonne D, soit autant de fois le même calcul... !

Même résultat pour S à la fin, mais de toute façon tu l'abandonnes. On se demande le pourquoi de la boucle inutile !

On repart sur une nouvelle boucle, avec le même calcul mais affecté à une autre variable nbS... C'est pas utile de le recalculer à chaque tour, le résultat sera toujours le même.

Les conditions sont curieuses aussi... Si nbS=1 on va servir la colonne C sur tout le parcours, identiquement.

Si nbS >1 ça se corse : au premier tour, nbSPrec non initialisé, est donc = 0 et la condition nbSPrec<nbS sera donc vraie (sauf si aucun "Super"...), les deux conditions étant vraies simultanément on servira sur la ligne a les colonnes B et C.

Mais en fin de tour, on fait nbSPrec = nbS ! nbS ne variant pas, nbSPrec ne variant plus non plus ensuite, la condition sera donc fausse sur tout le reste de la boucle, et donc pas d'autre inscription !

C'est très brouillon et ne permet pas de déduire ce que tu voudrais faire...

Cordialement.

Re,

Désolé pour la clarté de mon code, je débute en VBA j'essaie de me débrouiller du mieux que je peux avec mes connaissances, internet et l'aide du forum !

En fait la macro que je veux créer servira pour un outil de planning sur Excel concernant la construction de bâtiments.

  • Sur la colonne A on aura le nom des tâches (R+1 , R+2, Sous-sol, etc..)
  • Sur la colonne B la date de début de la tâche (en semaines)
  • Sur la colonne C la date de fin de la tâche (en semaines)
  • Sur la colonne D un code pour différencier les tâches entres elles. Par exemple "Super" pour les superstructures, "Infra" pour les infrastructures etc...

Ce planning doit respecter un enchaînement logique des tâches. Par exemple la superstructure ne peut pas commencer tant que les fondations ne sont pas finies. Et ce planning est souvent amené à être modifié.

Ci-joint un exemple de rendu final du planning.

Dans cet exemple, le code "Super" il regroupe un ensemble de 2 tâches (Rez de chaussée et R+1)

Mon 1er but était de pouvoir ajouter ou diminuer des semaines à toutes mes tâche portant le code "Super". La durée ajoutée ou diminuée doit être répartie entre les sous-tâches qui la compose.

Si je veux augmenter le code "Super" (durée de la superstructure) de 2 semaines, je dois augmenter d'une semaine ma tâche "Rez de chaussée" et d'une semaine ma tâche "Superstructure".

La tâche "R+1" doit commencer 1 semaine avant la fin de la Tâche Rez de chaussée dans mon exemple (fichier final).

- La tâche "Rez de chaussée" commence à la semaine 24 et se termine à la semaine 27. En ajoutant 1 semaine, elle commencera à la semaine 24 (pas de modification) et se terminera à la semaine 28 (+1 semaine).

- La tâche "R+1" commencera à la semaine 25 (+1 semaine pour respecter l’enchaînement avec le Rez de chaussée) et se terminera à la semaine 45 (+1 semaine pour respecter sa durée initiale et +1 semaine pour ajouter la semaine supplémentaire)

C'est ce que j'ai voulu automatiser avec ma première macro en utilisant la fonction Countif mais en prenant comme valeur 1/3 mais ça aurait pu etre 2 ou 3.

S permet d'obtenir le nombre total de sous-tâches portant le code "Super" donc ici 3.

J'ai utilisé nbS ensuite pour effectuer la répartition de la durée.

nbs = 1 pour appliquer la condition uniquement sur la première tâche portant le code "Super" car celle-ci n'aura que sa date de fin d'augmenté

nbs > 1 pour toutes les autres tâches du code "Super" car celles-ci auront leur dates de début et de fin de modifiée.

Ensuite nbSPrec pour récupérer la numéro de la tâche avec le code "Super" précédent pour que algorithme fonctionne. (Effectivement je ne l'ai pas définit dans ma macro)

Je sais pas si mon explication est compréhensible. Peut être que tu comprendras mieux avec le fichier final. Je t'invite à modifier manuelle la date de début et de fin des différentes tâches, tu remarqueras que les barres de durée (les couleurs) évoluent en fonction des dates de fin et de début, tu comprendras mieux le sens de ma macro.

Ensuite mon deuxième but était tout simplement de pouvoir choisir la plage dans laquelle ma macro allait s’exécuter grâce à un RefEdit.

Le fichier test 1 n'est qu'un fichier simplifié pour tester la macro.

Ci-joint les 2 fichiers.

Merci.

10test1.xlsm (26.66 Ko)

Le RefEdit est correctement utilisé... Après il y a un peu de mou dans les variables : tu définis 2 variables qui vont te servir à borner les lignes sur lesquelles tu opères. Ces deux variables suffisent pour cela, pas la peine d'en ajouter pour le même rôle et faire DerLigne = b, b suffisait ! Pareil pour le doublé S et nbS...

Et il y a un problème de conception dans l'utilisation de boucle. Tu parcours des lignes : soit pour intervenir sur la ligne selon un critère que tu y trouves et que tu vérifies donc à chaque tour, soit pour intervenir à partir d'un critère défini avant d'entrer dans la boucle et qui conditionne l'intervention sur toutes les lignes. Or tu utilises un critère unique mais que tu recalcules à chaque fois (une fois calculé, il ne bougera plus si les bases de calcul ne sont pas modifiées. On s'y perd !

L'utilisation de RefEdit fait de ta procédure une modification manuelle assistée... C'est toi qui détermine où la procédure va opérer en sélectionnant une plage.

Je suppose donc que en sélectionnant tu connais le résultat que tu dois obtenir !

Pour ajuster la proc. il serait donc bon que tu fournisses le résultat qui doit être obtenu, c'est le moyen simple de savoir où l'on doit aller.

Et ce si tu veux utiliser la même proc. dans diverses situations, il faut les indiquer...

Cordialement.

Re, MFerrand,

Merci pour tes remarques, je comprends ce que tu veux dire. Je vais donc expliciter ce que je souhaite réaliser directement à partir de mon fichier final plutôt qu'à partir d'un fichier test simplifié.

En reprenant depuis le début et en mettant le RefEdit de côté pour le moment, ma macro finalement actuelle est la suivante:

Private Sub CommandButton3_Click()  'Bouton Valider

Dim S As Integer, nbS As Integer, nbSPrec As Integer, derLig As Integer, i As Integer, f As Worksheet

    'modif_duree
    If TextBox1 = "" Then
        Exit Sub
    End If

Set f = Sheets("Planning")
    ''Définir les limites du planning
    DerniereLigne = PlanningCom.Range("A300").End(xlUp).Address
    L = Range(DerniereLigne).Row

'PARTIE 1

    For i = 13 To L

    'Modif tâches principales

     If Cells(i, 1) = "Réception contractuelle" Then
        Cells(i, 2) = Cells(i, 2) + Val(TextBox1)
        Cells(i, 3) = Cells(i, 3) + Val(TextBox1)

     ElseIf Cells(i, 1) = "Corps d'état architecturaux" Then
        Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
        Cells(i, 3) = Cells(i, 3) + Val(TextBox1)

    ElseIf Cells(i, 1) = "Clos / Couvert" Then
        Cells(i, 2) = Cells(i, 2) + ((1 / 3) * Val(TextBox1))
        Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

   'Modif tâches secondaires

    ElseIf Cells(i, 4) = "Terrasses & Maconneries" And Cells(i, 1) <> "GRUE" Then
           Cells(i, 2) = Cells(i, 2) + ((1 / 3) * Val(TextBox1))
           Cells(i, 3) = Cells(i, 3) + ((1 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "GRUE" Then
           Cells(i, 3) = Cells(i, 3) + ((1 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Travaux de façade" Then
           Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
           Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Hors d'eau / hors d'air" Then
           Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
           Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Logement technique" Then
           Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
           Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Logement témoin" Then
           Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
           Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Cloisons / Doublages" Then
        Cells(i, 2) = Cells(i, 2) + ((2 / 3) * Val(TextBox1))
        Cells(i, 3) = Cells(i, 3) + ((2 / 3) * Val(TextBox1))

    ElseIf Cells(i, 1) = "Appareillages" Then
        Cells(i, 2) = Cells(i, 2) + Val(TextBox1)
        Cells(i, 3) = Cells(i, 3) + Val(TextBox1)

    ElseIf Cells(i, 4) = "PC & Aménagements extérieurs" Then
        Cells(i, 2) = Cells(i, 2) + Val(TextBox1)
        Cells(i, 3) = Cells(i, 3) + Val(TextBox1)

    End If

Next i

'PARTIE 2

    Set f = Sheets("Planning")
    With f
    DerniereLigne = PlanningCom.Range("A300").End(xlUp).Address
    L = Range(DerniereLigne).Row

    For i = 13 To L
    S = WorksheetFunction.CountIf(.Range(.Cells(13, 4), .Cells(i, 4)), "Super")

      Next i

    End With

    With f
    DerniereLigne = PlanningCom.Range("A300").End(xlUp).Address
    L = Range(DerniereLigne).Row
   For i = 13 To L

   nbS = WorksheetFunction.CountIf(.Range(.Cells(13, 4), .Cells(i, 4)), "Super")
            If nbS = 1 Then
                .Cells(i, 3) = .Cells(i, 3) + ((1 / (S * 3)) * Val(TextBox1))
            'Modif 2ème tache de super
           ElseIf nbS > 1 And nbSPrec < nbS Then
                .Cells(i, 2) = .Cells(i, 2) + ((nbSPrec / (S * 3)) * Val(TextBox1))
                .Cells(i, 3) = .Cells(i, 3) + ((nbS / (S * 3)) * Val(TextBox1))
            End If
            nbSPrec = nbS
        Next i

    End With

   Set f = Nothing

Call mep

    TextBox1 = ""
repartition.Hide
End Sub

Cette macro est directement insérée dans un userForm. Le principe de fonctionnement est le suivant:

1 - Lorsqu'on appuie sur le bouton, L'userform apparaît.

2 - Dans cet Userform on indique dans un TestBox une valeur numérique positive ou négative qui représente les semaines qu'on veut ajouter ou diminuer.

3 - Ensuite la macro reprend la valeur du TextBox pour l'ajouter à la durée de mes tâches.

Dans cette PARTIE 1, je pense qu'il n'y a pas de problème, les valeurs renseignées sont Fixes, elles ne sont pas amenées à varier et je pense que la syntaxe est bonne. Le problème comme tu l'as dit vient de la PARTIE 2 de mon code.

Mon but dans cette PARTIE 2 c'est d'Ajouter 1/3 de la valeur du TextBox à ma Superstructure (code Super).

  • Par exemple si on a 5 tâches avec le code Super je doit ajouter à chacune des tache (1 / 3*5) de la valeur du TextBox
  • Si on a 10 tâches avec le code Super, je dois ajouter à chacune des tâches (1/3*10) de la valeur du Textbox.
  • Pour S tâches avec le code Super je dois ajouter à chacune des tâches (1/3*S) de la valeur du Textbox.

Je suis donc parti sur le principe suivant:

1 - Compter le nombre de tâches avec le code Super à l'aide de CountIf

2 - UNIQUEMENT dans la première tâche portant le code Super, modifier sa date de fin (la colonne 3) de celle-ci. D'où

If nbS = 1 Then
                .Cells(i, 3) = .Cells(i, 3) + ((1 / (S * 3)) * Val(TextBox1))

3 - POUR TOUTES les autres tâches portant le code Super suivantes (de la 2ème jusqu'à la dernière) modifier la date de début (colonne 2) et la date de fin (colonne 3) d'où:

ElseIf nbS > 1 And nbSPrec < nbS Then
                .Cells(i, 2) = .Cells(i, 2) + ((nbSPrec / (S * 3)) * Val(TextBox1))
                .Cells(i, 3) = .Cells(i, 3) + ((nbS / (S * 3)) * Val(TextBox1))
            End If
            nbSPrec = nbS

Pour que l'algorithme permette de conserver l’enchaînement logique des tâches, nbSPrec doit être également au numéro de la tâche super précédente.

  • Si on est à la 5ème tâche super, nbSPrec = 4
  • Si on est à la 6ème tâche super, nbSPrec = 5
  • Si on est à la N ème tâche super, nbSPrec = N-1

avec S le nombre total de tâches portant le code Super.

C'est ce que j'ai voulu appliquer avec la partie 2 de ma macro mais effectivement je n'avais pas définit ma variable nbSPrec.

J'ai essayé des remplacer nbSPrec par la syntaxe suivante

ElseIf nbS > 1 Then
                .Cells(i, 2) = .Cells(i, 2) + ((nbS -1 / (S * 3)) * Val(TextBox1))
                .Cells(i, 3) = .Cells(i, 3) + ((nbS / (S * 3)) * Val(TextBox1))
            End If

Mais le résultat obtenu n'est pas le bon.

Si on reprend mon fichier simplifié "test1" en lançant la macro, on est supposé obtenir les résultat de l'image 2. (cf fichiers joints).

La totalité de mon code s'est basé sur le principe suivant:

Parcourir toutes les lignes de mon classeur de la 13 ème lignes à la dernières et pour chaque ligne vérifier si une des condition de ma macro est remplie pour l'appliquer.

Ci-joint mon fichier simplifié "test1", mon fichier final "Planning client amélioré" les image illustrant le résultat attendu.

En espérant avoir été un peu plus clair.

Je te remercie d'avance.

Barrym

1 2
18test1.xlsm (26.26 Ko)

Re,

Pour ceux qui aurait le même problème que moi, la macro suivante que j'ai intégrée dans un UserForm pourra sans doute résoudre votre problème.

Private Sub CommandButton3_Click()  'Bouton Valider

    'modif_duree
    If TextBox1 = "" Then
        Exit Sub
    End If

Dim S As Integer, derLig As Integer, i As Integer, f As Worksheet, a As Integer, r As Range, nbOccurence As Variant, address As String, lignePrec As Integer

    Set f = ActiveWorkbook.Sheets("Feuil1")

    With f

        derLig = f.Cells(Rows.Count, 4).End(xlUp).Row
        nbOccurence = Evaluate("COUNTIF(D2:D" & derLig & ",""Super"")")
    'Compte le nombre total de tâches avec le code Super

        Set r = f.Range("D1:D" & derLig).Find("Super")

        f.Cells(r.Row, 3) = f.Cells(r.Row, 3) + ((1 / (nbOccurence * 3)) * Val(TextBox1))

        lignePrec = 1

       'On garde en mémoire l'adresse de la première ligne
       address = r.address
        Set r = f.Range("D1:D" & derLig).FindNext(r)

         If Not r Is Nothing Then

                Do

                    f.Cells(r.Row, 2) = f.Cells(r.Row, 2) + ((lignePrec / (nbOccurence * 3)) * Val(TextBox1))
                    f.Cells(r.Row, 3) = f.Cells(r.Row, 3) + (((lignePrec + 1) / (nbOccurence * 3)) * Val(TextBox1))

                    'On récupère le numéro de la ligne actuel
                    lignePrec = lignePrec + 1
                    'On passe à la ligne suivante
                    Set r = f.Range("D1:D" & derLig).FindNext(r)

                'Et on fait ceci tant que l'on a des ligne a étudié ou tant que l'on est pas retourné à la première ligne étudiée
                Loop While Not r Is Nothing And r.address <> address

        End If
    End With

    TextBox1 = ""
repartition.Hide
End Sub

Barrym

Rechercher des sujets similaires à "userform selection plage"