Ignorer les retours à la ligne dans la méthode Find

Le 17/02/2023 à 17:50

Bonjour,

Bon tout d'abord je ne suis pas un pro de la VBA mais j'ai pas mal progressé et j'aime apprendre :).

Voici mon problème:

J'ai créé un UserForm avec 1 ComboBox1 et 2 TextBox2 et 3 :

- la ComboBox1 va chercher une cellule avec un texte "XYZ" dans un tableau excel,

- les TextBox2 et 3 sont complétées à la main,

- le bouton Ok va permettre de transférer les infos des TextBox 2 et 3 dans des cellules définies de la colonne "XYZ" du tableau excel. Les bonnes cellules à compléter sont trouvées via l'utilisation de la méthode Find / offset sur le nom "XYZ".

Bref tout ça fonctionne sauf dans un cas --> si j'ai un saut de ligne dans le texte "XYZ"

En effet, le texte affiché dans la ComboBox1 inclus un symbole "^p" ou retour chariot ou retour à la ligne qui n'est pas reconnu par la méthode Find.

Lorsque j'enlève le saut de ligne dans ma cellule "XYZ", tout fonctionne.

Par soucis de "chipotage" et si ça existe, j'aimerais savoir comment je peux ignorer le saut de ligne en utilisant la même méthode Find.

Je tiens à préciser que cette méthode doit être applicable sur une plage fixe X2:BN2, et que le saut de ligne est aussi variable d'une cellule "XYZ" à une autre.

NB: je n'ai pas parlé de la TextBox4 pour ne pas embrouiller, mais c'est la même chose.

Deuxième point, si vous avez besoin d'un fichier test je dois en créer un car celui-ci est confidentiel.

EDIT : je viens de penser à l'opérateur LIKE, bon je continue de chercher pour tester.

En attendant, voici le code pour aller chercher le texte de la cellule "XYZ":

Private Sub UserForm_Initialize()
i = 24
Do While Cells(19, i).Value <> ""
If Cells(23, i).Value = "OK" Then --> si la ligne 23 comporte pour une colonne donnée le mot "OK", charger le nom dans la ComboBox1
Cells(19, i).Select
Colonne = ActiveCell.Column

UserForm1.ComboBox1.AddItem Cells(19, Colonne).Value --> nom chargé dans la ComboBox1 --> définir format ?
Colonne = Colonne + 1

End If
i = i + 1
Loop

End Sub

Voici le code du transfert:

CommandButton2 = bouton OK

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:="MDP"
If TextBox1 <> "" And TextBox2 <> "" Then
If (val(TextBox1) > val(TextBox2)) Or (val(TextBox2) < val(TextBox1)) Then
MsgBox "Erreur de saisie"
ElseIf ComboBox1 <> "" And TextBox1 <> "" And TextBox2 <> "" And TextBox4 <> "" Then
With Sheets("Tableau de saisie").Range("X2:BN2").Find(ComboBox1).Select --> ne marche pas si ^p
Selection.Offset(2, 0).Select
Selection.Value = val(Replace(TextBox1.Value, ",", "."))
End With
With Sheets("Tableau de saisie").Range("X2:BN2").Find(ComboBox1).Select --> ne marche pas si ^p
Selection.Offset(1, 0).Select
Selection.Value = val(Replace(TextBox2.Value, ",", "."))
End With
ActiveSheet.Range("X2:BN2").Find(ComboBox1).Select --> ne marche pas si ^p
Selection.Offset(3, 0).Select
Selection.Value = TextBox4
ActiveSheet.EnableSelection = xlNoRestrictions
ActiveSheet.Protect Password:="MDP", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End If
End If
End Sub

Merci !

Le 17/02/2023 à 18:50

Bonsoir Arkadia73

Petit rappel de la charte :

  • Pour plus de lisibilité, utilisez la fonctionnalité </> pour insérer vos codes VBA (et si possible aussi pour vos formules Excel).

Merci d'éditer votre post et d'utiliser le bouton

image

Pour mettre votre code entre balises SVP

A+

Le 18/02/2023 à 10:50

Bonjour,

Oups merci Bruno !

Voici les codes avec les balises:

Code pour aller chercher le texte de la cellule "XYZ"

Private Sub UserForm_Initialize()
i = 24
Do While Cells(19, i).Value <> ""
If Cells(23, i).Value = "OK" Then  'si la ligne 23 comporte pour une colonne donnée le mot "OK", charger le nom dans la ComboBox1'
Cells(19, i).Select
Colonne = ActiveCell.Column

UserForm1.ComboBox1.AddItem Cells(19, Colonne).Value 'nom chargé dans la ComboBox1 --> définir format ?'
Colonne = Colonne + 1

