Option Explicit
Dim pres_right As Integer

Dim first_row As Integer
Dim current_row As Integer
Dim last_check_row As Integer

Dim amount_in_func As String
Dim starting_cell As String

Dim grand_total_curr as currency
Dim grand_total_str as string

Dim amount_in_func_curr as currency
Dim amount_in_func_str as string

Dim pcco_str as string

Dim PCCO_COL as string
Dim ACCOUNT_COL as string

Dim AMOUNT_IN_FUNC_COL as string
Dim GRAND_TOTAL_COL As String
Dim DEBIT_COL as string
Dim CREDIT_COL as string
Dim REMARKS_COL as string

Dim ADJUSTMENT_WORKSHEET as string
Dim ADJUSTMENT_TEST_WORKSHEET as string

Dim AC_1020 as string
Dim AC_8600 as string
Dim AC_8601 as string
Dim AC_8700 as string
Dim AC_8701 as string
Dim AC_1311 as string
Dim AC_1375 as string
Dim AC_2020 as string

Dim current_row_value_valid as boolean
Dim check_next_row_ready as boolean

function writeRemarks(current_row as integer, remarks as string)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(REMARKS_COL & CStr(current_row)).Value = remarks
end function

function writeCredit(current_row as integer, credit as variant)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(CREDIT_COL & CStr(current_row)).Value = abs(credit)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(CREDIT_COL & CStr(current_row)).NumberFormat = "#,##0.00"

end function

function writeDebit(current_row as integer, debit as variant)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(DEBIT_COL & CStr(current_row)).Value = abs(debit)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(DEBIT_COL & CStr(current_row)).NumberFormat = "#,##0.00"
end function

function writeAccount(current_row as integer, account as string)
  Worksheets(ADJUSTMENT_WORKSHEET).Range(ACCOUNT_COL & CStr(current_row)).Value = account
end function

function getPCCO(current_row as integer)
  Dim output as string
  output = Worksheets(ADJUSTMENT_WORKSHEET).Range(PCCO_COL & CStr(current_row)).Value
  getPCCO = output
end function


function getGrandTotal(current_row as integer)
  Dim output as string
  output = Worksheets(ADJUSTMENT_WORKSHEET).Range(GRAND_TOTAL_COL & CStr(current_row)).text
  getGrandTotal = output
end function

function getAmountInFunc(current_row as integer)
  Dim output as string
  output = Worksheets(ADJUSTMENT_WORKSHEET).Range(AMOUNT_IN_FUNC_COL & CStr(current_row)).Value
  debug.print output
  getAmountInFunc = output
end function

function checkOutputCellReady(current_row as integer)
  Dim output as boolean
  output = True

  Dim account_cell as variant
  account_cell = Worksheets(ADJUSTMENT_WORKSHEET).Range(ACCOUNT_COL & CStr(current_row))

  if (IsEmpty(account_cell) = False) then
    output = False
  end if

  checkOutputCellReady = output
end function


function checkCurrentRowValueValid(current_row as integer)
  Dim output as boolean
  output = True

  if (checkIfGrandTotalRefNotFound(current_row) = True) then
    debug.print "grand total ref not found"

    output = False
  end if

  Dim grand_total_str as string
  dim amount_in_func_str as string
  dim pcco_str as string

    grand_total_str = getGrandTotal(current_row)
    amount_in_func_str = getAmountInFunc(current_row)
    pcco_str = getPCCO(current_row)

  ' Debug.print InStr(pcco_str, "Error") >= 0
  ' Debug.print InStr(amount_in_func_str, "N/A") >= 0
  ' Debug.print InStr(grand_total_str, "N/A") >= 0

  if (trim(pcco_str) = "" or InStr(pcco_str, "N/A") > 0 or InStr(pcco_str, "Error") > 0) then
    output = False
  end if

  if (trim(amount_in_func_str) = "" or InStr(amount_in_func_str, "N/A") > 0 or InStr(amount_in_func_str, "Error") > 0) then
    output = False
  end if

  if (trim(grand_total_str) = "" or InStr(grand_total_str, "N/A") > 0 or InStr(grand_total_str, "Error") > 0) then
    output = False
  end if

  checkCurrentRowValueValid = output
end function

function checkFullFillCasePCCOCode_P1375000Situation_1(grand_total as currency, amount_in_func as currency)
  ' ### Special case (if PCCO Code = P1375000):
  ' ### Situation 1:
  ' if Grand total + amount in func > 0 (missing example)

  checkFullFillCasePCCOCode_P1375000Situation_1 = (grand_total + amount_in_func > 0)
