Une boucle pour incrémenter une variable.

cibleo · 13 Avril 2010

Bonjour le forum,

Feuille "Clients", colonne A, j'ai une liste de noms.

Feuille "Planning", je peux retrouver ces noms dans la plage (B4:L4) parmi d'autres.

Dans mon exemple, m'intéresse seulement ceux-ci : Mme Olivaux, Mr Brisevin, Mr Leclerc puisqu'ils sont les seuls à figurer en colonne A feuille Client.

J'aimerais donc les retrouver dans ma variable, comme ceci : Mme Olivaux, Mr Brisevin, Mr Leclerc,

Autre problème : il ne doit pas avoir de doublons dans la variable, même si l'on retrouve 2 fois le nom dans la plage (B4:L4).

Je vous présente le code que j'ai essayé de créer, ça cloche 🙄

Code:

Sub MaVar_A_Incrementer()
Dim Mavar As String
Dim rng1 As Range, rng2 As Range
  Set rng1 = Sheets("Clients").Range("A1:A8")
  Set rng2 = Range("B4:L4")
 
  For Each x In rng1     ' pour chaque cellule de rng1
    With Sheets("planning")
      Set c = rng2.Find(what:=x, LookIn:=xlValues)   ' trouver le premier dans rng2
      If Not c Is Nothing Then
        premier = c.Address
        Do
          Mavar = Mavar & "," & " " & x
          MsgBox Mavar
          Set c = rng2.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> premier
      End If
    End With
  Next x
 
End Sub

Cibleo

Papou-net · 13 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonsoir cibleo,,

En modifiant ton code ainsi, tu devrais parvenir au résultat attendu :

Code:

Sub MaVar_A_Incrementer()
Dim Mavar As String
Dim rng1 As Range, rng2 As Range
  Set rng1 = Sheets("Clients").Range("A1:A8")
  Set rng2 = Range("B4:L4")
  
  For Each x In rng1     ' pour chaque cellule de rng1
    With Sheets("planning")
      Set c = rng2.Find(what:=x, LookIn:=xlValues)   ' trouver le premier dans rng2
      If Not c Is Nothing Then
        premier = c.Address
        Do
          [COLOR="Red"][B]If InStr(Mavar, x) = 0 Then
            Mavar = Mavar & "," & " " & x
            MsgBox Mavar
          End If[/B][/COLOR]
          Set c = rng2.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> premier
      End If
    End With
  Next x
End Sub

Espérant avoir répondu.

Cordialement.

cibleo · 17 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonjour le forum 🙂
Bonjour Papou-net,

Je reviens avec le code et la modif apportée par Papounet que je remercie.

Code:

Sub MaVar_A_Incrementer()
Dim Mavar As String
Dim rng1 As Range, rng2 As Range
[COLOR=green]'Set rng1 = Sheets("Clients").Range("A1:A9")[/COLOR]
Set rng1 = Sheets("Clients").Range("A1:A" & Sheets("Clients").Range("A65536").End(xlUp).Row)
[COLOR=green]'Set rng2 = Range("A4:L4")[/COLOR]
  For Each x In rng1     [COLOR=green]' pour chaque cellule de rng1[/COLOR]
    With Sheets("Planning").Range("A4:L4")
      Set c = .Find(what:=x, LookIn:=xlValues)   [COLOR=green]' trouver le premier dans rng2[/COLOR]
      If Not c Is Nothing Then [COLOR=green]'Si C contient quelque chose alors ...[/COLOR]
        premier = c.Address [COLOR=green]'Affecte la première adresse[/COLOR]
        Do
          If InStr(Mavar, x) = 0 Then
            Mavar = Mavar & x & "," & " "
            [COLOR=green]'MsgBox Mavar[/COLOR]
          End If
          Set c = .FindNext(c) 'Cherche le prochain
        Loop While Not c Is Nothing And c.Address <> premier [COLOR=green]'Fait la boucle jusqu'à ce que l'adresse de la recherche soit égale à la première adresse[/COLOR]
       [COLOR=seagreen]'Loop Until c.Address = premier[/COLOR]
      End If
    End With
  Next x
  MsgBox Mavar
  Range("E1").Value = Mavar
End Sub

Dans mon exemple, Mavar est donc égale à :

Mme Olivaux, Mr Brisevin, Mr Leclerc,

J'aimerais arriver à cela maintenant :

Mme Olivaux, Mr Brisevin et Mr Leclerc.

C'est à dire remplacer la dernière virgule par un point et l'avant dernière virgule (si elle est présente) par le terme "et".

