funkay91
Messages postés20Date d'inscriptionmercredi 9 novembre 2005StatutMembreDernière intervention22 novembre 2005 9 nov. 2005 à 16:41
Sub essaie()
Dim Cellule As Byte
Dim NbA As Integer
Dim NbB As Integer
Dim NbC As Integer
NbA = 0
NbB = 0
NbC = 0
NbN = 0
Dim Valeur As String
Dim Couleur As Integer
Cells(4, 61) = "nombre de A"
Cells(4, 62) = "nombre de B"
Cells(4, 63) = "nombre de N"
For LinIndex = 6 To 37
With Cells(LinIndex, 3)
Couleur = .Interior.ColorIndex
'MsgBox ("couleur de ligne")
'MsgBox ("la couleur de la ligne est" & Couleur)
End With
If Couleur = -4142 Then
For Colindex = 4 To 59
With Cells(LinIndex, Colindex)
Couleur = .Interior.ColorIndex
'MsgBox ("la clouleur de la cellule" & Couleur)
If Couleur = -4142 Then
Valeur = .Value()
'MsgBox (Luc) If Valeur "AA" Or Valeur "A1" Or Valeur = "A-1A" Or Valeur = "A-2A" Then
NbA = NbA + 1
End If
If Valeur "B1" Or Valeur "BB" Or Valeur = "B 1" Then
NbB = NbB + 1
End If
If Valeur "NN" Or Valeur "CC" Then
NbN = NbN + 1
End If
End If
End With
Next Colindex
'calcul en semaine
calculA = 3 - NbA
calculB = 3 - NbB
calculN = 6 - NbN
If calculA = 0 Then
Cells(LinIndex, Colindex + 1) = "Ok"
ElseIf calculA < 0 Then
Cells(LinIndex, Colindex + 1) = "+" & -calculA & " personne(s)"
Else
Cells(LinIndex, Colindex + 1) = "manque " & calculA
End If
NbA = 0
If calculB = 0 Then
Cells(LinIndex, Colindex + 2) = "Ok"
ElseIf calculB < 0 Then
Cells(LinIndex, Colindex + 2) = "+" & -calculB & " personne(s)"
Else
Cells(LinIndex, Colindex + 2) = "manque " & calculB
End If
NbB = 0
If calculN = 0 Then
Cells(LinIndex, Colindex + 3) = "Ok"
ElseIf calculN < 0 Then
Cells(LinIndex, Colindex + 3) = "+" & -calculN & " personne(s)"
Else
Cells(LinIndex, Colindex + 3) = "manque " & calculN
End If
NbN = 0
End If
If Couleur = 33 Then
For Colindex = 4 To 59
With Cells(LinIndex, Colindex)
Couleur = .Interior.ColorIndex
'MsgBox (Couleur & "est la couleur de la cellule")
If Couleur = 33 Then
Valeur = .Value()
'MsgBox (Luc) If Valeur "AA" Or Valeur "A1" Or Valeur = "A-1A" Or Valeur = "A-2A" Then
NbA = NbA + 1
End If
If Valeur "B1" Or Valeur "BB" Then
NbB = NbB + 1
End If
If Valeur "NN" Or Valeur "CC" Then
NbN = NbN + 1
End If
End If
End With
Next Colindex
jour = Cells(LinIndex, 3)
'MsgBox (jour)
If jour = "S" Then
'MsgBox (NbA)
'calcul le samedi
calculA = 5 - NbA
calculB = 3 - NbB
calculN = 6 - NbN
If calculA = 0 Then
Cells(LinIndex, Colindex + 1) = "Ok"
ElseIf calculA < 0 Then
Cells(LinIndex, Colindex + 1) = "+" & -calculA & " personne(s)"
Else
Cells(LinIndex, Colindex + 1) = "manque " & calculA
End If
NbA = 0
If calculB = 0 Then
Cells(LinIndex, Colindex + 2) = "Ok"
ElseIf calculB < 0 Then
Cells(LinIndex, Colindex + 2) = "+" & -calculB & " personne(s)"
Else
Cells(LinIndex, Colindex + 2) = "manque " & calculB
End If
NbB = 0
If calculN = 0 Then
Cells(LinIndex, Colindex + 3) = "Ok"
ElseIf calculN < 0 Then
Cells(LinIndex, Colindex + 3) = "+" & -calculN & " personne(s)"
Else
Cells(LinIndex, Colindex + 3) = "manque " & calculN
End If
NbN = 0
Else
'calcul du dimanche et jour férié
calculA = 3 - NbA
calculB = 3 - NbB
calculN = 6 - NbN
If calculA = 0 Then
Cells(LinIndex, Colindex + 1) = "Ok"
ElseIf calculA < 0 Then
Cells(LinIndex, Colindex + 1) = "+" & -calculA & " personne(s)"
Else
Cells(LinIndex, Colindex + 1) = "manque " & calculA
End If
NbA = 0
If calculB = 0 Then
Cells(LinIndex, Colindex + 2) = "Ok"
ElseIf calculB < 0 Then
Cells(LinIndex, Colindex + 2) = "+" & -calculB & " personne(s)"
Else
Cells(LinIndex, Colindex + 2) = "manque " & calculB
End If
NbB = 0
If calculN = 0 Then
Cells(LinIndex, Colindex + 3) = "Ok"
ElseIf calculN < 0 Then
Cells(LinIndex, Colindex + 3) = "+" & -calculN & " personne(s)"
Else
Cells(LinIndex, Colindex + 3) = "manque " & calculN
End If
NbN = 0
End If
End If
Next LinIndex
'MsgBox ("NBA=" & NbA)
End Sub
Voila ma macro mais il me reste une question comment faire pour dès qu'il y a un A dans la cellule mon compteur s'incremente sans que je connaisse les valeurs à l'interieur