end function

function checkFullFillCasePCCOCode_P1375000Situation_2(grand_total as currency, amount_in_func as currency)
  ' ### Special case (if PCCO Code = P1375000):
  ' ### Situation 2:
  ' if Grand total + amount in func < 0 (row 21)

  checkFullFillCasePCCOCode_P1375000Situation_2 = (grand_total + amount_in_func < 0)
end function

function checkFullFillCasePCCOCode_P1375000Situation_3(grand_total as currency, amount_in_func as currency)
  ' ### Special case (if PCCO Code = P1375000):
  ' ### Situation 3:
  ' if Grand total + amount in func = 0
  '   - do nothing

  checkFullFillCasePCCOCode_P1375000Situation_3 = (grand_total + amount_in_func = 0)
end function

function checkFullFillCaseCommonSituation_1(grand_total as currency, amount_in_func as currency)
  ' ### Situation 1:
  ' if Grand total + amount in func > 0 (row 8 ?)
  checkFullFillCaseCommonSituation_1 = (grand_total + amount_in_func > 0)
end function

function checkFullFillCaseCommonSituation_2(grand_total as currency, amount_in_func as currency)
  ' ### Situation 2:
  ' if Grand total + amount in func < 0 (row 8 ?)'
  checkFullFillCaseCommonSituation_2 = (grand_total + amount_in_func < 0)
end function

function checkFullFillCaseCommonSituation_3(grand_total as currency, amount_in_func as currency)
  ' ### Situation 3:
  ' if Grand total + amount in func = 0
  checkFullFillCaseCommonSituation_3 = (grand_total + amount_in_func = 0)
end function

function checkIfReferenceNotFound(cell as variant)
  Dim test_text as variant
  test_text = cell.text
  checkIfReferenceNotFound = (InStr(test_text, "#REF!") > 0)
end function

function checkIfGrandTotalRefNotFound(current_row as integer)
  checkIfGrandTotalRefNotFound = checkIfReferenceNotFound (Worksheets(ADJUSTMENT_WORKSHEET).Range(GRAND_TOTAL_COL & CStr(current_row)))
end function

function applyCommonSituation_1(current_row as integer)
  Dim net_var as variant
  Dim grand_total_var as variant
  Dim amount_in_func_var as variant

  grand_total_var = CDec(getGrandTotal(current_row))
  amount_in_func_var = CDec(getAmountInFunc(current_row))
  net_var = grand_total_var + amount_in_func_var

' current row
' - Account column: A1311, Debit column: Grand Total+amount in func
  writeAccount current_row, AC_1311
  writeDebit current_row, net_var

' - add row:
'     - account:1020,
'     - debit column: Grand Total+amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_1020
  writeDebit current_row, net_var

' - add row:
'     - account:NA8600,
'     - debit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8600
  writeDebit current_row, net_var

' - add row:
'     - account NA8601,
'     - credit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8601
  writeCredit current_row, net_var

  applyCommonSituation_1 = True
end function

function applyCommonSituation_2(current_row as integer)
  Dim net_var as variant
  Dim grand_total_var as variant
  Dim amount_in_func_var as variant

  grand_total_var = CDec(getGrandTotal(current_row))
  amount_in_func_var = CDec(getAmountInFunc(current_row))
  net_var = grand_total_var + amount_in_func_var

' if Grand total + amount in func < 0 (row 8 ?)
'   - Account column: A1311,
'   - Credit column: Grand Total+amount in func
  writeAccount current_row, AC_1311
  writeCredit current_row, net_var

'   - add row:
'     - account:1020,
'     - credit column: Grand Total+amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_1020
  writeCredit current_row, net_var

'   - add row:
'     - account:NA8700,
'     - credit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8700
  writeCredit current_row, net_var

'   - add row:
'     - account NA8701,
'     - debit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8701
  writeDebit current_row, net_var

  applyCommonSituation_2 = True
end function

function applyCommonSituation_3(current_row as integer)
' if Grand total + amount in func = 0
' - do nothing
  applyCommonSituation_3 = True
end function

