Microsoft 365 Loop avec un Select Case

MarcMad · 5 Novembre 2020

Bonjour,

J'ai crée un code qui fonctionne comme je le veux mais il est très lent. Je pense que je devrais crée une loop dedans pour le simplifier mais je ne voit pas comment faire.

Merci !

Voici le code en question :

Public Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim SimpleR, O4O8R, C4C8R, O8O8R, O8C8R, C8C8R, HL1, HL2, HL3, HL4, HL5, HL6, HL7, HL8 As Currency
Dim StartNumber, EndNumber As Integer

Dim Ws As Worksheet

Set Ws = ThisWorkbook.Sheets("Camurlat - Murs")

With Worksheets("Camurlat - Murs")

'---------------------------------------------------------Section_1---------------------------------------------------------------

HL1 = .Range("J11")

Select Case HL1

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F16") = SimpleR
.Range("F17") = O4O8R
.Range("F18") = C4C8R
.Range("F19") = O8O8R
.Range("F20") = O8C8R
.Range("F21") = C8C8R

If SimpleR = 0 Then
.Rows(16).Hidden = True
Else
.Rows(16).Hidden = False
End If

If O4O8R = 0 Then
.Rows(17).Hidden = True
Else
.Rows(17).Hidden = False
End If

If C4C8R = 0 Then
.Rows(18).Hidden = True
Else
.Rows(18).Hidden = False
End If

If O8O8R = 0 Then
.Rows(19).Hidden = True
Else
.Rows(19).Hidden = False
End If

If O8C8R = 0 Then
.Rows(20).Hidden = True
Else
.Rows(20).Hidden = False
End If

If C8C8R = 0 Then
.Rows(21).Hidden = True
Else
.Rows(21).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_2---------------------------------------------------------------

HL2 = .Range("J27")

Select Case HL2

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F32") = SimpleR
.Range("F33") = O4O8R
.Range("F34") = C4C8R
.Range("F35") = O8O8R
.Range("F36") = O8C8R
.Range("F37") = C8C8R

If SimpleR = 0 Then
.Rows(32).Hidden = True
Else
.Rows(32).Hidden = False
End If

If O4O8R = 0 Then
.Rows(33).Hidden = True
Else
.Rows(33).Hidden = False
End If

If C4C8R = 0 Then
.Rows(34).Hidden = True
Else
.Rows(34).Hidden = False
End If

If O8O8R = 0 Then
.Rows(35).Hidden = True
Else
.Rows(35).Hidden = False
End If

If O8C8R = 0 Then
.Rows(36).Hidden = True
Else
.Rows(36).Hidden = False
End If

If C8C8R = 0 Then
.Rows(37).Hidden = True
Else
.Rows(37).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_3---------------------------------------------------------------

HL3 = .Range("J43")

Select Case HL3

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F48") = SimpleR
.Range("F49") = O4O8R
.Range("F50") = C4C8R
.Range("F51") = O8O8R
.Range("F52") = O8C8R
.Range("F53") = C8C8R

If SimpleR = 0 Then
.Rows(48).Hidden = True
Else
.Rows(48).Hidden = False
End If

If O4O8R = 0 Then
.Rows(49).Hidden = True
Else
.Rows(49).Hidden = False
End If

If C4C8R = 0 Then
.Rows(50).Hidden = True
Else
.Rows(50).Hidden = False
End If

If O8O8R = 0 Then
.Rows(51).Hidden = True
Else
.Rows(51).Hidden = False
End If

If O8C8R = 0 Then
.Rows(52).Hidden = True
Else
.Rows(52).Hidden = False
End If

If C8C8R = 0 Then
.Rows(53).Hidden = True
Else
.Rows(53).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_4---------------------------------------------------------------

HL4 = .Range("J59")

Select Case HL4

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F64") = SimpleR
.Range("F65") = O4O8R
.Range("F66") = C4C8R
.Range("F67") = O8O8R
.Range("F68") = O8C8R
.Range("F69") = C8C8R

If SimpleR = 0 Then
.Rows(64).Hidden = True
Else
.Rows(64).Hidden = False
End If

If O4O8R = 0 Then
.Rows(65).Hidden = True
Else
.Rows(65).Hidden = False
End If

If C4C8R = 0 Then
.Rows(66).Hidden = True
Else
.Rows(66).Hidden = False
End If

If O8O8R = 0 Then
.Rows(67).Hidden = True
Else
.Rows(67).Hidden = False
End If

If O8C8R = 0 Then
.Rows(68).Hidden = True
Else
.Rows(68).Hidden = False
End If

