3 votes

anonymous_user avatar

Comment résoudre le problème de mon code qui me donne une erreur de compilation ?

Demandé el 29 de Janvier, 2019
Quand la question a-t-elle été
74 affichage
Nombre de visites la question a
2 Réponses
Nombre de réponses aux questions
Résolu
Situation réelle de la question

Il s'agit d'un code original sur lequel je travaille et auquel j'apporte quelques modifications.

 lColumnCount = objWorkbook.Worksheets(WSHTNAME(NAME)).UsedRange.Column.Count

 For j = 1 To lColumnCount - 1
       If objWorkbook.Worksheets(WSHTNAME(NAME)).Cells.Value >= 500000 Then

 End If
  Next j

Lorsque j'ai essayé de l'exécuter, j'ai obtenu un message d'erreur de compilation pour le "end if" au-dessus du "next j".

Y a-t-il un problème avec la façon dont je l'ai écrit ou le code modifié lui-même est-il erroné ? Je ne suis pas tout à fait sûr de l'origine du problème, et je ne sais pas non plus ce qu'implique l'erreur de compilation.

Demandé el 29 de Janvier, 2019 par anonymous_user

Réponses

Trop de publicités?

1voto

alowflyingpig avatar
alowflyingpig Points 720 
Sub Triangle_Monthly_Motor()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim objWorkbook As Workbook
    Dim PATH As String
    Dim lRowCount As Long
    Dim cumulative1, cumulative2, cumulative3, cumulative4 As Double
    Dim cumulative5, cumulative6, cumulative7, cumulative8 As Double
    Dim umulative9, cumulative10, cumulative11, cumulative12 As Double
    Dim CUMTOTAL As Double
    Dim i, j, col As Integer
    Dim WS2 As Worksheet
    Dim rng As Range
    Dim NAME As Long
    Dim WSHTNAME As Variant
    Dim ABC As String
    Dim yrs As Integer

    'DEFINING PATH
    PATH = Cells(18, 11).Text
    PATH1 = Cells(19, 11).Text
    PATH3 = PATH1 & "\Large Claim_Output_Extended.XLSX"
    PATH4 = Cells(23, 11).Text

    Set objWorkbook = Workbooks.Open(PATH3)

    Workbooks.Add

    ActiveWorkbook.SaveAs Filename:=PATH1 & "Large Claim Monthly Triangle.XLSX"

    'FOR MOTOR TAB
    WSHTNAME = Array("PMPCOD", "CMOD", "PMMCOD", "PMPCTBI", "CMTBI", "PMMCTBI", "PMPCTPD", "PMMCTPD", "CMTPD")

    For NAME = LBound(WSHTNAME) To UBound(WSHTNAME)
        With objWorkbook.Worksheets(WSHTNAME(NAME))

            'CHANGE WS NAME HERE'
            Set WS2 = Sheets.Add

            'CAPTURE DATA
            If WSHTNAME(NAME) = "PMMCOD" Then
                col = Application.Match("GINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                col_n = Application.Match("NINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                colp = Application.Match("GODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                colp_n = Application.Match("NODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                coltb_n = Application.Match("OD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                coltlc_n = Application.Match("TOD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "PMPCOD" Then
                    col = Application.Match("GINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("OD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TOD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "CMOD" Then
                    col = Application.Match("GINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_OD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NODP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("OD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TOD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "PMMCTBI" Then
                    col = Application.Match("GINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "PMPCTBI" Then
                    col = Application.Match("GINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "CMTBI" Then
                    col = Application.Match("GINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TB1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTBP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTB_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "CMTPD" Then
                    col = Application.Match("GINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "PMPCTPD" Then
                    col = Application.Match("GINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                ElseIf WSHTNAME(NAME) = "PMMCTPD" Then
                    col = Application.Match("GINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC_TD1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NTDP1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("TD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TTD_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

                Else
                    col = Application.Match("GINC01", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    col_n = Application.Match("NINC01", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp = Application.Match("GPAY01", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    colp_n = Application.Match("NPAY01", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltb_n = Application.Match("PA_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
                    coltlc_n = Application.Match("TPA_L1", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
            End If

            ABC = WSHTNAME(NAME)
            AOCCURYR = Application.Match("AOCCURYR", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)
            ACCTYEAR = Application.Match("ACCTYEAR", objWorkbook.Worksheets(WSHTNAME(NAME)).Range("A1:ZZ1"), 0)

            Application.ActiveSheet.UsedRange
            lRowCount = objWorkbook.Worksheets(WSHTNAME(NAME)).UsedRange.Rows.Count
            lColumnCount = objWorkbook.Worksheets(WSHTNAME(NAME)).UsedRange.Columns.Count

            WS2.NAME = ABC

            'INSERT TITLE
            WS2.Cells(2, 1).Value = "GROSS INCURRED"
            WS2.Cells(23, 1).Value = "GROSS PAID"
            WS2.Cells(44, 1).Value = "NET INCURRED"
            WS2.Cells(65, 1).Value = "NET PAID"
            WS2.Cells(86, 1).Value = "CLAIM COUNT"
            WS2.Cells(107, 1).Value = "CLAIM COUNT(THRESHOLD)"

            yrsmax = Workbooks(PATH4).Worksheets("SHEET1").Cells(17, 13).Value
            yrsmin = Workbooks(PATH4).Worksheets("SHEET1").Cells(17, 11).Value

            'GROSS INCURRED
            For d = 0 To 14
                cumulative1 = 0
                cumulative2 = 0
                cumulative3 = 0
                cumulative4 = 0
                cumulative5 = 0
                cumulative6 = 0
                cumulative7 = 0
                cumulative8 = 0
                cumulative9 = 0
                cumulative10 = 0
                cumulative11 = 0
                cumulative12 = 0
                For j = 1 To lColumnCount - 1
                    If objWorkbook.Worksheets(WSHTNAME(NAME)).Cells.Value >= 500000 Then
                        For i = 1 To lRowCount - 1
                            If objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value <= yrsmax - 14 Then
                                acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value - objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value
                                    If acct = d Then
                                        cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col).Value
                                        cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 1).Value
                                        cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 2).Value
                                        cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 3).Value
                                        cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 4).Value
                                        cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 5).Value
                                        cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 6).Value
                                        cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 7).Value
                                        cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 8).Value
                                        cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 9).Value
                                        cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 10).Value
                                        cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 11).Value
                                    End If
                            End If
                        Next i

                        WS2.Cells(4, 12 * (d) + 2).Value = cumulative1
                        WS2.Cells(4, 12 * (d) + 3).Value = cumulative2
                        WS2.Cells(4, 12 * (d) + 4).Value = cumulative3
                        WS2.Cells(4, 12 * (d) + 5).Value = cumulative4
                        WS2.Cells(4, 12 * (d) + 6).Value = cumulative5
                        WS2.Cells(4, 12 * (d) + 7).Value = cumulative6
                        WS2.Cells(4, 12 * (d) + 8).Value = cumulative7
                        WS2.Cells(4, 12 * (d) + 9).Value = cumulative8
                        WS2.Cells(4, 12 * (d) + 10).Value = cumulative9
                        WS2.Cells(4, 12 * (d) + 11).Value = cumulative10
                        WS2.Cells(4, 12 * (d) + 12).Value = cumulative11
                        WS2.Cells(4, 12 * (d) + 13).Value = cumulative12
                    End If
                Next j
            Next d

            For j = yrsmax - 13 To yrsmax
                For d = j To yrsmax
                    cumulative1 = 0
                    cumulative2 = 0
                    cumulative3 = 0
                    cumulative4 = 0
                    cumulative5 = 0
                    cumulative6 = 0
                    cumulative7 = 0
                    cumulative8 = 0
                    cumulative9 = 0
                    cumulative10 = 0
                    cumulative11 = 0
                    cumulative12 = 0

                    For i = 1 To lRowCount - 1
                        acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value & objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value

                        If acct = j & d Then
                            cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col).Value
                            cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 1).Value
                            cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 2).Value
                            cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 3).Value
                            cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 4).Value
                            cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 5).Value
                            cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 6).Value
                            cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 7).Value
                            cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 8).Value
                            cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 9).Value
                            cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 10).Value
                            cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col + 11).Value
                        End If
                    Next i
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 2).Value = cumulative1
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 3).Value = cumulative2
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 4).Value = cumulative3
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 5).Value = cumulative4
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 6).Value = cumulative5
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 7).Value = cumulative6
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 8).Value = cumulative7
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 9).Value = cumulative8
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 10).Value = cumulative9
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 11).Value = cumulative10
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 12).Value = cumulative11
                    WS2.Cells(3 + j - yrsmin + 1, 12 * (d - j) + 13).Value = cumulative12
                Next d
            Next j
