Notes-VBA-自用

11-19 For TK

Sub CF抓取分拆() ' ' 宏1 ' Data页为prophet查询结果
' 逐产品逐mp导出
'
Dim aa
aa = Sheets.Count
Do While aa > 2
    aa = Sheets.Count
    Application.DisplayAlerts = False
    Sheets(aa).Delete
Loop

Dim i As Integer
Dim j As Integer
Dim row_max As Integer
Dim column_max As Integer
row_max = Sheet1.Range("a100").End(xlUp).Row
 For i = 1 To row_max
 Sheets("Data").Range("B6") = Sheet1.Cells(i, 1)
 column_max = Sheet1.Cells(i, 100).End(xlToLeft).Column - 1
   For j = 1 To column_max
   Sheets("Data").Range("B4") = Sheet1.Cells(i, j + 1)
    Worksheets.Add after:=Sheets("Data")
    ActiveSheet.Name = Sheet1.Cells(i, 1) & "-" & Sheet1.Cells(i, j + 1)
    Sheets("Data").Cells.Copy
    Sheets(Sheet1.Cells(i, 1) & "-" & Sheet1.Cells(i, j + 1)).Range("a1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Next
 Next
  
End Sub
 

 

 

 

 

11-01 For EC

Sub cal_stress_scene()
'
' 9 base
' 1 mort        11 mort2
' 2 morb        12 morb2
' 3 lapse up    13 lapse up2
' 4 lapse down  14 lapse down2

For i = 1 To 10
    Sheets("2-1非万能险导出").Range("B2") = Sheets("SEN TEST").Cells(1, 1 + 2 * i)
    Application.Goto Sheets("2-1非万能险导出").Range("O9")
    Range(Selection, Selection.End(xlDown)).Copy
    Application.Goto Sheets("SEN TEST").Cells(5, 2 * i)
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Goto Sheets("2-1非万能险导出").Range("k9")
    Range(Selection, Selection.End(xlDown)).Copy
    
        Application.Goto Sheets("SEN TEST").Cells(5, 2 * i + 1)
                
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next
               

'单独调整PHI Run_no=3/13
            Sheets("2-1非万能险导出").Range("B2") = 3
                Sheets("SEN TEST").Range("H91") = Sheets("2-1非万能险导出").Range("O95")
                Sheets("SEN TEST").Range("I91") = Sheets("2-1非万能险导出").Range("k95")
                
            Sheets("2-1非万能险导出").Range("B2") = 13
                Sheets("SEN TEST").Range("J91") = Sheets("2-1非万能险导出").Range("O95")
                Sheets("SEN TEST").Range("K91") = Sheets("2-1非万能险导出").Range("k95")
                    
End Sub