function applyPcco1375Situation_1(current_row as integer)
  Dim net_var as variant
  Dim grand_total_var as variant
  Dim amount_in_func_var as variant

  grand_total_var = CDec(getGrandTotal(current_row))
  amount_in_func_var = CDec(getAmountInFunc(current_row))
  net_var = grand_total_var + amount_in_func_var

  '   - Account
  '     - column: P1375,
  '     - Debit column: Grand Total+amount in func
  writeAccount current_row, AC_1375
  writeDebit current_row, net_var

  '   - add row:
  '     - account:2020,
  '     - debit column: Grand Total+amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_2020
  writeDebit current_row, net_var

  '   - add row:
  '     - account:NA8600,
  '     - debit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8600
  writeDebit current_row, net_var

  '   - add row:
  '     - account NA8601,
  '     - credit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8601
  writeCredit current_row, net_var

  applyPcco1375Situation_1 = True
end function

function applyPcco1375Situation_2(current_row as integer)
  Dim net_var as variant
  Dim grand_total_var as variant
  Dim amount_in_func_var as variant

  grand_total_var = CDec(getGrandTotal(current_row))
  amount_in_func_var = CDec(getAmountInFunc(current_row))
  net_var = grand_total_var + amount_in_func_var

'   - Account
'     - column: P1375,
'     - Credit column: Grand Total+amount in func
  writeAccount current_row, AC_1375
  writeCredit current_row, net_var

'   - add row:
'     - account:2020,
'     - credit column: Grand Total+amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_2020
  writeCredit current_row, net_var

'   - add row:
'     - account:8700,
'     - credit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8700
  writeCredit current_row, net_var

'   - add row:
'     - account 8701,
'     - debit: grand total + amount in func
  insertNewRowBelow current_row
  current_row = current_row+ 1
  writeAccount current_row, AC_8701
  writeDebit current_row, net_var

  applyPcco1375Situation_2= True
end function

function applyPcco1375Situation_3(current_row as integer)
' - do nothing
  applyPcco1375Situation_3 = True
end function


Sub test()
  ' Debug.Assert True = isCurrentRowNeedToProcess("7")
  ' Debug.Assert False = isCurrentRowNeedToProcess("180")
  ' debug.assert False = isAllDone ("7")
  ' debug.assert True = isAllDone ("180")

  ' debug.assert False = checkFullFillCaseCommonSituation_1(-10,1)
  ' debug.assert False = checkFullFillCaseCommonSituation_1(1,-10)
  ' debug.assert False = checkFullFillCaseCommonSituation_1(-10,-10)
  ' debug.assert False = checkFullFillCaseCommonSituation_1(0,0)
  ' debug.assert True = checkFullFillCaseCommonSituation_1(0,1)
  ' debug.assert True = checkFullFillCaseCommonSituation_1(1,0)
  ' debug.assert True = checkFullFillCaseCommonSituation_1(1,1)

  ' debug.assert True = checkFullFillCaseCommonSituation_2(-10,1)
  ' debug.assert True = checkFullFillCaseCommonSituation_2(1,-10)
  ' debug.assert True = checkFullFillCaseCommonSituation_2(-10,-10)
  ' debug.assert False = checkFullFillCaseCommonSituation_2(0,0)
  ' debug.assert False = checkFullFillCaseCommonSituation_2(0,1)
  ' debug.assert False = checkFullFillCaseCommonSituation_2(1,0)
  ' debug.assert False = checkFullFillCaseCommonSituation_2(1,1)

  ' debug.assert True =  checkFullFillCaseCommonSituation_3(0,0)
  ' debug.assert False =  checkFullFillCaseCommonSituation_3(1,1)
  ' debug.assert False =  checkFullFillCaseCommonSituation_3(-10,-10)
  ' debug.assert False =  checkFullFillCaseCommonSituation_3(1,0)
  ' debug.assert False =  checkFullFillCaseCommonSituation_3(0,1)

  ' debug.print Worksheets("Adjustment_TEST").Range("G86")

  init

  current_row = 29
  ' amount_in_func_str = getAmountInFunc(current_row)
  ' pcco_str = getPCCO(current_row)

  ' debug.print current_row
  ' debug.print grand_total_str
  ' debug.print checkIfGrandTotalRefNotFound(current_row)
  debug.print checkCurrentRowValueValid(current_row)

  ' current_row_value_valid = False
  ' current_row_value_valid = checkCurrentRowValueValid(pcco_str, amount_in_func_str, grand_total_str)
  ' check_next_row_ready = False
  ' check_next_row_ready = checkOutputCellReady(current_row)

End Sub

Function getLastCheckRow(first_row As Integer)
  getLastCheckRow = first_row + 10
End Function

