Option Explicit
Dim C As Range, i As Byte
Sub EssAi()
For i = 1 To Sheets.Count
For Each C In Sheets("Feuil" & i).Range("C4:C19")
Select Case C.Value
Case "petu": C.Offset(0, -1) = 1
Case "puco": C.Offset(0, -1) = 2
Case "pace": C.Offset(0, -1) = 3
Case "puci": C.Offset(0, -1) = 4
Case Else: C.Offset(0, -1) = ""
End Select
Next C
Next i
End Sub
Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
For i = 4 To 19
If ws.Cells(i, 3) = "petu" Then
ws.Cells(i, 2) = 1
ElseIf Cells(i, 3) = "puco" Then
ws.Cells(i, 2) = 2
ElseIf Cells(i, 3) = "pace" Then
ws.Cells(i, 2) = 3
ElseIf Cells(i, 3) = "puci" Then
ws.Cells(i, 2) = 4
End If
Next i
Next ws
End Sub
Sub Demo()
texte = Array("petu", "puco", "pace", "puci")
For Each sh In Sheets
For Each cel In sh.Range("C:C").SpecialCells(xlCellTypeConstants)
For i = LBound(texte) To UBound(texte)
If cel.Value = texte(i) Then cel.Offset(0, -1) = i + 1
Next
Next
Next
End Sub
Sub test()
Dim ws As Worksheet
texte = Array("petu", "puco", "pace", "puci")
For Each ws In Worksheets
For i = 4 To 19
For j = 0 To UBound(texte)
If ws.Cells(i, 3) = texte(j) Then
ws.Cells(i, 2) = j + 1
End If
Next j
Next i
Next ws
End Sub
If ws.Cells(i, 4) = "toca" Then
ws.Cells(i, 3) = 0
ElseIf Cells(i, 4) = "tora" Then
ws.Cells(i, 3) = 1
If ws.Cells(i, 4) = "TOCA" Then
ws.Cells(i, 3) = 0
ElseIf ws.Cells(i, 4) = "TORA" Then
ws.Cells(i, 3) = 1
en tête de ModuleOption Compare Text
ElseIf ws.Cells(i, 4) = "TORA" Then
Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
For i = 2 To 31
Select Case UCase(ws.Cells(i, 4))
Case "TOCA", "TORA"
ws.Cells(i, 3) = 1
Case "YOLA"
ws.Cells(i, 3) = 2
Case "GEXI"
ws.Cells(i, 3) = 3
Case "GECA", "GEMO", "GECO"
ws.Cells(i, 3) = 4
Case "GUMI", "GIGA", "GETO"
ws.Cells(i, 3) = 5
Case "GUMO", "GOCA"
ws.Cells(i, 3) = 6
Case "TOCA", "GULE"
ws.Cells(i, 3) = 7
Case "TOCA", "YECA"
ws.Cells(i, 3) = 8
Case "MOCA", "MICA", "MIMA"
ws.Cells(i, 3) = 9
Case "PUCI", "PECO", "PUCO"
ws.Cells(i, 3) = 10
Case "PICA"
ws.Cells(i, 3) = 11
Case "PACK"
ws.Cells(i, 3) = 12
Case "PICO"
ws.Cells(i, 3) = 13
Case "POCA", "POCI", "PULE", "PULO", "PUCA", "PICU", "PACA"
ws.Cells(i, 3) = 14
Case "PENA", "PEMA", "PEMI"
ws.Cells(i, 3) = 15
Case "PUCE", "PUMA", "PECI"
ws.Cells(i, 3) = 16
Case "PETO", "PACY", "PENU"
ws.Cells(i, 3) = 17
Case "PACI"
ws.Cells(i, 3) = 18
Case "PACE"
ws.Cells(i, 3) = 19
Case "COFA"
ws.Cells(i, 3) = 20
Case Else
ws.Cells(i, 3) = ""
End Select
Next
Next
End Sub
Sub test()
Dim sh As Byte
For Each ws In Worksheets
If InStr(ws.Name, "à") > 0 Then
For i = 2 To 31
Select Case UCase(ws.Cells(i, 4))
Case "TOCA", "TORA"
ws.Cells(i, 3) = 1
Case "YOLA"
ws.Cells(i, 3) = 2
Case "GEXI"
ws.Cells(i, 3) = 3
Case "GECA", "GEMO", "GECO"
ws.Cells(i, 3) = 4
Case "GUMI", "GIGA", "GETO"
ws.Cells(i, 3) = 5
Case "GUMO", "GOCA"
ws.Cells(i, 3) = 6
Case "TOCA", "GULE"
ws.Cells(i, 3) = 7
Case "TOCA", "YECA"
ws.Cells(i, 3) = 8
Case "MOCA", "MICA", "MIMA"
ws.Cells(i, 3) = 9
Case "PUCI", "PECO", "PUCO"
ws.Cells(i, 3) = 10
Case "PICA"
ws.Cells(i, 3) = 11
Case "PACK"
ws.Cells(i, 3) = 12
Case "PICO"
ws.Cells(i, 3) = 13
Case "POCA", "POCI", "PULE", "PULO", "PUCA", "PICU", "PACA"
ws.Cells(i, 3) = 14
Case "PENA", "PEMA", "PEMI"
ws.Cells(i, 3) = 15
Case "PUCE", "PUMA", "PECI"
ws.Cells(i, 3) = 16
Case "PETO", "PACY", "PENU"
ws.Cells(i, 3) = 17
Case "PACI"
ws.Cells(i, 3) = 18
Case "PACE"
ws.Cells(i, 3) = 19
Case "COFA"
ws.Cells(i, 3) = 20
Case Else
ws.Cells(i, 3) = ""
End Select
Next
End If
Next
End Sub
Option Compare Text ' ne pas tenir compte des majuscules/minuscules
Sub test2()
' PS : COLA n'existe pas dans vos données !! peut être "yola" ?
Dim ws As Worksheet
Dim Ann As Worksheet
Set Ann = Worksheets("Annonces")
Dim tabAnn As Variant
fin = Ann.Range("A65536").End(xlUp).Row
tabAnn = Ann.Range(Ann.Cells(3, 1), Ann.Cells(fin, 3))
For Each ws In Worksheets
If ws.Name <> "Codes Missions" And ws.Name <> "Annonces" Then
finws = ws.Range("D65536").End(xlUp).Row
For i = 2 To finws
For j = 1 To UBound(tabAnn)
If ws.Cells(i, 4) = tabAnn(j, 1) Then
ws.Cells(i, 3) = tabAnn(j, 2)
End If
Next j
Next i
finws = Empty
End If
Next ws
End Sub