End If
i = i + 1
Loop

End Sub

Code du transfert

Rappel : CommandButton2 = bouton OK

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:="MDP"
If TextBox1 <> "" And TextBox2 <> "" Then
If (val(TextBox1) > val(TextBox2)) Or (val(TextBox2) < val(TextBox1)) Then
MsgBox "Erreur de saisie"
ElseIf ComboBox1 <> "" And TextBox1 <> "" And TextBox2 <> "" And TextBox4 <> "" Then
With Sheets("Tableau de saisie").Range("X2:BN2").Find(ComboBox1).Select 'ne marche pas si ^p'
Selection.Offset(2, 0).Select
Selection.Value = val(Replace(TextBox1.Value, ",", "."))
End With
With Sheets("Tableau de saisie").Range("X2:BN2").Find(ComboBox1).Select 'ne marche pas si ^p'
Selection.Offset(1, 0).Select
Selection.Value = val(Replace(TextBox2.Value, ",", "."))
End With
ActiveSheet.Range("X2:BN2").Find(ComboBox1).Select 'ne marche pas si ^p'
Selection.Offset(3, 0).Select
Selection.Value = TextBox4
ActiveSheet.EnableSelection = xlNoRestrictions
ActiveSheet.Protect Password:="MDP", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End If
End If
End Sub

Cordialement !

Le 18/02/2023 à 14:29

Bonjour Arkadia73, BrunoM45,

J'ai un truc clean à te proposer...

Modifier le code ainsi

UserForm1.ComboBox1.AddItem WorksheetFunction.Clean(Cells(19, Colonne).Value)

Ce qui fait que ton Combobox ne prends pas les caractères de contrôle du texte.

C'est l'équivalent de la formule Epurage du Menu de formules texte d'Excel.

Le 19/02/2023 à 12:20

Hello X Cellus,

Alors déjà merci pour cette info, je ne connaissais même pas la formule épurage ! Malheureusement, si j'utilise le code tel quel, mon saut de ligne devient un espace en moins et du coup les deux lignes sont collées l'une à l'autre, la méthode Find échoue une nouvelle fois, je me demande alors s'il ne faut pas Clean la cible du tableau excel que je veux rechercher, est-ce que le Clean va se voir sur ma cellule "XYZ" (soit ligne 1 et 2 collées à la suite ?).

Merci !

Le 19/02/2023 à 14:56

bonjour x Cellus,BrunoM45,Arkadia73,

Excel2007 est assez vieux, trop vieux pour la fonction "agregate", c'est encore excel2007 ? Autrement, ceci a besoin de quelque lignes supplémentaires.

