VBA：Triangle to Table

Alonso

发布于：2022年1月10日

次浏览

用VBA把二维表转为一维表。

There could be alternative ways to analyze triangle data in Python.

But initially, I'd like to convert the triangle data to a table. The table can then be analyzed with Python.

The following is a triangle data set:

Each blue cell has three attributes:

ValuationYear: The time in which conducting valuation.
AccidentYear: The time in which the claim happened.
OccurenceYear: The time in which the number in the cell occured.

The attributes for the selected cell are:

ValuationYear: 2020
AccidentYear: 2018
OccurenceYear: 2020

This is the final table I'd want to see:

Here is the VBA code:

Sub angletotable()

'select the correct column

Dim wb1 As Workbook

Set wb1 = ThisWorkbook

Dim rg As Range

Set rg = Selection

Dim countrows As Integer

countrows = rg.Rows.Count

Dim vy As Integer

vy = rg.Cells(countrows, 0).Value


'creat a new workbook

Dim wb2 As Workbook, sht As Worksheet
Set wb2 = Workbooks.Add
Set sht = wb2.Worksheets(1)
With sht
    .Name = "Table"
    .Range("A1:D1") = Array("ValuationYear", "AccidentYear", "OccurenceYear", "Amount")
End With

'valuationyear
sht.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize((countrows ^ 2 + countrows) / 2, 1) = vy

Dim rgi As Range
Dim i As Integer
Dim lastrow As Long
Dim ay As Integer


For i = 1 To countrows
        Set rgi = rg.Cells(i, 1).Resize(1, countrows + 1 - i)
        rgi.Copy
        sht.Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True

        ay = rgi.Cells(1, 0).Value
        sht.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(countrows + 1 - i, 1) = ay

        For j = 0 To countrows - i
            sht.Range("C" & Rows.Count).End(xlUp).Offset(1) = ay + j
        Next j

Next i


End Sub