En effet, je pourrais avoir seulement ceci :

Mme Olivaux,

qui serait remplacé alors par cela :

Mme Olivaux.

Que dois-je rajouter en bout de code ?

Une autre demande : avec le code, je passe en revue la plage B4:L4 pour détecter la présence des noms figurant dans la colonne A (Feuille "Clients")

Si la plage B4:L4 est vide ma boucle la parcourt quand même, comment en sortir avant, sans tester les cellules 1 par 1.

Cibleo

pierrejean · 17 Avril 2010

Re : Une boucle pour incrémenter une variable.

bonjour Cibleo

Salut Papou-net

pour la presentation de Mavar vois le fichier
Par contre je suis perturbé par le .Find qui recherche un c que l'on n'utilise pas (et uniquement dans A4:L4). En sus il ne trouve que des equivalents et non des valeurs exactes (pour ce faire il faudrait lookat:=xlwhole)
Le mieux serait d'expliciter ce que tu souhaites obtenir

Papou-net · 17 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonjour cibleo,

Bonjour pierrejean, je te remercie pour m'avoir appris la fonction InstrRev, que je ne connaissais pas. J'avais procédé par une boucle de lecture de la fin de Mavar vers le début, et j'en sortais après avoir remplacé la 1ère virgule rencontrée. Ta solution est plus rapide et plus élégante.

Par contre, une petite simplification à apporter dans la définition de Mavar :

Code:

Sub MaVar_A_Incrementer()
...
...
  For Each x In rng1     ' pour chaque cellule de rng1
    With Sheets("Planning").Range("A4:L4")
      Set c = .Find(what:=x, LookIn:=xlValues)   ' trouver le premier dans rng2
      If Not c Is Nothing Then 'Si C contient quelque chose alors ...
        premier = c.Address 'Affecte la première adresse
        Do
          If InStr(Mavar, x) = 0 Then
            [COLOR="Red"][B]Mavar = Mavar & x & ", "[/B][/COLOR]
            'MsgBox Mavar
          End If
          Set c = .FindNext(c) 'Cherche le prochain
        Loop While Not c Is Nothing And c.Address <> premier 'Fait la boucle jusqu'à ce que l'adresse de la recherche soit égale à la première adresse
        'Loop Until c.Address = premier
      End If
    End With
  Next x
...
...

Cordialement.

cibleo · 17 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonjour PierreJean et merci,

C'est parfait comme cela, sauf qu'il me manque le point à la place de la virgule finale.

J'ai mis ta macro dans un module standard.

Ce que je souhaite c'est l'intégrer dans la macro ci-dessous (module : EnvoiMail).

Plus exactement l'imbriquer dans la boucle surlignée en rouge.

Code:

Sub EnvoyerMail()
.../...
Dim Mavar As String
 
'---- Création et envoi message ------------
[COLOR=red]For Each cell In Sheets("MesDestinataires").Range("D2:D8").SpecialCells(xlCellTypeConstants)[/COLOR]
[COLOR=red]If cell.Value Like "*@*" And cell.Offset(0, -3).Value = "x" Then[/COLOR]
        Prenom = cell.Offset(0, -2).Value
        AdresMail = cell.Value
        DJour1 = DateSerial(Year(Date), Month(Date) + 1, 0)
 
        [COLOR=green]'Bloc With... end With de PierreJean[/COLOR]
        [COLOR=red]With ActiveSheet[/COLOR] 'Ici c'est la feuille "01 01 10" qui est la feuille active
        .../...        
        [COLOR=red]End With[/COLOR]

Dans la macro "MaVar_A_Incrementer", on prend B4:L4 comme plage de recherche.

J'aimerais que l'on puisse déterminer la plage de recherche en fonction du chauffeur coché en colonne A feuille "MesDestinataires"

Code:

[COLOR=red]For Each cell In Sheets("MesDestinataires").Range("D2:D8").SpecialCells(xlCellTypeConstants)[/COLOR]
[COLOR=red]If cell.Value Like "*@*" And cell.Offset(0, -3).Value = "x" Then[/COLOR]

Je dois faire ensuite un test à partir de cette variable pour pouvoir finir de composer le message de mon Mail personnalisé (Envoi à une personne précise).

If Mavar <> "" then Msg & "tu vas transporter le(s) client(s) suivant(s) & Mavar & blablabla

Je dois m'absenter, j'essaie de revenir demain.
j'espère ne pas être trop confus dans mes explications.