Répondu el 29 de Janvier, 2019 par alowflyingpig (720 Points )

0voto

alowflyingpig avatar
alowflyingpig Points 720

Partie 2 (car le code est supérieur à 30 000 caractères)

            For i = yrsmax - 13 To yrsmax
                WS2.Cells(4, 1).Value = (yrsmax - 14) & "&prior"
                WS2.Cells(3 + i - yrsmin + 1, 1).Value = i
            Next i

            For i = 1 To 180
                WS2.Cells(3, i + 1).Value = i
            Next i

            'PAID'
            For d = 0 To 14
                cumulative1 = 0
                cumulative2 = 0
                cumulative3 = 0
                cumulative4 = 0
                cumulative5 = 0
                cumulative6 = 0
                cumulative7 = 0
                cumulative8 = 0
                cumulative9 = 0
                cumulative10 = 0
                cumulative11 = 0
                cumulative12 = 0

                For i = 1 To lRowCount - 1
                    If objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value <= yrsmax - 14 Then
                        acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value - objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value
                        If acct = d Then
                            cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp).Value
                            cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 1).Value
                            cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 2).Value
                            cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 3).Value
                            cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 4).Value
                            cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 5).Value
                            cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 6).Value
                            cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 7).Value
                            cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 8).Value
                            cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 9).Value
                            cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 10).Value
                            cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 11).Value
                        End If
                    End If
                Next i

                WS2.Cells(25, 12 * (d) + 2).Value = cumulative1
                WS2.Cells(25, 12 * (d) + 3).Value = cumulative2
                WS2.Cells(25, 12 * (d) + 4).Value = cumulative3
                WS2.Cells(25, 12 * (d) + 5).Value = cumulative4
                WS2.Cells(25, 12 * (d) + 6).Value = cumulative5
                WS2.Cells(25, 12 * (d) + 7).Value = cumulative6
                WS2.Cells(25, 12 * (d) + 8).Value = cumulative7
                WS2.Cells(25, 12 * (d) + 9).Value = cumulative8
                WS2.Cells(25, 12 * (d) + 10).Value = cumulative9
                WS2.Cells(25, 12 * (d) + 11).Value = cumulative10
                WS2.Cells(25, 12 * (d) + 12).Value = cumulative11
                WS2.Cells(25, 12 * (d) + 13).Value = cumulative12
            Next d

            For j = yrsmax - 13 To yrsmax
                For d = j To yrsmax
                    cumulative1 = 0
                    cumulative2 = 0
                    cumulative3 = 0
                    cumulative4 = 0
                    cumulative5 = 0
                    cumulative6 = 0
                    cumulative7 = 0
                    cumulative8 = 0
                    cumulative9 = 0
                    cumulative10 = 0
                    cumulative11 = 0
                    cumulative12 = 0
                        For i = 1 To lRowCount - 1
                            acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value & objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value
                            If acct = j & d Then
                                cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp).Value
                                cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 1).Value
                                cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 2).Value
                                cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 3).Value
                                cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 4).Value
                                cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 5).Value
                                cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 6).Value
                                cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 7).Value
                                cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 8).Value
                                cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 9).Value
                                cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 10).Value
                                cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, colp + 11).Value
                            End If
                        Next i
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 2).Value = cumulative1
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 3).Value = cumulative2
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 4).Value = cumulative3
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 5).Value = cumulative4
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 6).Value = cumulative5
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 7).Value = cumulative6
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 8).Value = cumulative7
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 9).Value = cumulative8
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 10).Value = cumulative9
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 11).Value = cumulative10
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 12).Value = cumulative11
                        WS2.Cells(24 + j - yrsmin + 1, 12 * (d - j) + 13).Value = cumulative12
                Next d
            Next j

            For i = yrsmax - 13 To yrsmax
                WS2.Cells(25, 1).Value = (yrsmax - 14) & "&prior"
                WS2.Cells(24 + i - yrsmin + 1, 1).Value = i
            Next i

            For i = 1 To 180
                WS2.Cells(24, i + 1).Value = i
            Next i

            'NET INCURRED
            For d = 0 To 14
                cumulative1 = 0
                cumulative2 = 0
                cumulative3 = 0
                cumulative4 = 0
                cumulative5 = 0
                cumulative6 = 0
                cumulative7 = 0
                cumulative8 = 0
                cumulative9 = 0
                cumulative10 = 0
                cumulative11 = 0
                cumulative12 = 0
                For i = 1 To lRowCount - 1
                    If objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value <= yrsmax - 14 Then
                    acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value - objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value
                        If acct = d Then
                            cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n).Value
                            cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 1).Value
                            cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 2).Value
                            cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 3).Value
                            cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 4).Value
                            cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 5).Value
                            cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 6).Value
                            cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 7).Value
                            cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 8).Value
                            cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 9).Value
                            cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 10).Value
                            cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 11).Value
                        End If
                    End If
                Next i
                WS2.Cells(46, 12 * (d) + 2).Value = cumulative1
                WS2.Cells(46, 12 * (d) + 3).Value = cumulative2
                WS2.Cells(46, 12 * (d) + 4).Value = cumulative3
                WS2.Cells(46, 12 * (d) + 5).Value = cumulative4
                WS2.Cells(46, 12 * (d) + 6).Value = cumulative5
                WS2.Cells(46, 12 * (d) + 7).Value = cumulative6
                WS2.Cells(46, 12 * (d) + 8).Value = cumulative7
                WS2.Cells(46, 12 * (d) + 9).Value = cumulative8
                WS2.Cells(46, 12 * (d) + 10).Value = cumulative9
                WS2.Cells(46, 12 * (d) + 11).Value = cumulative10
                WS2.Cells(46, 12 * (d) + 12).Value = cumulative11
                WS2.Cells(46, 12 * (d) + 13).Value = cumulative12
            Next d

            For j = yrsmax - 13 To yrsmax
                For d = j To yrsmax
                    cumulative1 = 0
                    cumulative2 = 0
                    cumulative3 = 0
                    cumulative4 = 0
                    cumulative5 = 0
                    cumulative6 = 0
                    cumulative7 = 0
                    cumulative8 = 0
                    cumulative9 = 0
                    cumulative10 = 0
                    cumulative11 = 0
                    cumulative12 = 0

                    For i = 1 To lRowCount - 1
                        acct = objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, AOCCURYR).Value & objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, ACCTYEAR).Value
                        If acct = j & d Then
                            cumulative1 = cumulative1 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n).Value
                            cumulative2 = cumulative2 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 1).Value
                            cumulative3 = cumulative3 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 2).Value
                            cumulative4 = cumulative4 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 3).Value
                            cumulative5 = cumulative5 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 4).Value
                            cumulative6 = cumulative6 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 5).Value
                            cumulative7 = cumulative7 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 6).Value
                            cumulative8 = cumulative8 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 7).Value
                            cumulative9 = cumulative9 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 8).Value
                            cumulative10 = cumulative10 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 9).Value
                            cumulative11 = cumulative11 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 10).Value
                            cumulative12 = cumulative12 + objWorkbook.Worksheets(WSHTNAME(NAME)).Cells(1 + i, col_n + 11).Value
                        End If
                    Next i
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 2).Value = cumulative1
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 3).Value = cumulative2
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 4).Value = cumulative3
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 5).Value = cumulative4
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 6).Value = cumulative5
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 7).Value = cumulative6
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 8).Value = cumulative7
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 9).Value = cumulative8
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 10).Value = cumulative9
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 11).Value = cumulative10
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 12).Value = cumulative11
                    WS2.Cells(45 + j - yrsmin + 1, 12 * (d - j) + 13).Value = cumulative12
                Next d
            Next j

            For i = yrsmax - 13 To yrsmax
                WS2.Cells(46, 1).Value = (yrsmax - 14) & "&prior"
                WS2.Cells(45 + i - yrsmin + 1, 1).Value = i
            Next i

            For i = 1 To 180
                WS2.Cells(45, i + 1).Value = i
            Next i

        End With
    Next NAME

    'Rearrange Worksheets
    Sheets("PMPCTBI").Move before:=Sheets(1)
    Sheets("PMPCTPD").Move before:=Sheets(2)
    Sheets("PMPCOD").Move before:=Sheets(3)
    Sheets("PMMCOD").Move before:=Sheets(4)
    Sheets("PMMCTBI").Move before:=Sheets(5)
    Sheets("PMMCOD").Move before:=Sheets(6)
    Sheets("CMTBI").Move before:=Sheets(7)
    Sheets("CMTPD").Move before:=Sheets(8)
    Sheets("CMOD").Move before:=Sheets(9)

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    ActiveWorkbook.SaveAs Filename:=PATH1 & "\Large Claim Monthly Triangle_Motor.xlsx"

End Sub
Répondu el 29 de Janvier, 2019 par alowflyingpig (720 Points )

Questions en vedette

Top Tags

Dans notre réseau

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

Yandex
X