004_comission/natalie0312
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
natalie0312/task3/ThisWorkbook.cls
louiscklaw ec32653f3a update,
2025-02-01 02:04:23 +08:00

1108 lines
48 KiB
OpenEdge ABL
Raw Permalink Blame History

' change log:
Option Explicit
Dim first_row As Integer
Dim current_row As Integer
Dim last_check_row As Integer
Dim ADJUSTMENT_WORKSHEET As String
Dim RAW_DATA_WORKSHEET as String
Dim BMF_ADJUSTMENT_WORKSHEET As String
dim BMF_1690_2690_WORKSHEET as String
' bmf_1690_2690 sheet
Dim COL_BMF_1690_2690_BMF_ID as String
Dim COL_BMF_1690_2690_CLOSING_DATE as String
Dim COL_BMF_1690_2690_IFRS as String
Dim COL_BMF_1690_2690_BMF_REFERENCE_ID as String
Dim COL_BMF_1690_2690_PCCO as String
Dim COL_BMF_1690_2690_PCEC as String
Dim COL_BMF_1690_2690_LOCAL_DESK as String
Dim COL_BMF_1690_2690_NA as String
Dim COL_BMF_1690_2690_FISE as String
Dim COL_BMF_1690_2690_LOCAL_OPERATIONAL as String
Dim COL_BMF_1690_2690_LINE_TYPE as String
Dim COL_BMF_1690_2690_CONTRACT_REFERENCE_ID as String
Dim COL_BMF_1690_2690_AMOUNT_IN_ACCT as String
Dim COL_BMF_1690_2690_AMOUNT_IN_FUNCT as String
' Raw Data BALANCE_SHEET
Dim COL_BALANCE_SHEET_NA_1 As String
Dim COL_BALANCE_SHEET_PCCO_1 As String
Dim COL_BALANCE_SHEET_PCEC_1 As String
Dim COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 As String
Dim COL_BALANCE_SHEET_NA_2 As String
Dim COL_BALANCE_SHEET_PCCO_2 As String
Dim COL_BALANCE_SHEET_PCEC_2 As String
Dim COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 As String
' Raw Data OFF_BALANCE_SHEET
Dim COL_OFF_BALANCE_SHEET_NA_1 As String
Dim COL_OFF_BALANCE_SHEET_PCCO_1 As String
Dim COL_OFF_BALANCE_SHEET_PCEC_1 As String
Dim COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_1 As String
Dim COL_OFF_BALANCE_SHEET_NA_2 As String
Dim COL_OFF_BALANCE_SHEET_PCCO_2 As String
Dim COL_OFF_BALANCE_SHEET_PCEC_2 As String
Dim COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_2 As String
Dim COL_FINAL_BMF_ID As String
'BMF 1690 2690
Dim COL_BMF16902690_SHEET_BMF_REFERENCE_ID As String 'Column C
Dim COL_BMF16902690_SHEET_PCCO_CODE As String 'Column E
Dim COL_BMF16902690_SHEET_PCEC_CODE As String 'Column F
Dim COL_BMF16902690_SHEET_NA As String 'Column H
Dim COL_BMF16902690_SHEET_AMOUNT_TO_ADJ As String 'Column M
'BMF Adjustment
Dim COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID As String 'Column A
Dim COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE As String 'Column B
Dim COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE As String 'Column D
Dim COL_BMF_ADJUSTMENT_SHEET_COMMENT As String 'Column E
Dim COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY As String 'Column F
Dim COL_BMF_ADJUSTMENT_SHEET_PCCO As String 'Column G
Dim COL_BMF_ADJUSTMENT_SHEET_PCEC As String 'Column H
Dim COL_BMF_ADJUSTMENT_SHEET_ACCOUNTING_BALANCE_CURRENCY As String 'Column I
Dim COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE As String 'Column J
Dim COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT As String 'Column K
Dim COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE As String 'Column M
Dim COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG As String 'Column N
Dim COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG As String 'Column O
Dim COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ As String 'Column P
Dim COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT As String 'Column Q
Dim COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD As String 'Column R
Dim COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE As String 'Column S
Dim COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY As String 'Column BJ
Dim COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT As String 'Column BN
Dim RED_THOUSAND_SEP_NUMBER_FORMAT As String
Function highlightBmfAdjustmentBmfId(current_row As Integer)
On Error GoTo eh
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(current_row)).Interior.Color = rgb(253, 203, 110)
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(current_row)).Font.Color = vbBlack
Done:
Exit Function
eh:
Debug.Print "highlightBmfAdjustmentBmfId:Error: " & Err.Description
End Function
Sub initBmf16902690()
COL_BMF_1690_2690_BMF_ID = "C"
COL_BMF_1690_2690_CLOSING_DATE ="A"
COL_BMF_1690_2690_IFRS ="B"
COL_BMF_1690_2690_BMF_REFERENCE_ID ="D"
COL_BMF_1690_2690_PCCO ="E"
COL_BMF_1690_2690_PCEC ="F"
COL_BMF_1690_2690_LOCAL_DESK ="G"
COL_BMF_1690_2690_NA ="H"
COL_BMF_1690_2690_FISE ="I"
COL_BMF_1690_2690_LOCAL_OPERATIONAL ="J"
COL_BMF_1690_2690_LINE_TYPE ="K"
COL_BMF_1690_2690_CONTRACT_REFERENCE_ID ="L"
COL_BMF_1690_2690_AMOUNT_IN_ACCT ="M"
COL_BMF_1690_2690_AMOUNT_IN_FUNCT ="N"
end sub
Sub init()
On Error GoTo eh
RED_THOUSAND_SEP_NUMBER_FORMAT = "#,##0_);[Red](#,##0)"
' HEADER_ROW = 6
first_row = 6
current_row = first_row
last_check_row = 9999
' ADJUSTMENT_SHEET
ADJUSTMENT_WORKSHEET = "Raw Data"
RAW_DATA_WORKSHEET = "Raw Data"
BMF_1690_2690_WORKSHEET = "BMF 1690 2690"
' BALANCE_SHEET
COL_BALANCE_SHEET_NA_1 = "Q"
COL_BALANCE_SHEET_PCCO_1 = "R"
COL_BALANCE_SHEET_PCEC_1 = "S"
COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 = "T"
COL_BALANCE_SHEET_NA_2 = "U"
COL_BALANCE_SHEET_PCCO_2 = "V"
COL_BALANCE_SHEET_PCEC_2 = "W"
COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 = "X"
' OFF_BALANCE_SHEET
COL_OFF_BALANCE_SHEET_NA_1 = "Z"
COL_OFF_BALANCE_SHEET_PCCO_1 = "AA"
COL_OFF_BALANCE_SHEET_PCEC_1 = "AB"
COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_1 = "AC"
COL_OFF_BALANCE_SHEET_NA_2 = "AD"
COL_OFF_BALANCE_SHEET_PCCO_2 = "AE"
COL_OFF_BALANCE_SHEET_PCEC_2 = "AF"
COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_2 = "AG"
COL_FINAL_BMF_ID = "AM"
' BMF_ADJUSTMENT_SHEET
BMF_ADJUSTMENT_WORKSHEET = "BMF Adjustment"
COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID = "A"
COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE = "B"
COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE = "D"
COL_BMF_ADJUSTMENT_SHEET_COMMENT = "E"
COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY = "F"
COL_BMF_ADJUSTMENT_SHEET_PCCO = "G"
COL_BMF_ADJUSTMENT_SHEET_PCEC = "H"
COL_BMF_ADJUSTMENT_SHEET_ACCOUNTING_BALANCE_CURRENCY = "I"
COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE = "J"
COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT = "K"
COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE = "M"
COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG = "N"
COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG = "O"
COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ = "P"
COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT = "Q"
COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD = "R"
COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE = "S"
COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY = "BJ"
COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT = "BN"
Done:
Debug.Print "init done"
Exit Sub
eh:
Debug.Print "init:Error: " & Err.Description
End Sub
Function paintYellow(cell As Variant)
cell.Interior.Color = RGB(246, 229, 141)
paintYellow = True
End Function
Function paintRed(cell As Variant)
cell.Interior.Color = RGB(255, 121, 121)
paintRed = True
End Function
Function CheckIfOffBalanceSheetAmountToAdjust1NoNeedCopy(current_row As Integer, bmf_id As String, pcco As String)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(current_row)).Value
If (temp = "") Then
output = True
ElseIf (temp = "0") Then
paintYellow (Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(current_row)))
output = True
ElseIf (checkIfAlreadyExistInBmfAdjustmentResult(bmf_id, pcco, current_row)) Then
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range("AI" & CStr(current_row)))
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_1 & CStr(current_row)))
output = True
End If
CheckIfOffBalanceSheetAmountToAdjust1NoNeedCopy = output
Done:
Exit Function
eh:
Debug.Print "CheckIfOffBalanceSheetAmountToAdjust1NoNeedCopy:Error: " & Err.Description
End Function
Function CheckIfAdjustmentSheetBmfIdValid(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(current_row)).Value
If (temp <> "") Then
output = True
End If
CheckIfAdjustmentSheetBmfIdValid = output
Done:
Exit Function
eh:
Debug.Print "CheckIfAdjustmentSheetBmfIdValid:Error: " & Err.Description
End Function
Function CheckIfOffBalanceSheetAmountToAdjust2NoNeedCopy(current_row As Integer, bmf_id As String, pcco As String)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(current_row)).Value
If (temp = "") Then
output = True
ElseIf (temp = "0") Then
paintYellow (Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(current_row)))
output = True
ElseIf (checkIfAlreadyExistInBmfAdjustmentResult(bmf_id, pcco, current_row)) Then
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range("AI" & CStr(current_row)))
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_2 & CStr(current_row)))
output = True
End If
CheckIfOffBalanceSheetAmountToAdjust2NoNeedCopy = output
Done:
Exit Function
eh:
Debug.Print "CheckIfOffBalanceSheetAmountToAdjust2NoNeedCopy:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetAmountToAdjust1NoNeedCopy(current_row As Integer, bmf_id As String, pcco As String)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(current_row)).Value
Debug.Print "CheckIfBalanceSheetAmountToAdjust1NoNeedCopy:" & temp = "0"
If (temp = "") Then
output = True
ElseIf (temp = "0") Then
paintYellow (Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(current_row)))
output = True
ElseIf (checkIfAlreadyExistInBmfAdjustmentResult(bmf_id, pcco, current_row)) Then
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range("AI" & CStr(current_row)))
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_1 & CStr(current_row)))
output = True
End If
CheckIfBalanceSheetAmountToAdjust1NoNeedCopy = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetAmountToAdjust1NoNeedCopy:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetAmountToAdjust2NoNeedCopy(current_row As Integer, bmf_id As String, pcco As String)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(current_row)).Value
If (temp = "") Then
output = True
ElseIf (temp = "0") Then
paintYellow (Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(current_row)))
output = True
ElseIf (checkIfAlreadyExistInBmfAdjustmentResult(bmf_id, pcco, current_row)) Then
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range("AI" & CStr(current_row)))
paintRed (Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_2 & CStr(current_row)))
output = True
End If
CheckIfBalanceSheetAmountToAdjust2NoNeedCopy = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetAmountToAdjust2NoNeedCopy:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetNa2Exist(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_NA_2 & CStr(current_row)).Value
If (temp <> "") Then
output = True
End If
CheckIfBalanceSheetNa2Exist = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetNa2Exist:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetPcco2Exist(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_2 & CStr(current_row)).Value
If (temp <> "") Then
output = True
End If
CheckIfBalanceSheetPcco2Exist = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetPcco2Exist:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetPcec2Exist(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCEC_2 & CStr(current_row)).Value
If (temp <> "") Then
output = True
End If
CheckIfBalanceSheetPcco2Exist = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetPcco2Exist:Error: " & Err.Description
End Function
Function CheckIfBalanceSheetAmountToAdjust2Exist(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(current_row)).Value
If (temp <> "") Then
output = True
End If
CheckIfBalanceSheetAmountToAdjust2Exist = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBalanceSheetAmountToAdjust2Exist:Error: " & Err.Description
End Function
Function isCurrentRowNeedToProcess(input_row As Integer)
On Error GoTo eh
Dim output As Boolean
Dim cell_value As Currency
Dim input_cell As String
input_cell = GRANDTOTAL_COL & input_row
Done:
isCurrentRowNeedToProcess = Not (IsEmpty(Worksheets(RAW_DATA_WORKSHEET).Range(input_cell).Value))
Exit Function
eh:
Debug.Print "isCurrentRowNeedToProcess:Error: " & Err.Description
End Function
Function getLastCheckRow(first_row As Integer)
On Error GoTo eh
getLastCheckRow = first_row + 10
Done:
Exit Function
eh:
Debug.Print "getLastCheckRow:Error: " & Err.Description
End Function
Function checkAdjustmentSheetLastRow(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = True
Dim j As Integer
Dim temp As String
For j = current_row To getLastCheckRow(current_row)
temp = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(current_row)).Value
If (temp <> "") Then
output = False
End If
Next j
checkAdjustmentSheetLastRow = output
Done:
Exit Function
eh:
Debug.Print "checkAdjustmentSheetLastRow:Error: " & Err.Description
End Function
Function checkBmfAdjustmentSheetLastRow(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = True
Dim j As Integer
Dim temp As String
For j = current_row To getLastCheckRow(current_row)
temp = Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(current_row)).Value
If (temp <> "") Then
output = False
End If
Next j
checkBmfAdjustmentSheetLastRow = output
Done:
Exit Function
eh:
Debug.Print "checkBmfAdjustmentSheetLastRow:Error: " & Err.Description
End Function
Function checkIfAlreadyExistInBmfAdjustmentResult(adj_bmf_id As String, adj_pcco As String, adj_sheet_row As Integer)
On Error GoTo eh
Dim i As Integer
Dim bmf_adjustment_sheet_last_row As Integer
Dim found As Boolean
found = False
Dim temp_value As String
Dim bmf_adj_bmf_id As String
Dim bmf_adj_pcco As String
Dim bmf_adj_pcec As String
Application.ScreenUpdating = False
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
For i = 1 To bmf_adjustment_sheet_last_row
bmf_adj_bmf_id = Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(i)).Value
bmf_adj_pcco = Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(i)).Value
bmf_adj_pcec = Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(i)).Value
' debug.print bmf_adj_bmf_id
' debug.print bmf_adj_pcco
' debug.print adj_bmf_id
' debug.print adj_pcco
' debug.print bmf_adj_bmf_id = adj_bmf_id And bmf_adj_pcco = adj_pcco
If (bmf_adj_bmf_id = adj_bmf_id And bmf_adj_pcco = adj_pcco) Then
' Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("B"&cstr(i)).value = Cstr(bmf_adj_bmf_id = adj_bmf_id)
' Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("B"&cstr(i)).value = Cstr(bmf_adj_pcco = adj_pcco)
'Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(i)).Interior.Color = RGB(214, 48, 49)
paintRed (Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(i)))
' Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(i)).Interior.Color = RGB(214, 48, 49)
paintRed (Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(i)))
paintRed (Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(i)))
' adj_sheet_row
found = True
Exit For
End If
Next i
checkIfAlreadyExistInBmfAdjustmentResult = found
Application.ScreenUpdating = True
Done:
Exit Function
eh:
Application.ScreenUpdating = True
Debug.Print "checkIfAlreadyExistInBmfAdjustmentResult:Error: " & Err.Description
End Function
Function getBMFAdjustmentResultListEnd(start_row As Integer)
On Error GoTo eh
Dim i As Integer
Dim last_row As Boolean
Dim last_row_found As Boolean
last_row_found = False
last_row = True
current_row = start_row
For i = start_row To 9999
current_row = i
last_row_found = checkBmfAdjustmentSheetLastRow(current_row)
If (last_row_found = True) Then
Exit For
End If
Next i
getBMFAdjustmentResultListEnd = current_row - 1
Done:
Exit Function
eh:
Debug.Print "getBMFAdjustmentResultListEnd:Error: " & Err.Description
End Function
Function getAdjustmentSheetListEnd(start_row As Integer)
On Error GoTo eh
Dim i As Integer
Dim last_row As Boolean
Dim last_row_found As Boolean
last_row_found = False
last_row = True
current_row = start_row
For i = start_row To 9999
current_row = i
last_row_found = checkAdjustmentSheetLastRow(current_row)
If (last_row_found = True) Then
Exit For
End If
Next i
getAdjustmentSheetListEnd = current_row - 1
Done:
Exit Function
eh:
Debug.Print "getAdjustmentSheetListEnd:Error: " & Err.Description
End Function
Function checkBmf16902690SheetLastRow(current_row As Integer)
On Error GoTo eh
Dim output As Boolean
output = True
Dim j As Integer
Dim temp As String
For j = current_row To getLastCheckRow(current_row)
temp = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_BMF_ID & CStr(current_row)).Value
If (temp <> "") Then
output = False
End If
Next j
checkBmf16902690SheetLastRow = output
Done:
Exit Function
eh:
Debug.Print "checkBmf16902690SheetLastRow:Error: " & Err.Description
End Function
Function getBmf16902690SheetListEnd(start_row As Integer)
On Error GoTo eh
Dim i As Integer
Dim last_row As Boolean
Dim last_row_found As Boolean
last_row_found = False
last_row = True
current_row = start_row
For i = start_row To 9999
current_row = i
last_row_found = checkBmf16902690SheetLastRow(current_row)
If (last_row_found = True) Then
Exit For
End If
Next i
getBmf16902690SheetListEnd = current_row - 1
Done:
Exit Function
eh:
Debug.Print "getBmf16902690SheetListEnd:Error: " & Err.Description
End Function
Sub resetBmfAdjustmentSheet()
init
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("A4:BN9999").Clear
End Sub
Sub ProcessRawData()
On Error GoTo eh
Dim need_to_copy As Boolean
Dim adjustment_sheet_first_row As Integer
Dim adjustment_sheet_last_row As Integer
Dim adjustment_sheet_current_row As Integer
Dim bmf_adjustment_sheet_last_row As Integer
Dim bmf_adjustment_sheet_current_row As Integer
Dim temp_value As String
adjustment_sheet_first_row = 7
init
' MsgBox "helloworld"
adjustment_sheet_last_row = getAdjustmentSheetListEnd(adjustment_sheet_first_row)
Debug.Print adjustment_sheet_last_row
For adjustment_sheet_current_row = adjustment_sheet_first_row To adjustment_sheet_last_row
Debug.Print "processing adjustment_sheet_current_row: " & adjustment_sheet_current_row
' if (CheckIfBalanceSheetAmountToAdjust2Exist(adjustment_sheet_current_row) and CheckIfBalanceSheetNa2Exist(adjustment_sheet_current_row) and CheckIfBalanceSheetPcco2Exist(adjustment_sheet_current_row)) then
If (CheckIfAdjustmentSheetBmfIdValid(adjustment_sheet_current_row)) Then
Debug.Print "processing row: " & adjustment_sheet_current_row
Dim bmf_id As String
bmf_id = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(adjustment_sheet_current_row)).Value
Dim bal_pcco_1 As String
bal_pcco_1 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_1 & CStr(adjustment_sheet_current_row)).Value
Dim bal_pcco_2 As String
bal_pcco_2 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_2 & CStr(adjustment_sheet_current_row)).Value
Dim off_bal_pcco_1 As String
off_bal_pcco_1 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_1 & CStr(adjustment_sheet_current_row)).Value
Dim off_bal_pcco_2 As String
off_bal_pcco_2 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_2 & CStr(adjustment_sheet_current_row)).Value
Dim bal_pcec_1 As String
bal_pcec_1 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCEC_1 & CStr(adjustment_sheet_current_row)).Value
Dim bal_pcec_2 As String
bal_pcec_2 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCEC_2 & CStr(adjustment_sheet_current_row)).Value
Dim off_bal_pcec_1 As String
off_bal_pcec_1 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCEC_1 & CStr(adjustment_sheet_current_row)).Value
Dim off_bal_pcec_2 As String
off_bal_pcec_2 = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCEC_2 & CStr(adjustment_sheet_current_row)).Value
' balance sheet 1
' check need to copy ? see amount to adjust <> 0 ?
If (CheckIfBalanceSheetAmountToAdjust1NoNeedCopy(adjustment_sheet_current_row, bmf_id, bal_pcco_1) = True) Then
' do skip
Debug.Print "skip balance sheet 1"
Else
' do copy
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
bmf_adjustment_sheet_current_row = bmf_adjustment_sheet_last_row + 1
Debug.Print "bmf_adjustment_sheet_current_row:" & bmf_adjustment_sheet_current_row
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "14004"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "N"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "E"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "01"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE & CStr(bmf_adjustment_sheet_current_row)).Value = "DQ"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_COMMENT & CStr(bmf_adjustment_sheet_current_row)).Value = "Unsettled bond adjustment for RWA purpose"
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCEC_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_NA_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value & " " & "15130"
'If "Raw Data" tab "NA" is 1020/2020/8601/8701 <EFBFBD>> "Line type" fill Creation
if ("1020" = temp_value or "2020" = temp_value or "8601" = temp_value or "8701" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Creation"
end if
if ("1690" = temp_value or "2690" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Reversing"
end if
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("A" & bmf_adjustment_sheet_current_row & ":" & "BN" & bmf_adjustment_sheet_current_row).NumberFormat = "@"
' Selection.NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
End If
' balance sheet 2
' check need to copy ? see amount to adjust <> 0 ?
Debug.Print bal_pcco_2
If (CheckIfBalanceSheetAmountToAdjust2NoNeedCopy(adjustment_sheet_current_row, bmf_id, bal_pcco_2)) Then
' do skip
Debug.Print "skip balance sheet 2"
Else
' do copy
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
bmf_adjustment_sheet_current_row = bmf_adjustment_sheet_last_row + 1
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "14004"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "N"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "E"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "01"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE & CStr(bmf_adjustment_sheet_current_row)).Value = "DQ"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_COMMENT & CStr(bmf_adjustment_sheet_current_row)).Value = "Unsettled bond adjustment for RWA purpose"
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCCO_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_PCEC_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_BALANCE_SHEET_NA_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value & " " & "15130"
'If "Raw Data" tab "NA" is 1020/2020/8601/8701 <EFBFBD>> "Line type" fill Creation
if ("1020" = temp_value or "2020" = temp_value or "8601" = temp_value or "8701" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Creation"
end if
if ("1690" = temp_value or "2690" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Reversing"
end if
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("A" & bmf_adjustment_sheet_current_row & ":" & "BN" & bmf_adjustment_sheet_current_row).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
End If
' off-balance sheet 1
' check need to copy ? see amount to adjust <> 0 ?
If (CheckIfOffBalanceSheetAmountToAdjust1NoNeedCopy(adjustment_sheet_current_row, bmf_id, off_bal_pcco_1)) Then
' do skip
Debug.Print "skip off-balance sheet 1"
Else
' do copy
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
bmf_adjustment_sheet_current_row = bmf_adjustment_sheet_last_row + 1
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "14004"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "N"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "E"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "01"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE & CStr(bmf_adjustment_sheet_current_row)).Value = "DQ"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_COMMENT & CStr(bmf_adjustment_sheet_current_row)).Value = "Unsettled bond adjustment for RWA purpose"
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCEC_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_NA_1 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value & " " & "15130"
'If "Raw Data" tab "NA" is 1020/2020/8601/8701 <EFBFBD>> "Line type" fill Creation
if ("1020" = temp_value or "2020" = temp_value or "8601" = temp_value or "8701" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Creation"
end if
if ("1690" = temp_value or "2690" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Reversing"
end if
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("A" & bmf_adjustment_sheet_current_row & ":" & "BN" & bmf_adjustment_sheet_current_row).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
End If
' off-balance sheet 2
' check need to copy ? see amount to adjust <> 0 ?
If (CheckIfOffBalanceSheetAmountToAdjust2NoNeedCopy(adjustment_sheet_current_row, bmf_id, off_bal_pcco_2)) Then
' do skip
Debug.Print "skip off-balance sheet 2"
Else
' do copy
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
bmf_adjustment_sheet_current_row = bmf_adjustment_sheet_last_row + 1
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_FINAL_BMF_ID & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "14004"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "N"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "E"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "01"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE & CStr(bmf_adjustment_sheet_current_row)).Value = "DQ"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_COMMENT & CStr(bmf_adjustment_sheet_current_row)).Value = "Unsettled bond adjustment for RWA purpose"
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCCO_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_PCEC_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_AMOUNT_TO_ADJ_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(RAW_DATA_WORKSHEET).Range(COL_OFF_BALANCE_SHEET_NA_2 & CStr(adjustment_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value & " " & "15130"
'If "Raw Data" tab "NA" is 1020/2020/8601/8701 <EFBFBD>> "Line type" fill Creation
if ("1020" = temp_value or "2020" = temp_value or "8601" = temp_value or "8701" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Creation"
end if
if ("1690" = temp_value or "2690" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Reversing"
end if
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range("A" & bmf_adjustment_sheet_current_row & ":" & "BN" & bmf_adjustment_sheet_current_row).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & bmf_adjustment_sheet_current_row).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
End If
Else
Debug.Print adjustment_sheet_current_row & " skipping copy as bmfid is empty"
End If
Next adjustment_sheet_current_row
Done:
Exit Sub
eh:
Debug.Print "helloworld:Error: " & Err.Description
End Sub
function CheckIfBmf16902690ToBmfAdjustmentNoNeedCopy(current_row As Integer, bmf_id As String, pcco As String)
On Error GoTo eh
Dim output As Boolean
output = False
Dim temp As String
temp = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_AMOUNT_IN_ACCT & CStr(current_row)).Value
Debug.Print "CheckIfBmf16902690ToBmfAdjustmentNoNeedCopy:" & temp = "0"
If (temp = "") Then
output = True
ElseIf (temp = "0") Then
paintYellow (Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BALANCE_SHEET_AMOUNT_TO_ADJ_1 & CStr(current_row)))
output = True
ElseIf (checkIfAlreadyExistInBmfAdjustmentResult(bmf_id, pcco, current_row)) Then
paintRed (Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_BMF_ID & CStr(current_row)))
paintRed (Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_PCCO & CStr(current_row)))
output = True
End If
CheckIfBmf16902690ToBmfAdjustmentNoNeedCopy = output
Done:
Exit Function
eh:
Debug.Print "CheckIfBmf16902690ToBmfAdjustmentNoNeedCopy:Error: " & Err.Description
end function
Sub ProcessBmf16902690()
On Error GoTo eh
initBmf16902690
Dim need_to_copy As Boolean
Dim bmf_1690_2690_sheet_first_row As Integer
Dim bmf_1690_2690_sheet_last_row As Integer
Dim bmf_1690_2690_sheet_current_row As Integer
Dim bmf_adjustment_sheet_last_row As Integer
Dim bmf_adjustment_sheet_current_row As Integer
bmf_1690_2690_sheet_first_row = 12
bmf_1690_2690_sheet_last_row = getBmf16902690SheetListEnd(bmf_1690_2690_sheet_first_row)
'msgbox bmf_1690_2690_sheet_last_row
For bmf_1690_2690_sheet_current_row = bmf_1690_2690_sheet_first_row To bmf_1690_2690_sheet_last_row
bmf_adjustment_sheet_last_row = getBMFAdjustmentResultListEnd(1)
bmf_adjustment_sheet_current_row = bmf_adjustment_sheet_last_row + 1
Debug.Print "bmf_adjustment_sheet_current_row:" & bmf_adjustment_sheet_current_row
Dim temp_value as String
Dim bmf_id As String
bmf_id = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_BMF_ID & CStr(bmf_1690_2690_sheet_current_row)).Value
Dim bmf_1690_2690_pcco As String
bmf_1690_2690_pcco = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_PCCO & CStr(bmf_1690_2690_sheet_current_row)).Value
If (CheckIfBmf16902690ToBmfAdjustmentNoNeedCopy(bmf_1690_2690_sheet_current_row, bmf_id, bmf_1690_2690_pcco)) Then
' no need copy
else
'Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
temp_value = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_BMF_ID & CStr(bmf_1690_2690_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_BMF_REFERENCE_ID & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
highlightBmfAdjustmentBmfId(current_row)
temp_value = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_PCCO & CStr(bmf_1690_2690_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCCO & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
temp_value = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_PCEC & CStr(bmf_1690_2690_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PCEC & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
temp_value = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_NA & CStr(bmf_1690_2690_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LOCAL_OPERATIONAL_ACCOUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value & " 15130"
if ("1020" = temp_value or "2020" = temp_value or "8601" = temp_value or "8701" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Creation"
end if
if ("1690" = temp_value or "2690" = temp_value) then
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_LINE_TYPE & bmf_adjustment_sheet_current_row).Value = "Reversing"
end if
temp_value = Worksheets(BMF_1690_2690_WORKSHEET).Range(COL_BMF_1690_2690_AMOUNT_IN_ACCT & CStr(bmf_1690_2690_sheet_current_row)).Value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_FUNT & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).Value = temp_value
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_IN_ACCT_BALANCE & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = RED_THOUSAND_SEP_NUMBER_FORMAT
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "14004"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_OPAQUE_REPORTING_ENTITY & CStr(bmf_adjustment_sheet_current_row)).Value = "N"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_INTERNAL_EXTERNAL_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "E"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_IFRS_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_FRENCH_GAAP_NORM_FLAG & CStr(bmf_adjustment_sheet_current_row)).Value = "Y"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_LIQ & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_STT & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).Value = "1"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_PROCESS_IMPACTED_YLD & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).Value = "01"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_AMOUNT_TYPE_CODE & CStr(bmf_adjustment_sheet_current_row)).NumberFormat = "@"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_ADJUSTMENT_TYPE & CStr(bmf_adjustment_sheet_current_row)).Value = "DQ"
Worksheets(BMF_ADJUSTMENT_WORKSHEET).Range(COL_BMF_ADJUSTMENT_SHEET_COMMENT & CStr(bmf_adjustment_sheet_current_row)).Value = "Unsettled bond adjustment for RWA purpose"
'formatting
end if
next bmf_1690_2690_sheet_current_row
Done:
Exit Sub
eh:
Debug.Print "helloworld:Error: " & Err.Description
End Sub
Sub Main()
On Error GoTo eh
ProcessRawData
ProcessBmf16902690
Done:
Exit Sub
eh:
Debug.Print "helloworld:Error: " & Err.Description
End Sub
Reference in New Issue View Git Blame Copy Permalink