A+ Cibleo

Ps : Papounet, je regarde ta réponse plus tard.

Pour tester la macro (Sub EnvoyerMail) mettez votre propre adresse email dans D3 feuille "MesDestinataires" et Exécuter.

Puis ouvrez votre boîte mail.

Mais il faut cocher une référence avec la méthode CDO.

pierrejean · 18 Avril 2010

Re : Une boucle pour incrémenter une variable.

Re

Vois en module2 je t'ai créée une fonction Mavar avec comme parametres:
la feuille ou chercher
le prenom
Tu peux la tester avec la sub essai
Et, par pitié ,si tu pouvais eviter de mettre un espace a la fin de tes saisies !!!!

cibleo · 18 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonjour PierreJean,

Du travail d'orfèvre 🙂
Comme je le disais précédemment cette variable doit entrer dans la composition d'un Mail et faire l'objet d'un test (voir ci-dessous)

Code:

.../...
'Call[COLOR=red] Mavar[/COLOR](ActiveSheet, prenom)
 
'Composer le message
Msg = "Bonjour " & prenom & ","
Msg = Msg & vbCrLf & vbCrLf & "Tu trouveras ci-joint le planning du jour." & vbCrLf
Msg = Msg & DebJournee & vbCrLf & vbCrLf
 
If [COLOR=red]Mavar[/COLOR](ActiveSheet, prenom) <> "" _
Then Msg = Msg & "Les Clients suivants : " & [COLOR=red]Mavar[/COLOR](ActiveSheet, prenom) & vbCrLf & vbCrLf
.../...

Je l'appele donc par un Call ?
Mais après, comment dois-je écrire l'instruction du test ?
Ecrit sans arguments, cela provoque une erreur (argument non facultatif) 😱
Avec les arguments, le résultat souhaité est obtenu, mais la fonction est parcouru plusieurs fois 🙄

Cibleo

pierrejean · 18 Avril 2010

Re : Une boucle pour incrémenter une variable.

Re
non pas d'appel par call et
pour eviter de declencher plusieurs fois :

Code:

XX=Mavar(activesheet,prenom)
if XX<>"" then
Then Msg = Msg & "Les Clients suivants : " & XX & vbCrLf
end if

cibleo · 20 Avril 2010

Re : Une boucle pour incrémenter une variable.

Bonjour à tous 🙂
Bonjour Pierrejean, Papou-net

Merci pour toutes ces précisions Pierrejean, ça marche impeccable.
Tout est limpide encore une fois.

A bientôt Cibleo

cibleo · 3 Mai 2010

Re : Une boucle pour incrémenter une variable.

Bonsoir le forum,
Bonsoir Pierrejean