If C8C8R = 0 Then
.Rows(69).Hidden = True
Else
.Rows(69).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_5---------------------------------------------------------------

HL5 = .Range("J75")

Select Case HL5

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F80") = SimpleR
.Range("F81") = O4O8R
.Range("F82") = C4C8R
.Range("F83") = O8O8R
.Range("F84") = O8C8R
.Range("F85") = C8C8R

If SimpleR = 0 Then
.Rows(80).Hidden = True
Else
.Rows(80).Hidden = False
End If

If O4O8R = 0 Then
.Rows(81).Hidden = True
Else
.Rows(81).Hidden = False
End If

If C4C8R = 0 Then
.Rows(82).Hidden = True
Else
.Rows(82).Hidden = False
End If

If O8O8R = 0 Then
.Rows(83).Hidden = True
Else
.Rows(83).Hidden = False
End If

If O8C8R = 0 Then
.Rows(84).Hidden = True
Else
.Rows(84).Hidden = False
End If

If C8C8R = 0 Then
.Rows(85).Hidden = True
Else
.Rows(85).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_6---------------------------------------------------------------

HL6 = .Range("J91")

Select Case HL6

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F96") = SimpleR
.Range("F97") = O4O8R
.Range("F98") = C4C8R
.Range("F99") = O8O8R
.Range("F100") = O8C8R
.Range("F101") = C8C8R

If SimpleR = 0 Then
.Rows(96).Hidden = True
Else
.Rows(96).Hidden = False
End If

If O4O8R = 0 Then
.Rows(97).Hidden = True
Else
.Rows(97).Hidden = False
End If

If C4C8R = 0 Then
.Rows(98).Hidden = True
Else
.Rows(98).Hidden = False
End If

If O8O8R = 0 Then
.Rows(99).Hidden = True
Else
.Rows(99).Hidden = False
End If

If O8C8R = 0 Then
.Rows(100).Hidden = True
Else
.Rows(100).Hidden = False
End If

If C8C8R = 0 Then
.Rows(101).Hidden = True
Else
.Rows(101).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_7---------------------------------------------------------------

HL7 = .Range("J107")

Select Case HL7

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F112") = SimpleR
.Range("F113") = O4O8R
.Range("F114") = C4C8R
.Range("F115") = O8O8R
.Range("F116") = O8C8R
.Range("F117") = C8C8R

If SimpleR = 0 Then
.Rows(112).Hidden = True
Else
.Rows(112).Hidden = False
End If

If O4O8R = 0 Then
.Rows(113).Hidden = True
Else
.Rows(113).Hidden = False
End If

If C4C8R = 0 Then
.Rows(114).Hidden = True
Else
.Rows(114).Hidden = False
End If

If O8O8R = 0 Then
.Rows(115).Hidden = True
Else
.Rows(115).Hidden = False
End If

If O8C8R = 0 Then
.Rows(116).Hidden = True
Else
.Rows(116).Hidden = False
End If

If C8C8R = 0 Then
.Rows(117).Hidden = True
Else
.Rows(117).Hidden = False
End If

Application.EnableEvents = True ' => enable events

'---------------------------------------------------------Section_8---------------------------------------------------------------

HL8 = .Range("J123")

Select Case HL8

Case 0 To 2.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 2.5 To 4.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 5 To 19.6875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 19.75 To 35.6875
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 35.75 To 47.9375
SimpleR = 4
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 48 To 50.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 50.5 To 52.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 53 To 65.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 65.25 To 67.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 67.75 To 83.6875
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 83.75 To 95.9375
SimpleR = 3
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 0
C8C8R = 0

Case 96 To 97.1875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 97.25 To 99.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 99.75 To 116.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 117 To 132.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 133 To 143.9375
SimpleR = 3
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 144 To 146.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 146.5 To 148.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 149 To 161.1875
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 1
O8C8R = 0
C8C8R = 0

Case 161.25 To 163.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 163.75 To 179.6875
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 179.75 To 191.9375
SimpleR = 2
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 0

Case 192 To 194.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 194.5 To 196.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 197 To 209.1875
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 209.25 To 211.6875
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 211.75 To 228.9375
SimpleR = 1
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 229 To 239.9375
SimpleR = 2
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 240 To 242.4375
SimpleR = 0
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 242.5 To 244.9375
SimpleR = 1
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 245 To 260.9375
SimpleR = 2
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 261 To 273.1875
SimpleR = 3
O4O8R = 1
C4C8R = 0
O8O8R = 2
O8C8R = 0
C8C8R = 0

