alléger mon code redondant

Version imprimable

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
Sub test()
 
Dim i As Integer, verif As Integer, plage1 As String
Dim LaDerniere As Integer
 
 
With Worksheets("CUR ASTREINTE")
   LaDerniere = .Range("A" & .Rows.Count).End(xlUp).Row
   verif = 0
   For i = 1 To LaDerniere
         If .Cells(i, 1) = "cccc" Then
            verif = verif + 1
            If verif = 1 Then
               plage1 = .Range("a" & i).Address
               Else
               plage1 = plage1 & "," & .Range("A" & i).Address
            End If
         End If
   Next i
  If Not Range(plage1) Is Nothing Then Range(plage1).Select
  Range(plage1).Interior.ColorIndex = 3
 
End With
 
End Sub
 
Sub test2()
 
Dim i As Integer, verif As Integer, plage1 As String
Dim LaDerniere As Integer
 
 
With Worksheets("CUR ASTREINTE")
   LaDerniere = .Range("A" & .Rows.Count).End(xlUp).Row
   verif = 0
   For i = 1 To LaDerniere
         If .Cells(i, 1) = "dddd" Then
            verif = verif + 1
            If verif = 1 Then
               plage1 = .Range("a" & i).Address
               Else
               plage1 = plage1 & "," & .Range("A" & i).Address
            End If
         End If
   Next i
  If Not Range(plage1) Is Nothing Then Range(plage1).Select
  Range(plage1).Interior.ColorIndex = 4
 
End With
 
End Sub
Sub test3()
 
Dim i As Integer, verif As Integer, plage1 As String
Dim LaDerniere As Integer
 
 
With Worksheets("CUR ASTREINTE")
   LaDerniere = .Range("A" & .Rows.Count).End(xlUp).Row
   verif = 0
   For i = 1 To LaDerniere
         If .Cells(i, 1) = "eeee" Then
            verif = verif + 1
            If verif = 1 Then
               plage1 = .Range("a" & i).Address
               Else
               plage1 = plage1 & "," & .Range("A" & i).Address
            End If
         End If
   Next i
  If Not Range(plage1) Is Nothing Then Range(plage1).Select
  Range(plage1).Interior.ColorIndex = 5
 
End With
 
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sub mafonction(strtext as string,intCOlorIndex)
 
Dim i As Integer, verif As Integer, plage1 As String
Dim LaDerniere As Integer
 
 
With Worksheets("CUR ASTREINTE")
   LaDerniere = .Range("A" & .Rows.Count).End(xlUp).Row
   verif = 0
   For i = 1 To LaDerniere
         If .Cells(i, 1) = strtext  Then
            verif = verif + 1
            If verif = 1 Then
               plage1 = .Range("a" & i).Address
               Else
               plage1 = plage1 & "," & .Range("A" & i).Address
            End If
         End If
   Next i
  If Not Range(plage1) Is Nothing Then Range(plage1).Select
  Range(plage1).Interior.ColorIndex =intCOlorIndex
 
End With
 
End Sub
 
 
 
Sub text1()   
mafonction("aaaaa",2)
end sub
 
Sub test2()
    mafonction("dddd",3)
 
End Sub
Sub test2()
    mafonction("eeee",5)
 
End Sub

16/04/2010, 17h23
adriennoob

merci c'est cool c'est plus gérable maintenant

Salut
Juste pour info tu peux aussi faire comme ca
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub mafonction(strtext as string,intCOlorIndex) Dim plage1 As String Dim TheCel as Range Plage1="" 'Normalement c inutile, les variable chaine sont initialisé a "" lors de leur déclaration With Worksheets("CUR ASTREINTE") For each theCel in .range("A1",.Range("A" & .Rows.Count).End(xlUp)) If thecel = strtext Then if plage1 <> "" then plage1 = plage1 & "," plage1 = plage1 & thecel.Address End If Next If Not Range(plage1) Is Nothing Then Range(plage1).Select Range(plage1).Interior.ColorIndex =intCOlorIndex end if End With End Sub
L'utilisation de For Each allège un peu le code.
Et Verif n'est pas utile puisque tu peu utiliser la valeur de plage1 pour savoir su c'est la 1ere cellule a jouté.
Je n'ai pas excel sous la main, j'ai modifié le code a la volé tu auras peut etre quelques erreur de syntaxe a reprendre.

A++
Qwaz