Je reviens sur la fonction personnalisée de pierrejean (post #7#) pour la décliner à nouveau.
Je recherche 2 mots : radios et scanner
Si l'un ou l'autre sont trouvés, Mavar devra alors être affecter comme ci-dessous :
des radios <---- si "radios" est trouvé
un scanner<---- si "scanner" est trouvé
des radios et un scanner <---- si "radios" et "scanner" sont trouvés

rgn2 remplace donc rgn1 et j'élimine ce qui est surligné en rouge.

Mais j'ai une erreur sur la boucle For p... Next p 😱

Code:

Function Mavar(feuille, prenom)
Mavar = ""
[COLOR=red]'rng1 = Sheets("Clients").Range("A1:A" & Sheets("Clients").Range("A65536").End(xlUp).Row)[/COLOR]
[COLOR=blue]rng2[/COLOR] = Array("radios", "scanner")
Set c = feuille.Columns(1).Find(Trim(prenom), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
 For n = 2 To feuille.Cells(c.Row, 256).End(xlToLeft).Column
  If feuille.Cells(c.Row, n) <> "" Then
    For p = LBound([COLOR=blue]rng2[/COLOR], 1) To UBound([COLOR=blue]rng2[/COLOR], 1)
      If InStr(feuille.Cells(c.Row, n), [COLOR=blue]rng2[/COLOR](p, 1)) <> 0 Then
        If InStr(Mavar, [COLOR=blue]rng2[/COLOR](p, 1)) = 0 Then
          Mavar = Mavar & [COLOR=blue]rng2[/COLOR](p, 1) [COLOR=red]& ", "[/COLOR]
        End If
      End If
    Next p
  End If
 Next n
 [COLOR=darkgreen][B]If Mavar <> "" Then[/B][/COLOR]
   Mavar = Trim(Mavar)
  [COLOR=red] Mavar = Left(Mavar, Len(Mavar) - 1)[/COLOR]
    If InStrRev(Mavar, ",") <> 0 Then
      X = InStrRev(Mavar, ",")
      Mavar = Left(Mavar, X - 1) & " et " & Right(Mavar, Len(Mavar) - X - 1)
    End If
  Mavar = Mavar & "."
 [COLOR=darkgreen][B]End If[/B][/COLOR]
End If
End Function

Je patauge aussi avec la manipulation de chaine de caractères, pouvez-vous aussi m'aider à modifier la condition If Mavar.... end If pour affecter ma variable comme énoncé plus haut.

Bonne soirée à tous
Cibleo

cibleo · 5 Mai 2010

Re : Une boucle pour incrémenter une variable.

Bonjour le forum,

La fonction est placée dans le module de pierrejean.
Sur la fin du code, je cherche à convertir le contenu de Mavar et donc modifier le bloc surligné en bleu pour parvenir à ceci.
3 cas peuvent se présenter :

si Mavar = radios ----> Mavar devient des radios
si Mavar = scanner ----> Mavar devient un scanner
si Mavar = radios scanner ----> Mavar devient des radios ou un scanner

Code:

Function Mavar(feuille, prenom)
Mavar = ""
rng2 = Array("radios", "scanner")
Set c = feuille.Columns(1).Find(Trim(prenom), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
 For n = 2 To feuille.Cells(c.Row, 256).End(xlToLeft).Column
  If feuille.Cells(c.Row, n) <> "" Then
    For p = LBound(rng2) To UBound(rng2)
      If InStr(feuille.Cells(c.Row, n), rng2(p)) <> 0 Then
        If InStr(Mavar, rng2(p)) = 0 Then
          Mavar = Mavar & rng2(p) & " "
        End If
      End If
    Next p
  End If
 Next n
 If Mavar <> "" Then
   [B][SIZE=3]Mavar = Trim(Mavar)[/SIZE][/B]
    [COLOR=blue]If InStrRev(Mavar, ",") <> 0 Then[/COLOR]
[COLOR=blue]    X = InStrRev(Mavar, ",")[/COLOR]
[COLOR=blue]    Mavar = Left(Mavar, X - 1) & " et " & Right(Mavar, Len(Mavar) - X - 1)[/COLOR]
[COLOR=blue]   End If[/COLOR]
 End If
End If
End Function

Pouvez-vous m'aider à réaliser cette conversion ?
Cibleo

cibleo · 6 Mai 2010

Re : Une boucle pour incrémenter une variable.

Bonjour le forum,

J'ai essayé ceci mais je n'obtiens pas le résultat souhaité 🙁
Si Mavar = radios scanner (au niveau de la ligne bleue), le 1er If Instr est vrai mais le 2ème If Instr l'est aussi.
J'obtiens alors Mavar = des radios au lieu de Mavar = des radios ou un scanner

Code:

.../...
If Mavar <> "" Then
   [COLOR=blue]Mavar = Trim(Mavar)[/COLOR]
     If InStr(Mavar, "radios scanner") <> 0 Then Mavar = "des radios ou un scanner"
     If InStr(Mavar, "radios") <> 0 Then Mavar = "des radios"
     If InStr(Mavar, "scanner") <> 0 Then Mavar = "un scanner"
End If
.../...

Cibleo

cibleo · 6 Mai 2010

Re : Une boucle pour incrémenter une variable.

Re

J'ai essayé ceci, pas bon non plus 🙁

Code:

If Mavar <> "" Then
   Mavar = Trim(Mavar)
    If InStr(Mavar, " ") <> 0 Then
      X = InStr(Mavar, " ")
      Mavar = " des " & Left(Mavar, X - 1) & " et un " & Right(Mavar, Len(Mavar) - X)
    End If
 End If

Et je m'aperçois que le cas ci-dessous peut se présenter.

Code:

If InStr(Mavar, "scanner radios") <> 0 Then Mavar = "un scanner ou des radios"

Epineux problème à résoudre finalement 😕

Cibleo

pierrejean · 6 Mai 2010

Re : Une boucle pour incrémenter une variable.

Re

Vois si cela te convient

Une boucle pour incrémenter une variable.

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

XLDnaute Impliqué

XLDnaute Impliqué

Pièces jointes

XLDnaute Impliqué

XLDnaute Impliqué

XLDnaute Barbatruc

Pièces jointes

Discussions similaires

Nous accordons de l'importance à votre vie privée