Case 273.25 To 275.6875
SimpleR = 0
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 275.75 To 287.9375
SimpleR = 1
O4O8R = 0
C4C8R = 1
O8O8R = 0
O8C8R = 1
C8C8R = 1

Case 288 To 290.4375
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 3
O8C8R = 0
C8C8R = 0

Case 290.5 To 1200
SimpleR = 0
O4O8R = 0
C4C8R = 0
O8O8R = 0
O8C8R = 0
C8C8R = 0

End Select

Application.EnableEvents = False ' => disable events

.Range("F128") = SimpleR
.Range("F129") = O4O8R
.Range("F130") = C4C8R
.Range("F131") = O8O8R
.Range("F132") = O8C8R
.Range("F133") = C8C8R

If SimpleR = 0 Then
.Rows(128).Hidden = True
Else
.Rows(128).Hidden = False
End If

If O4O8R = 0 Then
.Rows(129).Hidden = True
Else
.Rows(129).Hidden = False
End If

If C4C8R = 0 Then
.Rows(130).Hidden = True
Else
.Rows(130).Hidden = False
End If

If O8O8R = 0 Then
.Rows(131).Hidden = True
Else
.Rows(131).Hidden = False
End If

If O8C8R = 0 Then
.Rows(132).Hidden = True
Else
.Rows(132).Hidden = False
End If

If C8C8R = 0 Then
.Rows(133).Hidden = True
Else
.Rows(133).Hidden = False
End If

Application.EnableEvents = True ' => enable events

End With

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

eriiic · 6 Novembre 2020

Bonjour,

dans :

VB:

Dim SimpleR, O4O8R, C4C8R, O8O8R, O8C8R, C8C8R, HL1, HL2, HL3, HL4, HL5, HL6, HL7, HL8 As Currency
Dim StartNumber, EndNumber As Integer

seules HL8 et EndNumber sont typées, les autres sont Variant
Il faut typer toutes les variables une par une.

Pour le reste à part le masquage des lignes je ne vois pas ce qui prendrait du temps.
Et encore, pour si peu de lignes je suis surpris que ça se sente.
Tu peux enregistrer les lignes à masquer pour toutes les faire en une fois à la fin :

Code:

    Dim pl as Range
    '...
    ' en fin de chaque section remplacer par
    If SimpleR = 0 Then If pl Is Nothing Then Set pl = Rows(16) Else Set pl = Union(pl, Rows(16))
    If O4O8R = 0 Then If pl Is Nothing Then Set pl = Rows(17) Else Set pl = Union(pl, Rows(17))
    ' etc jusqu'à la dernière section
    '...
    ' à la fin
    Rows.Hidden = False ' afficher toues les lignes
    pl.EntireRow.Hidden = True ' masquer toutes celles notées
End Sub

eric
PS : J'ai enlever le With pour tester, remettre les . devant les Rows

MarcMad · 6 Novembre 2020

Merci pour votre réponse j'ai finalement trouver d'où venais le délais c'est au niveau de l'userform qui loppait dans cette feuille sans arrêt. J'ai aussi mis des limite a l'event worksheet change afin d'éviter a excel de refaire les calcul tout le temps.Je vais essayer de votre code pour simplifier le tout.

Par contre je rencontre encore un défi :

J'aimerais tester plusieurs variable dans un select Case mais les Case ne sont pas variable. Ensuite additionner les résultats ensemble ex :

HL1 = .Range("J11") = 2.0
P1a = HL1 - .Range("G15") * 12 - .Range("H15") = 4.0
P1b = ...
P1c = ...
P1d = ...

Select Case HL1 , P1a , P1b , P1c P1d <-------- Ici sa ne fonctionne pas

Case 0 To 2.4375
SimpleR = 1
O4O8R = 2
C4C8R = 1
O8O8R =1
O8C8R = 2
C8C8R = 1

Case 2.5 To 4.9375
SimpleR = 0
O4O8R = 3
C4C8R = 0
O8O8R = 4
O8C8R = 0
C8C8R = 5

SimpleR (1) = SimpleR de HL1 (1) + SimpleR de P1a (0)

eriiic · 6 Novembre 2020

Bonjour,

tu as ta réponse : ça ne fonctionne pas.
eric

Microsoft 365 Loop avec un Select Case

MarcMad

XLDnaute Nouveau

eriiic

XLDnaute Barbatruc

MarcMad

XLDnaute Nouveau

eriiic

XLDnaute Barbatruc

Discussions similaires