Recherche dans un champ
Résoluest ce que vous avez une idée comment faire pour chercher un caractere dans une cellule exemple : (Significant* and recent** experience in providing strategic advice, both in writing and orally, to senior management (Director General, DG equivalent or higher) in relation to IM/IT services and solutions.
)
il peut être une (*) où (**) ou les deux en meme temps comme l'exemple ci dessus
2 réponses
La problématique porte sur la détection d'un caractère spécial dans une cellule, notamment l'étoile (*) et la double étoile (**), et sur leur prise en compte pour qualifier les données. Réponse 3 propose une approche VBA testée, intitulée Emails_Screening_Manual, qui parcourt les lignes, repère les *, ** via InStr et construit des messages à partir de données Excel, Word et Outlook. Le code utilise des indicateurs Flag_1etoile et Flag_2etoiles pour activer les définitions correspondantes et alimente les variables de corps des messages en fonction des critères trouvés. En pratique, les échanges indiquent que le code fonctionne et que des ajustements humains pourraient être souhaitables, par exemple pour afficher une définition unique lorsque plusieurs champs contiennent des astérisques.
-
Bonjour,
Formule ou VBA et que voulez-vous faire du resultat ????-
-
@r4944Bonjour,
adaptez cellule et feuille
Sub test()With Worksheets("feuil1") 'une "*" ou deux "**" If InStr(.Range("C3"), "*") > 0 Or InStr(.Range("C3"), "**") > 0 Then 'votre code End If End With End Sub -
-
@r4944Re,
Dans ce cas c'est un and à la place du or et faire ce test avant le test avec or si vous voulez faire une différence -
j'ai fais ça mais j'ai un probléme d'affichage car il m'affiche toujours 2 fois
If Sheets(2).Cells(i, 23 + Exp).Value = "DNM" And InStr(Sheets(2).Cells(3, 23 + Exp), "*") > 0 Then
bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & vbNewLine & " -- " & Sheets(1).Range("B45").Text & "<br/>"
fr(Exp) = fr(Exp) & vbNewLine & " -- " & Sheets(1).Range("E45").Text & "<br/>"
End If
If Sheets(2).Cells(i, 23 + Exp).Value = "DNM" And InStr(Sheets(2).Cells(3, 23 + Exp), "**") > 0 Then
bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & vbNewLine & " -- " & Sheets(1).Range("B46").Text & "<br/>"
fr(Exp) = fr(Exp) & vbNewLine & " -- " & Sheets(1).Range("E46").Text & "<br/>"
End If
-
-
Bonjour,
a tester, ai optimise un tout petit peu:
Sub Emails_Screening_Manual() 'Déclaration des variable '----------------------------------------------------------------------------------------------------------------------------------------- Dim wdDoc As Object Dim wdFileName As Variant Dim ws As Worksheet Dim wb As Workbook Dim WApp As Object Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Dim VAR_Message As String Dim VAR_Message_Eng As String Dim VAR_Message_FR As String Dim i As Integer Dim Nbre_Line As Integer Dim VAR_TO As String Dim VAR_CC As String Dim VAR_BCC As String Dim VAR_Subject As String Dim VAR_Beginning As String Dim VAR_End As String Dim ABody As String Dim qualification_EN As String Dim qualification_FR As String Dim Exp As Byte Dim bodymessage(0 To 9) As String Dim fr(1 To 10) As String Dim Significant_Recent_EN As String Dim Significant_Recent_FR As String Dim significant_FR As String Dim Significant_EN As String Dim Recent_EN As String Dim Recent_FR As String '----------------------------------------------------------------------------------------------------------------------------------------- Application.ScreenUpdating = False wdFileName = UserForm1.txtExcelDatasheet.Value Set wb = ThisWorkbook Set ws = wb.Sheets(1) Set WApp = CreateObject("Word.Application") If wdFileName = False Then Exit Sub Set wdDoc = WApp.Documents.Open(wdFileName) With wdDoc '----------------------------------------------------------------------------------------------------------------------------------------- Nbre_Line = Sheets(2).Range("A456541").End(xlUp).Row 'Boucle pour tous les lignes du feuille numéro 2 '----------------------------------------------------------------------------------------------------------------------------------------- For i = 8 To Nbre_Line 'Initialisation des variables '----------------------------------------------------------------------------------------------------------------------------------------- qualification_EN = "" qualification_FR = "" Erase bodymessage, fr Significant_Recent_EN = "" Significant_Recent_FR = "" Significant_EN = "" significant_FR = "" Recent_EN = "" Recent_FR = "" Dim Flag_1etoile As Boolean Dim Flag_2etoiles As Boolean Dim Cel423 'Condition pour la qualification '----------------------------------------------------------------------------------------------------------------------------------------- If Sheets(2).Cells(i, 44) = "OUT" And Sheets(2).Cells(i, 1) <> "" And Sheets(2).Cells(i, 3).Text Like "?*@?*.?*" Then For Exp = 1 To 10 Cel423 = Sheets(2).Cells(4, 23 + Exp) Select Case Exp Case 1 To 10 'Condition pour les critéres du mérite '----------------------------------------------------------------------------------------------------------------------------------------- If Sheets(2).Cells(i, 23 + Exp).Value = "DNM" Then If InStr(Sheets(2).Cells(3, 23 + Exp), "*") > 0 And Not Flag_1etoile Then Flag_1etoile = True Significant_EN = "<br/>" & Sheets(1).Range("B45").Text & "<br/>" significant_FR = "<br/>" & Sheets(1).Range("E45").Text & "<br/>" End If If InStr(Sheets(2).Cells(3, 23 + Exp), "**") > 0 And Not Flag_2etoiles Then Flag_2etoiles = True Recent_EN = "<br/>" & Sheets(1).Range("B46").Text & "<br/>" Recent_FR = "<br/>" & Sheets(1).Range("E46").Text & "<br/>" End If 'Éducation '------------------------------------------------------------------------------------------------------------------------------ If Cel423 = "Educ" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B19").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E19").Text & "<br/>" 'Éxpériences '------------------------------------------------------------------------------------------------------------------------------ ElseIf Cel423 = "EX1" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B25").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E25").Text & "<br/>" ElseIf Cel423 = "EX2" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B26").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E26").Text & "<br/>" ElseIf Cel423 = "EX3" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B27").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E27").Text & "<br/>" ElseIf Cel423 = "EX4" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B28").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E28").Text & "<br/>" ElseIf Cel423 = "EX5" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B29").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E29").Text & "<br/>" ElseIf Cel423 = "EX6" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B30").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E30").Text & "<br/>" ElseIf Cel423 = "EX7" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B31").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E31").Text & "<br/>" ElseIf Cel423 = "EX8" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B32").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E32").Text & "<br/>" ElseIf Sheets(2).Cells(i, 23 + Exp).Value = "DNM" And Cel423 = "EX9" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B33").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E33").Text & "<br/>" ElseIf Cel423 = "EX10" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B34").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E34").Text & "<br/>" 'Abilities '------------------------------------------------------------------------------------------------------------------------------- ElseIf Cel423 = "A1" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B71").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E71").Text & "<br/>" ElseIf Cel423 = "A2" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B72").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("C72").Text & "<br/>" 'Personal Suitability '------------------------------------------------------------------------------------------------------------------------------- ElseIf Cel423 = "PS1" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B83").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E83").Text & "<br/>" ElseIf Cel423 = "PS2" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B84").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("E84").Text & "<br/>" 'Asset Education '------------------------------------------------------------------------------------------------------------------------------- ElseIf Cel423 = "AED1" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B45").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("C45").Text & "<br/>" ElseIf Cel423 = "AED2" Then bodymessage(Exp - 1) = bodymessage(Exp - 1) & vbNewLine & " - " & Sheets(1).Range("B46").Text & "<br/>" fr(Exp) = fr(Exp) & vbNewLine & " - " & Sheets(1).Range("C46").Text & "<br/>" End If End If End Select Next Exp 'Alimentation des variables qualification_EN & qualification_FR '----------------------------------------------------------------------------------------------------------------------------------------- qualification_EN = bodymessage(0) & bodymessage(1) & bodymessage(2) & bodymessage(3) & bodymessage(4) & bodymessage(5) & bodymessage(6) & bodymessage(7) & bodymessage(8) & bodymessage(9) & Significant_Recent_EN & Significant_EN & Recent_EN qualification_FR = fr(1) & fr(2) & fr(3) & fr(4) & fr(5) & fr(6) & fr(7) & fr(8) & fr(9) & fr(10) & Significant_Recent_FR & significant_FR & Recent_FR 'Alimentation du variable VAR_Message_Eng (la partie anglais du message du corps d'email) '----------------------------------------------------------------------------------------------------------------------------------------- VAR_Message_Eng = "<b>" & wdDoc.Tables(1).Cell(1, 0).Range & "</b>" & "<br/>" & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(2, 0).Range & "</b>" & Sheets(1).Cells(4, 2) & ", " & Sheets(1).Cells(8, 2) & "<br/>" & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(3, 0).Range & "</b>" & Sheets(1).Cells(4, 2) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(4, 0).Range & "</b>" & Sheets(1).Cells(6, 2) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(5, 0).Range & "</b>" & Sheets(1).Cells(7, 2) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(6, 0).Range & "</b>" & Sheets(1).Cells(8, 2) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(7, 0).Range & "</b>" & Sheets(1).Cells(9, 2) & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(8, 0).Range & "<br/>" _ & wdDoc.Tables(1).Cell(9, 0).Range & "<br/>" & "<br/>" _ & "<b>" & qualification_EN & "</b>" & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(11, 0).Range & Sheets(1).Cells(10, 2) & "<br/>" _ & wdDoc.Tables(1).Cell(12, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(13, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(14, 0).Range & Sheets(1).Cells(11, 2) & "<br/>" _ & wdDoc.Tables(1).Cell(15, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(16, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(17, 0).Range & "<br/>" & "<br/>" 'Alimentation du variable VAR_Message_FR (la partie française du message du corps d'email) '----------------------------------------------------------------------------------------------------------------------------------------- VAR_Message_FR = "<b>" & wdDoc.Tables(1).Cell(18, 0).Range & "</b>" & "<br/>" & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(19, 0).Range & "</b>" & Sheets(1).Cells(4, 5) & ", " & Sheets(1).Cells(8, 5) & "<br/>" & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(20, 0).Range & "</b>" & Sheets(1).Cells(4, 5) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(21, 0).Range & "</b>" & Sheets(1).Cells(6, 5) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(22, 0).Range & "</b>" & Sheets(1).Cells(7, 5) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(23, 0).Range & "</b>" & Sheets(1).Cells(8, 5) & "<br/>" _ & "<b>" & wdDoc.Tables(1).Cell(24, 0).Range & "</b>" & Sheets(1).Cells(9, 5) & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(25, 0).Range & "<br/>" _ & wdDoc.Tables(1).Cell(26, 0).Range & "<br/>" & "<br/>" _ & "<b>" & qualification_FR & "</b>" & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(28, 0).Range & Sheets(1).Cells(10, 5) & "<br/>" _ & wdDoc.Tables(1).Cell(29, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(30, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(31, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(32, 0).Range & Sheets(1).Cells(11, 5) & "<br/>" _ & wdDoc.Tables(1).Cell(33, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(34, 0).Range & "<br/>" & "<br/>" _ & wdDoc.Tables(1).Cell(35, 0).Range & "<br/>" & "<br/>" 'Alimentation du variable VAR_Message (pour le corps d'email) '----------------------------------------------------------------------------------------------------------------------------------------- VAR_Message = VAR_Message_Eng & VAR_Message_FR 'Alimentation du variable VAR_Message (pour le corps d'email) '----------------------------------------------------------------------------------------------------------------------------------------- Set OutApp = CreateObject("outlook.application") Set OutMail = OutApp.CreateItem(olMailItem) 'Setting of Email VAR_TO = Sheets(2).Cells(i, 3) VAR_CC = Sheets(1).Cells(13, 2) VAR_BCC = Sheets(1).Cells(14, 2) VAR_Subject = Sheets(1).Cells(4, 2) & ", " & Sheets(1).Cells(8, 2) 'VAR_Beginning = "" 'VAR_End = "" ABody = VAR_Message With OutMail .To = VAR_TO .CC = VAR_CC .BCC = VAR_BCC .Subject = VAR_Subject .HTMLBody = ABody .display '.Save '.Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End If Next i 'Close the Word document with saving wdDoc.Close False Set wdDoc = Nothing 'wdFileName = Dir End With Application.ScreenUpdating = True End Sub-
-
-
@r4944Bonjour,
j'ai pas bien compris le (Cel423) L'interpreteur excel n'a qu'une variable a lire au lieu de "decortiquer"Sheets(2).Cells(4, 23 + Exp)
aussi la défintion d'une seule (*) Ca se corse, mais je regarde
-