function insertNewRowBelow(current_row as integer)
  Range("A"&current_row+1).EntireRow.Insert
end function

function isAllDone(input_row as integer)
  Dim cell_value As Currency
  Dim output as Boolean
  Dim i as integer
  output = true

  last_check_row = getLastCheckRow(input_row)
  For i = input_row To last_check_row
    if (isCurrentRowNeedToProcess(i) = True) then
      output = false
      Exit For
    end if
  Next i

  isAllDone = output
end function

Function isCurrentRowNeedToProcess(input_row as integer)
  Dim output As Boolean
  Dim cell_value As Currency
  Dim input_cell as string
  input_cell = GRAND_TOTAL_COL & input_row

  isCurrentRowNeedToProcess = not(IsEmpty(Worksheets(ADJUSTMENT_WORKSHEET).Range(input_cell).Value))
End Function

Sub init()
 pres_right = 111
  first_row = 7
  current_row = first_row
  last_check_row = getLastCheckRow(first_row)


  GRAND_TOTAL_COL = "G"
  amount_in_func = "J"
  starting_cell = GRAND_TOTAL_COL & first_row
  PCCO_COL = "I"
  AMOUNT_IN_FUNC_COL = "J"
  DEBIT_COL = "N"
  CREDIT_COL= "O"
  ACCOUNT_COL = "M"
  REMARKS_COL = "Q"

  AC_1020 = "AC_1020"
  AC_8600 = "AC_8600"
  AC_8601 = "AC_8601"
  AC_8700 = "AC_8700"
  AC_8701 = "AC_8701"
  AC_1311 = "AC_1311"
  AC_1375 = "AC_1375"
  AC_2020 = "AC_2020"

  ADJUSTMENT_WORKSHEET = "Adjustment_TEST"
  ' ADJUSTMENT_WORKSHEET = "Adjustment"

end sub

Sub Main()
  init

  While isAllDone(current_row) = False
    debug.print "processing row: " & current_row
    debug.print checkCurrentRowValueValid(current_row)



    current_row_value_valid = False
    current_row_value_valid = checkCurrentRowValueValid(current_row)
    check_next_row_ready = False
    check_next_row_ready = checkOutputCellReady(current_row)



    if (current_row_value_valid and check_next_row_ready) then
    ' input considered valid after this line

  Dim grand_total_str as string
  dim amount_in_func_str as string
  dim pcco_str as string

    grand_total_str = getGrandTotal(current_row)
    amount_in_func_str = getAmountInFunc(current_row)
    pcco_str = getPCCO(current_row)

      debug.print "before"
      debug.print grand_total_str
      debug.print "pass"
      grand_total_curr = ccur(grand_total_str)

      amount_in_func_curr = ccur(amount_in_func_str)

      if (pcco_str = "P1375000") then
        if (checkFullFillCasePCCOCode_P1375000Situation_1(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "P1375000 common sit 1"
          applyPcco1375Situation_1(current_row)
        Elseif (checkFullFillCasePCCOCode_P1375000Situation_2(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "P1375000 common sit 2"
          applyPcco1375Situation_2(current_row)
        Elseif (checkFullFillCasePCCOCode_P1375000Situation_3(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "P1375000 common sit 3"
          applyPcco1375Situation_3(current_row)
        Else
          msgbox "missing pcco 1375 situation"
        end if

      Elseif (pcco_str = "N/A") then
        msgbox "pcco value is N/A"

      Else
        if (checkFullFillCaseCommonSituation_1(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "common sit 1"
          applyCommonSituation_1(current_row)
        Elseif (checkFullFillCaseCommonSituation_2(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "common sit 2"
          applyCommonSituation_2(current_row)
        Elseif (checkFullFillCaseCommonSituation_3(grand_total_curr, amount_in_func_curr) = True) then
          writeRemarks current_row, "common sit 3"
          applyCommonSituation_3(current_row)
        Else
          msgbox "missing situation"
        end if
      end if

    else
      debug.print "input is not valid, skipping row"

      if (checkOutputCellReady(current_row)) then
        if (checkCurrentRowValueValid(current_row)) then
        else
          writeRemarks current_row, "input is not valid"
        end if
      end if
    end if

    current_row = current_row + 1
  Wend

End Sub

Sub helloworld()
  ' debug.print isCurrentRowNeedToProcess ("7")
  ' debug.print isCurrentRowNeedToProcess ("180")

  ' debug.print isAllDone ("7")
  ' debug.print isAllDone ("180")

End Sub