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 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
| Option Explicit
Option Base 1
Sub Pricer2()
'déclaration de tableaux
Dim Tableau() As Variant, Tableau1() As Variant
Dim Tableau_TGCS() As Variant, Tableau_TGCC() As Variant, Tableau_TT() As Variant, Tableau_TARC_C() As Variant, Tableau_TARC_S() As Variant
Dim I As Double, N As Double, poli As Double, nb_cs As Double, nb_cc As Double, nb_t As Double, nb_arc_s As Double, nb_arc_c As Double
'Déclaration des variables des taux, capital de la parcelle et des primes
Dim Taux_Grele, Taux_Temp, Taux_Arc
Dim Capital_Par As Double
Dim Prime As Double, Prime_Grele As Double, Prime_Temp As Double, Prime_Arc As Double
Dim Prime_Totale_Grele As Double, Prime_Totale_Temp As Double, Prime_Totale_Arc As Double
'Déclaration des variables qui permettront de rechercher les taux via la fonction rechercheV
Dim Recherche_Taux_CC As String, Recherche_Taux_CS As String, Recherche_taux_temp As String
'Déclaration des plages des taux
'Dim Tableau_TGCC As Range, Tableau_TGCS As Range, Tableau_TT As Range
Dim A As Worksheet, B As Worksheet, C As Worksheet, D As Worksheet, E As Worksheet, F As Worksheet, G As Worksheet, P As Worksheet
'Déclaration d'une variable qui servira à calculer le temps d'exécution de la macro
Dim Start As Single
'Calcul du temps d'exécution de ma macro avec Start = Timer en début de macro, l'affichage se fera avec Msgbox en fin de macro
Start = Timer
Application.ScreenUpdating = False
Set A = Worksheets("Automatisé")
Set B = Worksheets("Prime")
Set C = Worksheets("Tarifgrele 1-95") 'feuille de taux grele cultures courantes
Set D = Worksheets("Tarifgrele 101") 'feuille de taux grele cultures spéciales
Set E = Worksheets("Tarifgrele 116") 'feuille de taux ARC Confort
Set F = Worksheets("Tarifgrele 121") 'feuille de taux ARC socle
Set G = Worksheets("Tarifgrele 104") 'feuille de taux tempête
Set P = Worksheets("Contrats_2017 (2)")
A.Range("A:Z").ClearContents
B.Range("A2:G1000000").ClearContents
N = P.Cells(Rows.Count, 1).End(xlUp).Row
nb_cs = D.Cells(Rows.Count, 1).End(xlUp).Row
nb_cc = C.Cells(Rows.Count, 1).End(xlUp).Row
nb_t = G.Cells(Rows.Count, 1).End(xlUp).Row
nb_arc_s = F.Cells(Rows.Count, 1).End(xlUp).Row
nb_arc_c = E.Cells(Rows.Count, 1).End(xlUp).Row
ReDim Tableau(N - 1, 29) 'on va y stocker nos données
ReDim Tableau1(N - 1, 4) 'on va y enregistrer nos primes
ReDim Tableau_TGCS(nb_cs - 1, 2), Tableau_TGCC(nb_cc - 1, 2), Tableau_TT(nb_t - 1, 2), Tableau_TARC_C(nb_arc_c - 1, 2), Tableau_TARC_S(nb_arc_s - 1, 2)
Tableau() = P.Range("A2:AC" & N)
Tableau_TGCS() = D.Range("B2:C" & nb_cs)
Tableau_TGCC() = C.Range("A2:B" & nb_cc)
Tableau_TT() = G.Range("C2:D" & nb_t)
Tableau_TARC_S() = E.Range("C2:D" & nb_arc_s)
Tableau_TARC_C() = F.Range("C2:D" & nb_arc_c)
For I = 1 To N
Tableau1(I, 1) = Tableau(I, 1) 'Retient le numéro de police
Select Case (Tableau(I, 2))
Case Is = "GRE"
Select Case (Tableau(I, 3))
Case Is = ""
Select Case (Tableau(I, 4))
Case Is = ""
'''''''''''''''Taux grêle
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP, la culture
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP colza, la culture
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 11) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la culture (vigne)
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'si taux introuvable alors on prends le taux en base
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Taux_Grele = Tableau(I, 28)
End If
End If
End If
End If
End If
'''''''''''''''Taux tempête
Taux_Temp = 0
'''''''''''''''Taux arc
Taux_Arc = 0
'''''''''''''''Calcul des primes
'Calcul capital assuré
Capital_Par = Tableau(I, 27)
Prime_Grele = (Taux_Grele / 100) * Capital_Par
Tableau1(I, 2) = Prime_Grele
Prime_Temp = (Taux_Temp / 100) * Capital_Par
Tableau1(I, 3) = Prime_Temp
Prime_Arc = (Taux_Arc / 100) * Capital_Par
Tableau1(I, 4) = Prime_Arc
Case Is = "ARC"
'''''''''''''''Taux grêle
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP, la culture
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP colza, la culture
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 11) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la culture (vigne)
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'si taux introuvable alors on prends le taux en base
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Taux_Grele = Tableau(I, 28)
End If
End If
End If
End If
End If
'''''''''''''''Taux tempête
Taux_Temp = 0
'''''''''''''''Taux arc
Taux_Arc = 0
'''''''''''''''Calcul des primes
'Calcul capital assuré
Capital_Par = Tableau(I, 27)
Prime_Grele = (Taux_Grele / 100) * Capital_Par
Tableau1(I, 2) = Prime_Grele
Prime_Temp = (Taux_Temp / 100) * Capital_Par
Tableau1(I, 3) = Prime_Temp
Prime_Arc = (Taux_Arc / 100) * Capital_Par
Tableau1(I, 4) = Prime_Arc
End Select
Case Is = "TEM"
Select Case (Tableau(I, 4))
Case Is = ""
'''''''''''''''Taux grêle
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP, la culture
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP colza, la culture
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 11) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la culture (vigne)
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'si taux introuvable alors on prends le taux en base
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Taux_Grele = Tableau(I, 28)
End If
End If
End If
End If
End If
'''''''''''''''Taux tempête
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la date
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 18) 'taux selon la date
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 9) 'FAP
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 14) 'FAC
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 16) 'FAP Colza
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
'le taux est nul aucune recherche n'est vraie
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
Taux_Temp = Tableau(I, 29)
End If
End If
End If
End If
'''''''''''''''Taux arc
Taux_Arc = 0
'''''''''''''''Calcul des primes
'Calcul capital assuré
Capital_Par = Tableau(I, 27)
Prime_Grele = (Taux_Grele / 100) * Capital_Par
Tableau1(I, 2) = Prime_Grele
Prime_Temp = (Taux_Temp / 100) * Capital_Par
Tableau1(I, 3) = Prime_Temp
Prime_Arc = (Taux_Arc / 100) * Capital_Par
Tableau1(I, 4) = Prime_Arc
Case Is = "ARC"
'''''''''''''''Taux grêle
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP, la culture
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 9) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la franchise FAP colza, la culture
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CC = Tableau(I, 5) & "-" & Tableau(I, 6) & "-" & Tableau(I, 8) & "-" & Tableau(I, 11) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CC, Tableau_TGCC, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe, la culture (vigne)
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-" & Tableau(I, 12)
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'recherche du taux selon le dpt, le niveau d'exception, la classe
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Recherche_Taux_CS = Tableau(I, 5) & "-" & Tableau(I, 7) & "-" & Tableau(I, 8) & "-"
Taux_Grele = Application.VLookup(Recherche_Taux_CS, Tableau_TGCS, 2, 0)
'si taux introuvable alors on prends le taux en base
If Application.WorksheetFunction.IsNA(Taux_Grele) Then
Taux_Grele = Tableau(I, 28)
End If
End If
End If
End If
End If
'''''''''''''''Taux tempête
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la date
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 18) 'taux selon la date
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 9) 'FAP
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 14) 'FAC
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
'recherche du taux selon la franchise concatené à la zone, la nature de la culture, la franchise
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
Recherche_taux_temp = Tableau(I, 13) & "-" & Tableau(I, 17) & "-" & Tableau(I, 16) 'FAP Colza
Taux_Temp = Application.VLookup(Recherche_taux_temp, Tableau_TT, 2, 0)
'le taux est nul aucune recherche n'est vraie
If Application.WorksheetFunction.IsNA(Taux_Temp) Then
Taux_Temp = Tableau(I, 29)
End If
End If
End If
End If
'''''''''''''''Taux arc
Taux_Arc = 0
'''''''''''''''Calcul des primes
'Calcul capital assuré
Capital_Par = Tableau(I, 27)
Prime_Grele = (Taux_Grele / 100) * Capital_Par
Tableau1(I, 2) = Prime_Grele
Prime_Temp = (Taux_Temp / 100) * Capital_Par
Tableau1(I, 3) = Prime_Temp
Prime_Arc = (Taux_Arc / 100) * Capital_Par
Tableau1(I, 4) = Prime_Arc
End Select
End Select
End Select
'With A
' .Cells(I, 1).Value = Tableau1(I, 1)
' .Cells(I, 3).Value = Tableau1(I, 2)
' .Cells(I, 5).Value = Tableau1(I, 3)
' .Cells(I, 7).Value = Tableau1(I, 4)
'End With
Next I
With A
Range("A2:D" & N).Value = Tableau1()
End With
A.Select
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Durée du traitement: " & Timer - Start & " secondes"
End Sub |
Partager