Sub Cherchez_Avec_vblf()
     Dim c, sRecherche, s

     s = "ABC" & vbLf & "DEF"     'string avec vblf que vous cherchez
     sRecherche = Replace(s, vbLf, "|")     'string dans lequel le vblf est remplacé par "|"

     Set c = Range("G8").CurrentRegion 'votre plage (éventuellement dans une autre feuille)
     For i = 1 To 10
     s = Replace(Replace(Replace("=AGGREGATE(15,6,IF(SUBSTITUTE(#,CHAR(10),""|"")=@,ROW(#) + COLUMN(#)/1000),§)", "#", c.Parent.Name & "!" & c.Address), "@", """" & sRecherche & """"), "§", i) 'formule pour la première cellule en occurence
     x = Evaluate(s) 'evaluer

     If IsNumeric(x) Then 'résultat est numerique = trouvé
          sp = Split(Replace(Format(x, "0.000"), ",", "."), ".") 'pour les 2 cas, virgule ou point
          Set c1 = Sheets(c.Parent.Name).Cells(--sp(0), --sp(1)) 'votre cellule
          MsgBox "Addresse cellule " & i & " : " & c1.Address
          Application.Goto c1, 1
     Else
          MsgBox "il n'y a pas une " & i & "ème cellule"
          Exit For
     End If
     Next
End Sub

La cellule B6 montre la même formule pour la première occurence et est okay pour les premières 999 colonnes.

11arcadia73.xlsb (37.31 Ko)
Le 19/02/2023 à 15:20

re, si vous cherchez dans une ligne c'est même plus facile avec la macro "une_ligne"

13arcadia73.xlsb (38.51 Ko)
Sub Une_ligne()
     Set c = Range("D1:AK1")     'votre plage

     For Each charactère In Array(vbLf, ".", ",", "")     'boucle cherchez pour un vblf, un point, une virgule ou rien
          s = Replace("ABcXdef", "X", CStr(charactère), 1)
          r = Application.Match(s, c, 0)
          If IsNumeric(r) Then
               Set c1 = Cells(c.Row, c.Column + r - 1)
               Application.Goto c1
               MsgBox "cellule " & c1.Address & vbLf & c1.Value & vbLf & "pour " & IIf(charactère = vbLf, "vblf", charactère)
          Else
               MsgBox "pour " & charactère & ", rien trouvé"
          End If
     Next
End Sub
Le 19/02/2023 à 16:00

Hello BsAlv,

Merci pour votre réponse, il est vrai que je n'ai pas précisé la version, je suis sur office 365 version Excel ci-dessous:

Microsoft® Excel® pour Microsoft 365 MSO (Version 2211 Build 16.0.15831.20220) 32 bits

Par contre à première vue, ce code dépasse toute mes compétences (et donc compréhension ^^'), je vais me pencher tranquillement dessus et reviendrai vers vous si question, en attendant s'il y a possibilité de faire plus simple n'hésitez pas !

Cordialement !

Arka

Le 19/02/2023 à 16:26

bonjour Arka, pourtant ma dernière réaction est assez simple. Supposons que vous avez 2 textes "abc" et "def" (pour le moment majuscules et miniscules n'ont pas d'importance) que vous voulez trouver et il y a 4 séparateurs possible : une virgule, un point, rien du tout et un vblf ou vbCrLf (vos retour chariot ou retour).

Cette macro joint les 2 textes avec le séparateur au milieu et cherche le résultat dans votre plage.

Le 19/02/2023 à 18:14

Bonjour Arkadia73

Arkadia73 a écrit :

Merci pour votre réponse, il est vrai que je n'ai pas précisé la version, je suis sur office 365 version Excel ci-dessous:

Microsoft® Excel® pour Microsoft 365 MSO (Version 2211 Build 16.0.15831.20220) 32 bits

Alors, merci de changer la version dans votre profil SVP
https://www.excel-pratique.com/fr/compte/forum-profil

A+

Le 19/02/2023 à 18:27

A nouveau,

Merci de suivre d'abord le conseil de BrunoM45,

Ensuite placer dans un fichier Excel d'une feuille, réalisé pour l'occasion un exemple de texte ou plus de cellule(s) de ton tableau.

Voire, modifie en le texte mais tout en gardant le ^p

Cela ira plus vite pour te répondre au mieux.

Le 20/02/2023 à 12:21

Bonjour à tous !

Profil mis à jour, j'ai créé un fichier Excel "explicatif" sur l'objectif de ma demande.

En attendant, j'ai simulé le retour à la ligne par des espaces entre les deux lignes des cellules concernées dans mon tableau excel, et ça fonctionne, mais s'il existe un moyen plus propre je suis preneur.

Cordialement !

21fichierarkatest1.xlsm (21.32 Ko)
Le 20/02/2023 à 13:01

Bonjour Arkadia73,

En retour le fichier modifié. Clic sur le bouton pour lancer le formulaire test réalisé.

La formule Replace a été utilisée.

28fichierarkatestmodif.xlsm (27.63 Ko)
Le 20/02/2023 à 15:38

bonjour,

plutôt le même idée, replacer le vblf par quelque chose rare (comme un "~") et après faire l'inverse

Sub teste()
     '********************************************************************************************************
     'l'idée est qu'on replace le vblf par un charatère, par exemple un "~" pour le combobox et puis l'inverse pour le coller dans une autre cellule
     '********************************************************************************************************
     With Range("A1:F1")     'votre plage

          'methode 1
          .Name = "test"
          a1 = [SUBSTITUTE(test,CHAR(10),"~")]  'une matrice (base 1 !) qu'on peut utiliser pour votre combobox
          'ComboBox1.List = a1 'par exemple

          For i = 1 To UBound(a1)
               Cells(12, i).Value = Replace(a1(i), "~", vbLf)     'copier et coller les valeurs dans ligne 12
          Next

     'methode 2
          a2 = Application.Transpose(Application.Transpose(.Value))     'matrice (base 1) avec vos données
          sp = Split(Replace(Join(a2, "|"), vbLf, "~"), "|")     'matrice (base 0) avec vos données modifiés, prêt pour votre combobox
          'ComboBox1.List = sp'par exemple

          For i = 0 To UBound(sp)
               Cells(13, i + 1).Value = Replace(sp(i), "~", vbLf)     'copier et coller les valeurs dans ligne 12
          Next

     End With
End Sub
Le 21/02/2023 à 10:32

Bonjour à tous !

Finalement j'ai tout simplement redimensionné mes cellules pour avoir seulement un espace entre ma première ligne et la seconde. Code trop complexe pour la finalité attendue et mon niveau. Désolé, après tous ces codes pourront servir à d'autres ^^.

Merci !

Cordialement !

Arka

Rechercher des sujets similaires à "ignorer retours ligne methode find"