here is the code, the debuger stops where the **************** are
Dim printselect(100), selected1(9)
Public makepgnum
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Run "Print_Print"
Unload FormPrint
End
End Sub
Private Sub CommandButton3_Click()
tb1 = Int(TextBox1.Value)
origtb = tb1
If Label1.Caption = tb1 Then flagz = 1
If Label2.Caption = tb1 Then flagz = 1
If Label3.Caption = tb1 Then flagz = 1
If Label4.Caption = tb1 Then flagz = 1
If Label5.Caption = tb1 Then flagz = 1
If Label6.Caption = tb1 Then flagz = 1
If Label7.Caption = tb1 Then flagz = 1
If Label8.Caption = tb1 Then flagz = 1
If Label9.Caption = tb1 Then flagz = 1
If Label19.Caption = tb1 Then flagz = 1
If Label20.Caption = tb1 Then flagz = 1
If Label21.Caption = tb1 Then flagz = 1
If Label22.Caption = tb1 Then flagz = 1
If Label23.Caption = tb1 Then flagz = 1
If Label24.Caption = tb1 Then flagz = 1
If Label25.Caption = tb1 Then flagz = 1
If Label26.Caption = tb1 Then flagz = 1
If Label27.Caption = tb1 Then flagz = 1
If Label28.Caption = tb1 Then flagz = 1
If Label29.Caption = tb1 Then flagz = 1
If Label30.Caption = tb1 Then flagz = 1
If Label31.Caption = tb1 Then flagz = 1
If Label32.Caption = tb1 Then flagz = 1
If Label33.Caption = tb1 Then flagz = 1
If flagz = 1 Then GoTo linez
If tb1 = 1 Then
If Label1.Caption = "1" Then flag = 1
If Label2.Caption = "1" Then flag = 1
If Label3.Caption = "1" Then flag = 1
If Label4.Caption = "1" Then flag = 1
If Label5.Caption = "1" Then flag = 1
If Label6.Caption = "1" Then flag = 1
If Label7.Caption = "1" Then flag = 1
If Label8.Caption = "1" Then flag = 1
If Label9.Caption = "1" Then flag = 1
If Label19.Caption = "1" Then flag = 1
If Label20.Caption = "1" Then flag = 1
If Label21.Caption = "1" Then flag = 1
If Label22.Caption = "1" Then flag = 1
If Label23.Caption = "1" Then flag = 1
If Label24.Caption = "1" Then flag = 1
If Label25.Caption = "1" Then flag = 1
If Label26.Caption = "1" Then flag = 1
If Label27.Caption = "1" Then flag = 1
If Label28.Caption = "1" Then flag = 1
If Label29.Caption = "1" Then flag = 1
If Label30.Caption = "1" Then flag = 1
If Label31.Caption = "1" Then flag = 1
If Label32.Caption = "1" Then flag = 1
If Label33.Caption = "1" Then flag = 1
End If
If tb1 = Range("counter") Then
If Label1.Caption = Range("counter") Then flag = 1
If Label2.Caption = Range("counter") Then flag = 1
If Label3.Caption = Range("counter") Then flag = 1
If Label4.Caption = Range("counter") Then flag = 1
If Label5.Caption = Range("counter") Then flag = 1
If Label6.Caption = Range("counter") Then flag = 1
If Label7.Caption = Range("counter") Then flag = 1
If Label8.Caption = Range("counter") Then flag = 1
If Label9.Caption = Range("counter") Then flag = 1
If Label19.Caption = Range("counter") Then flag = 1
If Label20.Caption = Range("counter") Then flag = 1
If Label21.Caption = Range("counter") Then flag = 1
If Label22.Caption = Range("counter") Then flag = 1
If Label23.Caption = Range("counter") Then flag = 1
If Label24.Caption = Range("counter") Then flag = 1
If Label25.Caption = Range("counter") Then flag = 1
If Label26.Caption = Range("counter") Then flag = 1
If Label27.Caption = Range("counter") Then flag = 1
If Label28.Caption = Range("counter") Then flag = 1
If Label29.Caption = Range("counter") Then flag = 1
If Label30.Caption = Range("counter") Then flag = 1
If Label31.Caption = Range("counter") Then flag = 1
If Label32.Caption = Range("counter") Then flag = 1
If Label33.Caption = Range("counter") Then flag = 1
End If
If flag = 0 Then
If Label1.Caption = "" Then
Label1.Caption = TextBox1.Value
printselect(1) = TextBox1.Value
ElseIf Label2.Caption = "" Then
Label2.Caption = TextBox1.Value
printselect(2) = TextBox1.Value
Label10.Caption = ","
ElseIf Label3.Caption = "" Then
Label3.Caption = TextBox1.Value
printselect(3) = TextBox1.Value
Label11.Caption = ","
ElseIf Label4.Caption = "" Then
Label4.Caption = TextBox1.Value
printselect(4) = TextBox1.Value
Label12.Caption = ","
ElseIf Label5.Caption = "" Then
Label5.Caption = TextBox1.Value
printselect(5) = TextBox1.Value
Label13.Caption = ","
ElseIf Label6.Caption = "" Then
Label6.Caption = TextBox1.Value
printselect(6) = TextBox1.Value
Label17.Caption = ","
ElseIf Label7.Caption = "" Then
Label7.Caption = TextBox1.Value
printselect(7) = TextBox1.Value
Label14.Caption = ","
ElseIf Label8.Caption = "" Then
Label8.Caption = TextBox1.Value
printselect(8) = TextBox1.Value
Label15.Caption = ","
ElseIf Label9.Caption = "" Then
Label9.Caption = TextBox1.Value
printselect(9) = TextBox1.Value
Label16.Caption = ","
ElseIf Label19.Caption = "" Then
Label19.Caption = TextBox1.Value
printselect(10) = TextBox1.Value
Label76.Caption = ","
ElseIf Label20.Caption = "" Then
Label20.Caption = TextBox1.Value
printselect(11) = TextBox1.Value
Label43.Caption = ","
ElseIf Label21.Caption = "" Then
Label21.Caption = TextBox1.Value
printselect(12) = TextBox1.Value
Label44.Caption = ","
ElseIf Label22.Caption = "" Then
Label22.Caption = TextBox1.Value
printselect(13) = TextBox1.Value
ElseIf Label23.Caption = "" Then
Label23.Caption = TextBox1.Value
printselect(14) = TextBox1.Value
Label45.Caption = ","
ElseIf Label24.Caption = "" Then
Label24.Caption = TextBox1.Value
printselect(15) = TextBox1.Value
Label46.Caption = ","
ElseIf Label25.Caption = "" Then
Label25.Caption = TextBox1.Value
printselect(16) = TextBox1.Value
Label47.Caption = ","
ElseIf Label26.Caption = "" Then
Label26.Caption = TextBox1.Value
printselect(17) = TextBox1.Value
Label48.Caption = ","
ElseIf Label27.Caption = "" Then
Label27.Caption = TextBox1.Value
printselect(18) = TextBox1.Value
Label49.Caption = ","
ElseIf Label28.Caption = "" Then
Label28.Caption = TextBox1.Value
printselect(19) = TextBox1.Value
Label50.Caption = ","
ElseIf Label29.Caption = "" Then
Label29.Caption = TextBox1.Value
printselect(20) = TextBox1.Value
Label51.Caption = ","
ElseIf Label30.Caption = "" Then
Label30.Caption = TextBox1.Value
printselect(21) = TextBox1.Value
Label52.Caption = ","
ElseIf Label31.Caption = "" Then
Label31.Caption = TextBox1.Value
printselect(22) = TextBox1.Value
Label53.Caption = ","
ElseIf Label32.Caption = "" Then
Label32.Caption = TextBox1.Value
printselect(23) = TextBox1.Value
Label54.Caption = ","
ElseIf Label33.Caption = "" Then
Label33.Caption = TextBox1.Value
printselect(24) = TextBox1.Value
Label55.Caption = ","
End If
End If
TextBox1.Value = TextBox1.Value + 1
tb1 = Int(TextBox1.Value)
If tb1 > Range("counter") Then TextBox1.Value = Range("counter")
g = 1
Do Until g = 0
g = tb1
If Label1.Caption = tb1 Then tb1 = tb1 + 1
If Label2.Caption = tb1 Then tb1 = tb1 + 1
If Label3.Caption = tb1 Then tb1 = tb1 + 1
If Label4.Caption = tb1 Then tb1 = tb1 + 1
If Label5.Caption = tb1 Then tb1 = tb1 + 1
If Label6.Caption = tb1 Then tb1 = tb1 + 1
If Label7.Caption = tb1 Then tb1 = tb1 + 1
If Label8.Caption = tb1 Then tb1 = tb1 + 1
If Label9.Caption = tb1 Then tb1 = tb1 + 1
If Label19.Caption = tb1 Then tb1 = tb1 + 1
If Label20.Caption = tb1 Then tb1 = tb1 + 1
If Label21.Caption = tb1 Then tb1 = tb1 + 1
If Label22.Caption = tb1 Then tb1 = tb1 + 1
If Label23.Caption = tb1 Then tb1 = tb1 + 1
If Label24.Caption = tb1 Then tb1 = tb1 + 1
If Label25.Caption = tb1 Then tb1 = tb1 + 1
If Label26.Caption = tb1 Then tb1 = tb1 + 1
If Label27.Caption = tb1 Then tb1 = tb1 + 1
If Label28.Caption = tb1 Then tb1 = tb1 + 1
If Label29.Caption = tb1 Then tb1 = tb1 + 1
If Label30.Caption = tb1 Then tb1 = tb1 + 1
If Label31.Caption = tb1 Then tb1 = tb1 + 1
If Label32.Caption = tb1 Then tb1 = tb1 + 1
If Label33.Caption = tb1 Then tb1 = tb1 + 1
If tb1 = g Then g = 0
If tb1 >= Range("counter") Then g = 0
If tb1 > Range("counter") Then tb1 = origtb
Loop
TextBox1.Value = tb1
linez:
End Sub
Private Sub CommandButton4_Click()
Unload FormPrint
End
End Sub
Private Sub CommandButton5_Click()
Label1.Caption = ""
Label2.Caption = ""
Label3.Caption = ""
Label4.Caption = ""
Label5.Caption = ""
Label6.Caption = ""
Label7.Caption = ""
Label8.Caption = ""
Label9.Caption = ""
Label10.Caption = ""
Label11.Caption = ""
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label19.Caption = ""
Label20.Caption = ""
Label21.Caption = ""
Label22.Caption = ""
Label23.Caption = ""
Label24.Caption = ""
Label25.Caption = ""
Label26.Caption = ""
Label27.Caption = ""
Label28.Caption = ""
Label29.Caption = ""
Label30.Caption = ""
Label31.Caption = ""
Label32.Caption = ""
Label33.Caption = ""
Label43.Caption = ""
Label44.Caption = ""
Label45.Caption = ""
Label46.Caption = ""
Label47.Caption = ""
Label48.Caption = ""
Label49.Caption = ""
Label50.Caption = ""
Label51.Caption = ""
Label52.Caption = ""
Label53.Caption = ""
Label54.Caption = ""
Label55.Caption = ""
Label56.Caption = ""
Label76.Caption = ""
End Sub
Private Sub CommandButton6_Click()
Application.ScreenUpdating = False
Sheets("FullQtrTri").Visible = True
Sheets("Ratio Tri").Visible = True
Sheets("Ratio Numerator").Visible = True
Sheets("Ratio Denominator").Visible = True
Sheets("Annual Tri").Visible = True
Sheets("Annual Tri 2").Visible = True
Sheets("Graphs").Visible = True
Sheets("Graph Calc").Visible = True
Sheets("Annual Ratio Tri").Visible = True
Sheets("Annual Ratio Tri (2)").Visible = True
Sheets("Annual Ratio Numerator").Visible = True
Sheets("Annual Ratio Denominator").Visible = True
Sheets("P-40QtrSummary").Visible = True
Sheets("P-40QtrACER").Visible = True
FormPrintSelectedPgNum.Show
For X = 1 To 24
If printselect(X) > 0 Then
printcount = printcount + 1
End If
Next X
FormPrint.Height = 190
For q = 1 To printcount
printline = printselect(q)
If printcount = 1 Then printline = printselect(1)
FormPrint.Image1.Width = 186 * q / printcount
FormPrint.Label81.Caption = Int(q / printcount * 100) & "%"
FormPrint.Label78.Caption = q
FormPrint.Label80.Caption = printcount
FormPrint.Repaint
'reads data one row at a time
Title = Range("print_table_start").Offset(printline, 1).Text
crit1 = Range("print_table_start").Offset(printline, 2).Text
crit2 = Range("print_table_start").Offset(printline, 3).Text
crit3 = Range("print_table_start").Offset(printline, 4).Text
crit4 = Range("print_table_start").Offset(printline, 5).Text
lbl = Range("print_table_start").Offset(printline, 7).Text
'calcs pg num
pgnum = 0
For e = 1 To printline
pgnum = pgnum + 1
If Range("print_table_start").Offset(e - 1, 1).Text = "A vs E" Then pgnum = pgnum + 2
Next e
FormPrint.Label84.Caption = Title
FormPrint.Label83.Caption = lbl
FormPrint.Repaint
If Title = "IBNR" Then
If crit1 = "40 Qtr" Then
Application.Run ("transferqtrtable")
Range("exh_40IBNR") = lbl
TitleRange = Range("toc_ibnr40")
If makepgnum = 1 Then Range("pgnumIBNRQ").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If crit1 = "Annual" Then
Range("exh_IBNR") = lbl
Worksheets("IBNR").Activate
TitleRange = Range("toc_ibnr")
If makepgnum = 1 Then Range("pgnumIBNRA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
ElseIf Title = "ACER" Then
If crit1 = "40 Qtr" Then
Application.Run ("transferqtrtableacer")
Range("exh_40ACER") = lbl
TitleRange = Range("toc_acer40")
If makepgnum = 1 Then Range("pgnumACERQ").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If crit1 = "Annual" Then
Range("exh_ACER") = lbl
Worksheets("ACER").Activate
TitleRange = Range("toc_acer")
If makepgnum = 1 Then Range("pgnumACERA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
ElseIf Title = "IBNR+ACER" Then
If crit1 = "Annual" Then
Range("exh_IBNRACER") = lbl
Worksheets("IBNR+ACER").Activate
Range("toggle_IBNRACER") = 0
TitleRange = Range("toc_ibnracer")
If makepgnum = 1 Then Range("pgnumIBNRACER").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("toggle_IBNRACER") = 1
End If
ElseIf Title = "Development Triangle" Then
If crit1 = "40 Qtr" Then
Worksheets(crit2).Activate
Application.Run ("MakeP40QtrTri")
Range("exh_dev") = lbl
TitleRange = Range("toc_dev40")
If makepgnum = 1 Then Range("pgnumDevQ").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
If crit1 = "Annual" Then
Worksheets(crit2).Activate
Application.Run ("MakeAnnualTri")
Worksheets("P-AnnualTri").Select
Range("exh_devAnnual") = lbl
TitleRange = Range("toc_devAnnual")
If makepgnum = 1 Then Range("pgnumDevA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
ElseIf Title = "LR vs ATA" Then
For X = 1 To 150
For Y = 150 To 300
If Worksheets(crit1).Cells(Y, X) = crit2 Then
agecol = Cells(Y, X).Column
X = 150
Y = 300
End If
Next Y
Next X
Worksheets(crit1).Activate
Worksheets(crit1).Cells(1, agecol).Select
Application.Run ("reg_chart")
Range("exh_reg") = lbl
TitleRange = Range("toc_reg")
Worksheets("Reg").Select
If makepgnum = 1 Then Range("pgnumReg").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ElseIf Title = "Double Weighted Avg" Then
If crit1 = "Inc Loss" Then TriType = "inc"
If crit1 = "Pd Loss" Then TriType = "pd"
If crit1 = "Pd ALAE" Then TriType = "ALAE"
If crit1 = "Counts Open" Then TriType = "count"
If crit1 = "Counts O&C" Then TriType = "countOC"
Worksheets(crit1).Range("creddrop_" & TriType) = crit3 * 20
For X = 1 To 150
For Y = 150 To 300
If Worksheets(crit1).Cells(Y, X) = crit2 Then
agecol = Cells(Y, X).Column
X = 150
Y = 300
End If
Next Y
Next X
Worksheets(crit1).Activate
Worksheets(crit1).Cells(1, agecol).Select
Application.Run ("Dbl_Weighted_Avg_Chart")
Range("exh_dwa") = lbl
TitleRange = Range("toc_dwa")
Worksheets("Dbl Wtd Avg").Select
If makepgnum = 1 Then Range("pgnumDWA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ElseIf Title = "Quarterly Ratio Triangle" Then
If crit1 = "X: Incurred" Then crit1 = 1
If crit1 = "X: Paid" Then crit1 = 2
If crit1 = "X: Pd ALAE" Then crit1 = 3
If crit1 = "X: Open Counts" Then crit1 = 4
If crit1 = "X: Total Counts" Then crit1 = 5
If crit1 = "X: Earned Premium" Then crit1 = 6
If crit1 = "X: Incurred (Resv)" Then crit1 = 7
If crit1 = "X: Paid (Resv)" Then crit1 = 8
If crit1 = "X: Pd ALAE (Resv)" Then crit1 = 9
If crit1 = "X: Open Counts (Resv)" Then crit1 = 10
If crit1 = "X: Total Counts (Resv)" Then crit1 = 11
If crit1 = "X: Earned Premium (Resv)" Then crit1 = 12
If crit2 = "Y: Incurred" Then crit2 = 1
If crit2 = "Y: Paid" Then crit2 = 2
If crit2 = "Y: Pd ALAE" Then crit2 = 3
If crit2 = "Y: Open Counts" Then crit2 = 4
If crit2 = "Y: Total Counts" Then crit2 = 5
If crit2 = "Y: Earned Premium" Then crit2 = 6
If crit2 = "Y: Incurred (Resv)" Then crit2 = 7
If crit2 = "Y: Paid (Resv)" Then crit2 = 8
If crit2 = "Y: Pd ALAE (Resv)" Then crit2 = 9
If crit2 = "Y: Open Counts (Resv)" Then crit2 = 10
If crit2 = "Y: Total Counts (Resv)" Then crit2 = 11
If crit2 = "Y: Earned Premium (Resv)" Then crit2 = 12
If crit3 = "= X/Y" Then crit3 = 1
If crit3 = "= 1 - (X/Y)" Then crit3 = 2
If crit3 = "= c * (X/Y)" Then crit3 = 3
If crit4 = "Percent (0)" Then crit4 = 1
If crit4 = "Percent (1)" Then crit4 = 2
If crit4 = "Percent (2)" Then crit4 = 3
If crit4 = "Percent (3)" Then crit4 = 4
If crit4 = "Percent (4)" Then crit4 = 5
If crit4 = "Percent (5)" Then crit4 = 6
If crit4 = "General (0)" Then crit4 = 7
If crit4 = "General (1)" Then crit4 = 8
If crit4 = "General (2)" Then crit4 = 9
If crit4 = "General (3)" Then crit4 = 10
If crit4 = "General (4)" Then crit4 = 11
If crit4 = "General (5)" Then crit4 = 12
If crit4 = "Comma" Then crit4 = 13
If (Range("ratio_num0") <> crit1 Or Range("ratio_denom0") <> crit2 Or Range("rt_formula") <> crit3 Or Range("rt_format1") <> crit4) Or Range("IsRatioRun2") <> 2 Then
Range("ratio_num0") = crit1
Range("ratio_denom0") = crit2
Range("rt_formula") = crit3
Range("rt_format1") = crit4
'add level for rt_trirec
Application.Run ("MakeRatioTris")
Application.Run ("makep40qtrratiotri")
End If
Range("exh_rt") = lbl
TitleRange = Range("toc_ratio40")
If makepgnum = 1 Then Range("pgnumRatioTriQ").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ElseIf Title = "Ratio Triangle Annual" Then
If crit1 = "X: Incurred" Then crit1 = 1
If crit1 = "X: Paid" Then crit1 = 2
If crit1 = "X: Pd ALAE" Then crit1 = 3
If crit1 = "X: Open Counts" Then crit1 = 4
If crit1 = "X: Total Counts" Then crit1 = 5
If crit1 = "X: Earned Premium" Then crit1 = 6
If crit1 = "X: Incurred (Resv)" Then crit1 = 7
If crit1 = "X: Paid (Resv)" Then crit1 = 8
If crit1 = "X: Pd ALAE (Resv)" Then crit1 = 9
If crit1 = "X: Open Counts (Resv)" Then crit1 = 10
If crit1 = "X: Total Counts (Resv)" Then crit1 = 11
If crit1 = "X: Earned Premium (Resv)" Then crit1 = 12
If crit2 = "Y: Incurred" Then crit2 = 1
If crit2 = "Y: Paid" Then crit2 = 2
If crit2 = "Y: Pd ALAE" Then crit2 = 3
If crit2 = "Y: Open Counts" Then crit2 = 4
If crit2 = "Y: Total Counts" Then crit2 = 5
If crit2 = "Y: Earned Premium" Then crit2 = 6
If crit2 = "Y: Incurred (Resv)" Then crit2 = 7
If crit2 = "Y: Paid (Resv)" Then crit2 = 8
If crit2 = "Y: Pd ALAE (Resv)" Then crit2 = 9
If crit2 = "Y: Open Counts (Resv)" Then crit2 = 10
If crit2 = "Y: Total Counts (Resv)" Then crit2 = 11
If crit2 = "Y: Earned Premium (Resv)" Then crit2 = 12
If crit3 = "= X/Y" Then crit3 = 1
If crit3 = "= 1 - (X/Y)" Then crit3 = 2
If crit3 = "= c * (X/Y)" Then crit3 = 3
If crit4 = "Percent (0)" Then crit4 = 1
If crit4 = "Percent (1)" Then crit4 = 2
If crit4 = "Percent (2)" Then crit4 = 3
If crit4 = "Percent (3)" Then crit4 = 4
If crit4 = "Percent (4)" Then crit4 = 5
If crit4 = "Percent (5)" Then crit4 = 6
If crit4 = "General (0)" Then crit4 = 7
If crit4 = "General (1)" Then crit4 = 8
If crit4 = "General (2)" Then crit4 = 9
If crit4 = "General (3)" Then crit4 = 10
If crit4 = "General (4)" Then crit4 = 11
If crit4 = "General (5)" Then crit4 = 12
If crit4 = "Comma" Then crit4 = 13
If (Range("ratio_num0") <> crit1 Or Range("ratio_denom0") <> crit2 Or Range("rt_formula") <> crit3 Or Range("rt_format1") <> crit4) Or Range("IsRatioRun2") <> 2 Then
Range("ratio_num0") = crit1
Range("ratio_denom0") = crit2
Range("rt_formula") = crit3
Range("rt_format1") = crit4
'add level for rt_trirec
Application.Run ("MakeRatioTris")
Application.Run ("makep40qtrratiotri")
End If
Application.Run ("makeAnnualratiotris")
Range("annualexh_rt") = lbl
TitleRange = Range("annualtoc_ratio40")
Worksheets("P-AnnualRatioTri").Activate
If makepgnum = 1 Then Range("pgnumRatioTriA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut
ElseIf Title = "Quarterly Ratio Triangle Graph" Then
If crit1 = "X: Incurred" Then crit1 = 1
If crit1 = "X: Paid" Then crit1 = 2
If crit1 = "X: Pd ALAE" Then crit1 = 3
If crit1 = "X: Open Counts" Then crit1 = 4
If crit1 = "X: Total Counts" Then crit1 = 5
If crit1 = "X: Earned Premium" Then crit1 = 6
If crit1 = "X: Incurred (Resv)" Then crit1 = 7
If crit1 = "X: Paid (Resv)" Then crit1 = 8
If crit1 = "X: Pd ALAE (Resv)" Then crit1 = 9
If crit1 = "X: Open Counts (Resv)" Then crit1 = 10
If crit1 = "X: Total Counts (Resv)" Then crit1 = 11
If crit1 = "X: Earned Premium (Resv)" Then crit1 = 12
If crit2 = "Y: Incurred" Then crit2 = 1
If crit2 = "Y: Paid" Then crit2 = 2
If crit2 = "Y: Pd ALAE" Then crit2 = 3
If crit2 = "Y: Open Counts" Then crit2 = 4
If crit2 = "Y: Total Counts" Then crit2 = 5
If crit2 = "Y: Earned Premium" Then crit2 = 6
If crit2 = "Y: Incurred (Resv)" Then crit2 = 7
If crit2 = "Y: Paid (Resv)" Then crit2 = 8
If crit2 = "Y: Pd ALAE (Resv)" Then crit2 = 9
If crit2 = "Y: Open Counts (Resv)" Then crit2 = 10
If crit2 = "Y: Total Counts (Resv)" Then crit2 = 11
If crit2 = "Y: Earned Premium (Resv)" Then crit2 = 12
If crit3 = "= X/Y" Then crit3 = 1
If crit3 = "= 1 - (X/Y)" Then crit3 = 2
If crit3 = "= c * (X/Y)" Then crit3 = 3
If crit4 = "Percent (0)" Then crit4 = 1
If crit4 = "Percent (1)" Then crit4 = 2
If crit4 = "Percent (2)" Then crit4 = 3
If crit4 = "Percent (3)" Then crit4 = 4
If crit4 = "Percent (4)" Then crit4 = 5
If crit4 = "Percent (5)" Then crit4 = 6
If crit4 = "General (0)" Then crit4 = 7
If crit4 = "General (1)" Then crit4 = 8
If crit4 = "General (2)" Then crit4 = 9
If crit4 = "General (3)" Then crit4 = 10
If crit4 = "General (4)" Then crit4 = 11
If crit4 = "General (5)" Then crit4 = 12
If crit4 = "Comma" Then crit4 = 13
If (Range("ratio_num0") <> crit1 Or Range("ratio_denom0") <> crit2 Or Range("rt_formula") <> crit3 Or Range("rt_format1") <> crit4) Or Range("IsRatioRun2") <> 2 Then
Range("ratio_num0") = crit1
Range("ratio_denom0") = crit2
Range("rt_formula") = crit3
Range("rt_format1") = crit4
'add level for rt_trirec
Application.Run ("MakeRatioTris")
Application.Run ("makep40qtrratiotri")
End If
Range("exh_rtg") = lbl
TitleRange = Range("toc_ratio40")
Sheets("P-40QtrRatioTri Graph").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
Selection.TickLabels.Font.size = 18
ActiveChart.SeriesCollection(5).DataLabels.Select
Selection.Font.size = 12
If Range("firstcell_p40QtrRatioTri").Offset(-1, 0) < 40 Then
Application.Run ("RTGraph_less_than_40")
End If
Worksheets("P-40QtrRatioTri Graph").Select
If makepgnum = 1 Then Range("pgnumRTGQ").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("Graphdatestart") = "" & Range("firstcell_p40QtrRatioTri").Offset(0, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(0, 1)
Range("Graphdatestart").Offset(0, 4) = "" & Range("firstcell_p40QtrRatioTri").Offset(4, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(4, 1)
Range("Graphdatestart").Offset(0,
= "" & Range("firstcell_p40QtrRatioTri").Offset(8, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(8, 1)
Range("Graphdatestart").Offset(0, 12) = "" & Range("firstcell_p40QtrRatioTri").Offset(12, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(12, 1)
Range("Graphdatestart").Offset(0, 16) = "" & Range("firstcell_p40QtrRatioTri").Offset(16, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(16, 1)
Range("Graphdatestart").Offset(0, 20) = "" & Range("firstcell_p40QtrRatioTri").Offset(20, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(20, 1)
Range("Graphdatestart").Offset(0, 24) = "" & Range("firstcell_p40QtrRatioTri").Offset(24, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(24, 1)
Range("Graphdatestart").Offset(0, 28) = "" & Range("firstcell_p40QtrRatioTri").Offset(28, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(28, 1)
Range("Graphdatestart").Offset(0, 32) = "" & Range("firstcell_p40QtrRatioTri").Offset(32, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(32, 1)
Range("Graphdatestart").Offset(0, 36) = "" & Range("firstcell_p40QtrRatioTri").Offset(36, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(36, 1)
Range("Graphdateend") = "" & Range("firstcell_p40QtrRatioTri").Offset(39, 0) & " Q" & Range("firstcell_p40QtrRatioTri").Offset(39, 1)
ElseIf Title = "Ratio Triangle Annual Graph" Then
If crit1 = "X: Incurred" Then crit1 = 1
If crit1 = "X: Paid" Then crit1 = 2
If crit1 = "X: Pd ALAE" Then crit1 = 3
If crit1 = "X: Open Counts" Then crit1 = 4
If crit1 = "X: Total Counts" Then crit1 = 5
If crit1 = "X: Earned Premium" Then crit1 = 6
If crit1 = "X: Incurred (Resv)" Then crit1 = 7
If crit1 = "X: Paid (Resv)" Then crit1 = 8
If crit1 = "X: Pd ALAE (Resv)" Then crit1 = 9
If crit1 = "X: Open Counts (Resv)" Then crit1 = 10
If crit1 = "X: Total Counts (Resv)" Then crit1 = 11
If crit1 = "X: Earned Premium (Resv)" Then crit1 = 12
If crit2 = "Y: Incurred" Then crit2 = 1
If crit2 = "Y: Paid" Then crit2 = 2
If crit2 = "Y: Pd ALAE" Then crit2 = 3
If crit2 = "Y: Open Counts" Then crit2 = 4
If crit2 = "Y: Total Counts" Then crit2 = 5
If crit2 = "Y: Earned Premium" Then crit2 = 6
If crit2 = "Y: Incurred (Resv)" Then crit2 = 7
If crit2 = "Y: Paid (Resv)" Then crit2 = 8
If crit2 = "Y: Pd ALAE (Resv)" Then crit2 = 9
If crit2 = "Y: Open Counts (Resv)" Then crit2 = 10
If crit2 = "Y: Total Counts (Resv)" Then crit2 = 11
If crit2 = "Y: Earned Premium (Resv)" Then crit2 = 12
If crit3 = "= X/Y" Then crit3 = 1
If crit3 = "= 1 - (X/Y)" Then crit3 = 2
If crit3 = "= c * (X/Y)" Then crit3 = 3
If crit4 = "Percent (0)" Then crit4 = 1
If crit4 = "Percent (1)" Then crit4 = 2
If crit4 = "Percent (2)" Then crit4 = 3
If crit4 = "Percent (3)" Then crit4 = 4
If crit4 = "Percent (4)" Then crit4 = 5
If crit4 = "Percent (5)" Then crit4 = 6
If crit4 = "General (0)" Then crit4 = 7
If crit4 = "General (1)" Then crit4 = 8
If crit4 = "General (2)" Then crit4 = 9
If crit4 = "General (3)" Then crit4 = 10
If crit4 = "General (4)" Then crit4 = 11
If crit4 = "General (5)" Then crit4 = 12
If crit4 = "Comma" Then crit4 = 13
If (Range("ratio_num0") <> crit1 Or Range("ratio_denom0") <> crit2 Or Range("rt_formula") <> crit3 Or Range("rt_format1") <> crit4) And Range("IsRatioRun2") <> 2 Then
Range("ratio_num0") = crit1
Range("ratio_denom0") = crit2
Range("rt_formula") = crit3
Range("rt_format1") = crit4
'add level for rt_trirec
Application.Run ("MakeRatioTris")
Application.Run ("makep40qtrratiotri")
End If
Application.Run ("makeAnnualratiotris")
Range("annualexh_rtg") = lbl
TitleRange = Range("annualtoc_ratio40")
Sheets("P-AnnualRatioTri Graph").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
Selection.TickLabels.Font.size = 18
ActiveChart.SeriesCollection(5).DataLabels.Select
Selection.Font.size = 12
Worksheets("P-AnnualRatioTri Graph").Activate
Range("A1").Select
ActiveSheet.Shapes.Range("Text Box 8").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 2).Text
ActiveSheet.Shapes.Range("Text Box 9").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 3).Text
ActiveSheet.Shapes.Range("Text Box 10").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 4).Text
ActiveSheet.Shapes.Range("Text Box 11").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 5).Text
ActiveSheet.Shapes.Range("Text Box 13").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 6).Text
ActiveSheet.Shapes.Range("Text Box 17").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 3).Text
ActiveSheet.Shapes.Range("Text Box 18").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 4).Text
ActiveSheet.Shapes.Range("Text Box 19").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 5).Text
ActiveSheet.Shapes.Range("Text Box 21").Select
Selection.Characters.Text = Range("Annualtoc_ratio40").Offset(2, 6).Text
If Range("Annualfirstcell_p40QtrRatioTri").Offset(-1, 0) < 40 Then
Application.Run ("RTAGraph_less_than_40")
End If
Worksheets("P-AnnualRatioTri Graph").Select
If makepgnum = 1 Then Range("pgnumRTGA").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("AnnualGraphdatestart") = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(0, 0)
'Range("AnnualGraphdatestart").Offset(0, 4) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(4, 0)
Range("AnnualGraphdatestart").Offset(0,
= "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(8, 0)
'Range("AnnualGraphdatestart").Offset(0, 12) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(12, 0)
Range("AnnualGraphdatestart").Offset(0, 16) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(16, 0)
'Range("AnnualGraphdatestart").Offset(0, 20) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(20, 0)
Range("AnnualGraphdatestart").Offset(0, 24) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(24, 0)
'Range("AnnualGraphdatestart").Offset(0, 28) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(28, 0)
Range("AnnualGraphdatestart").Offset(0, 32) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(32, 0)
'Range("AnnualGraphdatestart").Offset(0, 36) = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(36, 0)
Range("AnnualGraphdateend") = "" & Range("Annualfirstcell_p40QtrRatioTri").Offset(39, 0)
ElseIf Title = "A vs E" Then
If crit1 = "Incurred Loss" Then crit1 = 1
If crit1 = "Paid Loss" Then crit1 = 2
If crit1 = "Paid ALAE" Then crit1 = 3
If crit1 = "Counts Open" Then crit1 = 4
If crit1 = "Counts O&C" Then crit1 = 5
If crit2 = "Development Triangle" Then crit2 = 1
If crit2 = "Reserve Triangle" Then crit2 = 2
Worksheets("Act vs Exp").Activate
Worksheets("Act vs Exp").Range("ave_type2") = crit1
Worksheets("Act vs Exp").Range("ave_resdev") = crit2
Application.Run ("MakeAveCum")
Range("exh_AvE1") = lbl
Worksheets("P-32QtrAve").Activate
TitleRange = Range("toc_ave32")
If makepgnum = 1 Then
Range("pgnumAvE1").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
Range("pgnumAvE2").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum + 1
Range("pgnumAvE3").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum + 2
pgnum = pgnum + 2
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ElseIf Title = "E vs A" Then
Application.Run ("Make_Est_Vs_Act")
If makepgnum = 1 Then Worksheets("Exp Vs Act").Range("X" & size + 17) = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ElseIf Title = "ATA Distribution Graphs" Then
Worksheets("Graphs").Select
Range("A1") = crit1
Worksheets(crit1).Activate
Application.Run ("Graphs")
Range("exh_Freq1") = lbl
TitleRange = Range("toc_ATAdistgraph")
If makepgnum = 1 Then Range("pgnumATAG").Formula = Month(Now) & "/" & Day(Now) & "/" & Year(Now) & " Page " & pgnum
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Worksheets("Graphs").Select
For X = 1 To 20
ActiveSheet.Shapes("Picture " & X).Delete
Next X
Range("Graphcleardata").ClearContents
Range("Graphcleardata2").ClearContents
Range("Graphcleardata3").ClearContents
Range("Graphcleardata4").ClearContents
' FreqX = FreqX + 20
End If
Next q
Sheets("FullQtrTri").Visible = False
Sheets("Ratio Tri").Visible = False
Sheets("Ratio Numerator").Visible = False
Sheets("Ratio Denominator").Visible = False
Sheets("Annual Tri").Visible = False
Sheets("Annual Tri 2").Visible = False
Sheets("Graphs").Visible = False
Sheets("Graph Calc").Visible = False
Sheets("Annual Ratio Tri").Visible = False
Sheets("Annual Ratio Tri (2)").Visible = False
Sheets("Annual Ratio Numerator").Visible = False
Sheets("Annual Ratio Denominator").Visible = False
Sheets("P-40QtrSummary").Visible = False
Sheets("P-40QtrACER").Visible = False
Unload FormPrint
Worksheets("Print").Activate
Range("print_table_start").Offset(1, 0).Select
End
End Sub
******************************************************************************************
Private Sub SpinButton1_SpinDown()
tb1 = Int(TextBox1.Value)
If tb1 = 1 Then GoTo line2
If Label9.Caption <> "" Then GoTo line2
origtb = tb1
tb1 = tb1 - 1
g = 1
Do Until g = 0
g = tb1
If Label1.Caption = tb1 Then tb1 = tb1 - 1
If Label2.Caption = tb1 Then tb1 = tb1 - 1
If Label3.Caption = tb1 Then tb1 = tb1 - 1
If Label4.Caption = tb1 Then tb1 = tb1 - 1
If Label5.Caption = tb1 Then tb1 = tb1 - 1
If Label6.Caption = tb1 Then tb1 = tb1 - 1
If Label7.Caption = tb1 Then tb1 = tb1 - 1
If Label8.Caption = tb1 Then tb1 = tb1 - 1
If Label9.Caption = tb1 Then tb1 = tb1 - 1
If Label19.Caption = tb1 Then tb1 = tb1 - 1
If Label20.Caption = tb1 Then tb1 = tb1 - 1
If Label21.Caption = tb1 Then tb1 = tb1 - 1
If Label22.Caption = tb1 Then tb1 = tb1 - 1
If Label23.Caption = tb1 Then tb1 = tb1 - 1
If Label24.Caption = tb1 Then tb1 = tb1 - 1
If Label25.Caption = tb1 Then tb1 = tb1 - 1
If Label26.Caption = tb1 Then tb1 = tb1 - 1
If Label27.Caption = tb1 Then tb1 = tb1 - 1
If Label28.Caption = tb1 Then tb1 = tb1 - 1
If Label29.Caption = tb1 Then tb1 = tb1 - 1
If Label30.Caption = tb1 Then tb1 = tb1 - 1
If Label31.Caption = tb1 Then tb1 = tb1 - 1
If Label32.Caption = tb1 Then tb1 = tb1 - 1
If Label33.Caption = tb1 Then tb1 = tb1 - 1
If tb1 = g Then g = 0
If tb1 < 1 Then g = 0
If tb1 < 1 Then tb1 = origtb
Loop
TextBox1.Value = tb1
line2:
End Sub
Private Sub SpinButton1_SpinUp()
tb1 = Int(TextBox1.Value)
size = Range("counter").Value
origtb = tb1
If tb1 = size Then GoTo line1
If Label9.Caption <> "" Then GoTo line1
tb1 = tb1 + 1
g = 1
Do Until g = 0
g = tb1
If Label1.Caption = tb1 Then tb1 = tb1 + 1
If Label2.Caption = tb1 Then tb1 = tb1 + 1
If Label3.Caption = tb1 Then tb1 = tb1 + 1
If Label4.Caption = tb1 Then tb1 = tb1 + 1
If Label5.Caption = tb1 Then tb1 = tb1 + 1
If Label6.Caption = tb1 Then tb1 = tb1 + 1
If Label7.Caption = tb1 Then tb1 = tb1 + 1
If Label8.Caption = tb1 Then tb1 = tb1 + 1
If Label9.Caption = tb1 Then tb1 = tb1 + 1
If Label19.Caption = tb1 Then tb1 = tb1 + 1
If Label20.Caption = tb1 Then tb1 = tb1 + 1
If Label21.Caption = tb1 Then tb1 = tb1 + 1
If Label22.Caption = tb1 Then tb1 = tb1 + 1
If Label23.Caption = tb1 Then tb1 = tb1 + 1
If Label24.Caption = tb1 Then tb1 = tb1 + 1
If Label25.Caption = tb1 Then tb1 = tb1 + 1
If Label26.Caption = tb1 Then tb1 = tb1 + 1
If Label27.Caption = tb1 Then tb1 = tb1 + 1
If Label28.Caption = tb1 Then tb1 = tb1 + 1
If Label29.Caption = tb1 Then tb1 = tb1 + 1
If Label30.Caption = tb1 Then tb1 = tb1 + 1
If Label31.Caption = tb1 Then tb1 = tb1 + 1
If Label32.Caption = tb1 Then tb1 = tb1 + 1
If Label33.Caption = tb1 Then tb1 = tb1 + 1
If tb1 = g Then g = 0
If tb1 > size Then g = 0
If tb1 > size Then tb1 = origtb
Loop
TextBox1.Value = tb1
line1:
End Sub