Endicia Testing (Original Schema)

Sub Transform()
    'Optimizing Run Speeds
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


    Sheets("Original Report").Activate

    Dim config As Worksheet
    Dim cell As Range
    Dim RowCount As Long
    Dim SheetName As Variant
    Dim SheetName2 As String
    Dim Rep As String
    Dim Margin As Double
    Dim matchFound2 As Boolean
    Dim matchFound As Boolean

    RowCount = Worksheets("Original Report").Range("A" & Rows.Count).End(xlUp).Row
    ColumnCount = Worksheets("Original Report").Cells(1, Columns.Count).End(xlToLeft).Column
    RowCountCust = Worksheets("Customer").Range("A" & Rows.Count).End(xlUp).Row

    Dim Data() As Variant

    Dim PriorityMail() As Variant
    Dim PriorityMail2() As Variant
    Dim PriorityMailExpress() As Variant
    Dim PriorityMailExpress2() As Variant
    Dim FirstClassMail() As Variant
    Dim FirstClassMail2() As Variant
    Dim PriorityMailInt() As Variant
    Dim PriorityMailInt2() As Variant
    Dim PriorityMailExpressInt() As Variant
    Dim PriorityMailExpressInt2() As Variant
    Dim FirstClassPackage() As Variant
    Dim FirstClassPackage2() As Variant

    Dim Customers() As Variant

    Dim cCMG() As Variant
    Dim cBusinessFacilitating() As Variant
    Dim cUnitedEx() As Variant
    Dim cHapHolmstead() As Variant
    Dim cHouse() As Variant
    Dim cJohnnyJohnson() As Variant
    Dim cOrphan() As Variant
    ReDim cCMG(RowCount, 27)
    ReDim cBusinessFacilitating(RowCount, 27)
    ReDim cUnitedEx(RowCount, 27)
    ReDim cHapHolmstead(RowCount, 27)
    ReDim cHouse(RowCount, 27)
    ReDim cJohnnyJohnson(RowCount, 27)
    ReDim cOrphan(RowCount, 27)


    Dim iCMG As Integer
    Dim iBusinessFacilitating As Integer
    Dim iUnitedEx As Integer
    Dim iHapHolmstead As Integer
    Dim iHouse As Integer
    Dim iJohnnyJohnson As Integer
    Dim iOrphan As Integer

    Dim bBusinessFacilitating As Boolean

    iCMG = 0
    iBusinessFacilitating = 0
    iUnitedEx = 0
    iHapHolmstead = 0
    iHouse = 0
    iJohnnyJohnson = 0
    iOrphan = 0

    tCMG = 0
    tBusinessFacilitating = 0
    tUnitedEx = 0
    tHapHolmstead = 0
    tHouse = 0
    tJohnnyJohnson = 0
    tOrphan = 0

    rCMG = 0
    rBusinessFacilitating = 0
    rUnitedEx = 0
    rHapHolmstead = 0
    rHouse = 0
    rJohnnyJohnson = 0
    rOrphan = 0

    bBusinessFacilitating = false

    Data = Worksheets("Original Report").Range("A2:AA" & RowCount).Value
    Customers = Worksheets("Customer").Range("A2:AA" & RowCountCust).Value

    PriorityMail = Worksheets("Priority Mail").Range("B2:J92").Value
    PriorityMail2 = Worksheets("Priority Mail").Range("N2:V92").Value

    PriorityMailInt = Worksheets("Priority Mail International").Range("B2:Y77").Value
    PriorityMailInt2 = Worksheets("Priority Mail International").Range("AC2:AZ77").Value

    PriorityMailExpress = Worksheets("Priority Mail Express Intl").Range("B2:J77").Value
    PriorityMailExpress2 = Worksheets("Priority Mail Express Intl").Range("N2:V77").Value

    FirstClassMail = Worksheets("First-Class Mail").Range("B2:J17").Value
    FirstClassMail2 = Worksheets("First-Class Mail").Range("N2:V17").Value

    PriorityMailExpressInt = Worksheets("Priority Mail Express Intl").Range("B2:R75").Value
    PriorityMailExpressInt2 = Worksheets("Priority Mail Express Intl").Range("V2:AL75").Value

    FirstClassPackage = Worksheets("First-Class Package Intl").Range("B2:J23").Value
    FirstClassPackage2 = Worksheets("First-Class Package Intl").Range("N2:V23").Value



    For x = 1 To RowCount - 1
        DoEvents

        matchFound = False

        For i = 1 To UBound(Customers, 1)
            For j = 1 To UBound(Customers, 2)
                If Customers(i, j) Like Data(x, 2) Then
                    Rep = Customers(i, j + 1)
                    Margin = Customers(i, j + 2)
                    Select Case Rep
                        Case "CMG"
                            For f = 1 To ColumnCount
                                cCMG(iCMG, f - 1) = Data(x, f)
                            Next f
                            ' calculate the margin column by subtracting wholesale from retail and then subtracting credit card fees and then the Endicia min fee.
                            cCMG(iCMG, 15) = cCMG(iCMG, 13) - cCMG(iCMG, 14) - cCMG(iCMG, 12) - cCMG(iCMG,26)
                            ' calculates the total earned column by multiplying the margin by the split rate
                            cCMG(iCMG, 27) = (cCMG(iCMG, 15) * Margin)
                            ' This adds to the rCMG if its a return, and to the tCMG if not
                            If cCMG(iCMG, 10) = "POSTAGE REFUND" Then
                                rCMG = rCMG + cCMG(iCMG, 27)
                            Else
                                tCMG = tCMG + cCMG(iCMG, 27)
                            End if
                            iCMG = iCMG + 1
                        Case "Business Facilitating"
                            ' loads the row into the customers array
                            For f = 1 To ColumnCount
                                cBusinessFacilitating(iBusinessFacilitating, f - 1) = Data(x, f)
                            Next f
                            ' gets the rows mailtype
                            MailType = cBusinessFacilitating(iBusinessFacilitating, 24)

                            Select Case MailType
                            Case "Priority Mail"
                                SheetArray = PriorityMail
                                SheetArray2 = PriorityMail2
                            Case "Priority Mail International Parcels"
                                SheetArray = PriorityMailInt
                                SheetArray2 = PriorityMailInt2
                            Case "Priority Mail Express"
                                SheetArray = PriorityMailExpress
                                SheetArray2 = PriorityMailExpress2
                            Case "First Class"
                                SheetArray = FirstClassMail
                                SheetArray2 = FirstClassMail2
                            Case "Priority Mail Express International"
                                SheetArray = PriorityMailExpressInt
                                SheetArray2 = PriorityMailExpressInt2
                            Case "First Class Package International Service"
                                SheetArray = FirstClassPackage
                                SheetArray2 = FirstClassPackage2
                            End Select

                            matchFound2 = False
                            For a = 1 To UBound(SheetArray, 1)
                                For b = 1 To UBound(SheetArray, 2)
                                    ' if it finds the wholesale rate in the corresponding sheetarray
                                    If SheetArray(a, b) Like cBusinessFacilitating(iBusinessFacilitating, 14) Then
                                        ' looks for the same coordinates from sheet array one, in sheet array 2
                                        cBusinessFacilitating(iBusinessFacilitating, 14) = SheetArray2(a, b)
                                        ' calculates total for the row by subtracting retail from wholesale minus credit card fee and the endicia min fee
                                        cBusinessFacilitating(iBusinessFacilitating, 15) = cBusinessFacilitating(iBusinessFacilitating, 13) - cBusinessFacilitating(iBusinessFacilitating, 14) - cBusinessFacilitating(iBusinessFacilitating, 12) - cBusinessFacilitating(iBusinessFacilitating,26)
                                        ' row total is the same as the margin column
                                        cBusinessFacilitating(iBusinessFacilitating, 27) = cBusinessFacilitating(iBusinessFacilitating, 15)
                                        matchFound2 = True
                                        Exit For
                                    End If
                                Next b
                                If matchFound2 Then Exit For
                            Next a
                            ' if it cant find the wholesale value in the sheet array, put an err in the total column
                            If Not matchFound2 Then
                                cBusinessFacilitating(iBusinessFacilitating, 27) = "err"
                                bBusinessFacilitating = true
                            End if
                            ' if there are no errors and its a refund
                            If matchFound2 And cBusinessFacilitating(iBusinessFacilitating, 10) = "POSTAGE REFUND" Then
                                rBusinessFacilitating = rBusinessFacilitating + cBusinessFacilitating(iBusinessFacilitating, 27)
                            ' if there are no errors and its a print
                            Elseif matchFound2 And cBusinessFacilitating(iBusinessFacilitating, 10) = "POSTAGE PRINT" Then
                                tBusinessFacilitating = tBusinessFacilitating + cBusinessFacilitating(iBusinessFacilitating, 27)
                            End if

                            iBusinessFacilitating = iBusinessFacilitating + 1

                        Case "UnitedEx"
                            For f = 1 To ColumnCount
                                cUnitedEx(iUnitedEx, f - 1) = Data(x, f)
                            Next f

                            MailType = cUnitedEx(iUnitedEx, 24)

                            Select Case MailType
                            Case "Priority Mail"
                                SheetArray = PriorityMail
                                SheetArray2 = PriorityMail2
                            Case "Priority Mail International Parcels"
                                SheetArray = PriorityMailInt
                                SheetArray2 = PriorityMailInt2
                            Case "Priority Mail Express"
                                SheetArray = PriorityMailExpress
                                SheetArray2 = PriorityMailExpress2
                            Case "First Class"
                                SheetArray = FirstClassMail
                                SheetArray2 = FirstClassMail2
                            Case "Priority Mail Express International"
                                SheetArray = PriorityMailExpressInt
                                SheetArray2 = PriorityMailExpressInt2
                            Case "First Class Package International Service"
                                SheetArray = FirstClassPackage
                                SheetArray2 = FirstClassPackage2
                            End Select


                            matchFound2 = False
                            For a = 1 To UBound(SheetArray, 1)
                                For b = 1 To UBound(SheetArray, 2)
                                    If SheetArray(a, b) Like cUnitedEx(iUnitedEx, 14) Then
                                        cUnitedEx(iUnitedEx, 14) = SheetArray2(a, b)
                                        Sheets("Original Report").Cells(x + 1, 12).Value = cUnitedEx(iUnitedEx, 14)
                                        matchFound2 = True
                                        Exit For
                                    End If
                                Next b
                                If matchFound2 Then Exit For
                            Next a
                            If Not matchFound2 Then Sheets("Original Report").Cells(x + 2, 11).Value = "err"

                            If cUnitedEx(iUnitedEx, 10) = "POSTAGE REFUND" Then
                                rUnitedEx = rUnitedEx + cUnitedEx(iUnitedEx, 14)
                            Else
                                tUnitedEx = tUnitedEx + cUnitedEx(iUnitedEx, 14)
                            End if

                            iUnitedEx = iUnitedEx + 1
                        Case "Hap Holmstead"
                            For f = 1 To ColumnCount
                                cHapHolmstead(iHapHolmstead, f - 1) = Data(x, f)
                            Next f
                            cHapHolmstead(iHapHolmstead, 27) = ((cHapHolmstead(iHapHolmstead, 14) - cHapHolmstead(iHapHolmstead, 12)) * Margin)

                            If cHapHolmstead(iHapHolmstead, 10) = "POSTAGE REFUND" Then
                                rHapHolmstead = rHapHolmstead + cHapHolmstead(iHapHolmstead, 14)
                            Else
                                tHapHolmstead = tHapHolmstead + cHapHolmstead(iHapHolmstead, 14)
                            End if

                            iHapHolmstead = iHapHolmstead + 1
                        Case "House"
                            For f = 1 To ColumnCount
                                cHouse(iHouse, f - 1) = Data(x, f)
                            Next f
                            cHouse(iHouse, 27) = ((Data(x, 14) - Data(x, 12)) * Margin)

                            If cHouse(iHouse, 10) = "POSTAGE REFUND" Then
                                rHouse = rHouse + cHouse(iHouse, 14)
                            Else
                                tHouse = tHouse + cHouse(iHouse, 14)
                            End if

                            iHouse = iHouse + 1
                        Case "Johnny Johnson"
                            For f = 1 To ColumnCount
                                cJohnnyJohnson(iJohnnyJohnson, f - 1) = Data(x, f)
                            Next f

                            MailType = cJohnnyJohnson(iJohnnyJohnson, 24)

                            Select Case MailType
                            Case "Priority Mail"
                                SheetArray = PriorityMail
                                SheetArray2 = PriorityMail2
                            Case "Priority Mail International Parcels"
                                SheetArray = PriorityMailInt
                                SheetArray2 = PriorityMailInt2
                            Case "Priority Mail Express"
                                SheetArray = PriorityMailExpress
                                SheetArray2 = PriorityMailExpress2
                            Case "First Class"
                                SheetArray = FirstClassMail
                                SheetArray2 = FirstClassMail2
                            Case "Priority Mail Express International"
                                SheetArray = PriorityMailExpressInt
                                SheetArray2 = PriorityMailExpressInt2
                            Case "First Class Package International Service"
                                SheetArray = FirstClassPackage
                                SheetArray2 = FirstClassPackage2
                            End Select

                            matchFound2 = False
                            For a = 1 To UBound(SheetArray, 1)
                                For b = 1 To UBound(SheetArray, 2)
                                    If SheetArray(a, b) Like cJohnnyJohnson(iJohnnyJohnson, 14) Then
                                        cJohnnyJohnson(iJohnnyJohnson, 14) = SheetArray2(a, b)
                                        Sheets("Original Report").Cells(x + 1, 12).Value = cJohnnyJohnson(iJohnnyJohnson, 14)
                                        matchFound2 = True
                                        Exit For
                                    End If
                                Next b
                                If matchFound2 Then Exit For
                            Next a
                            If Not matchFound2 Then Sheets("Original Report").Cells(x + 2, 11).Value = "err"

                            If cJohnnyJohnson(iJohnnyJohnson, 10) = "POSTAGE REFUND" Then
                                rJohnnyJohnson = rJohnnyJohnson + cJohnnyJohnson(iJohnnyJohnson, 14)
                            Else
                                tJohnnyJohnson = tJohnnyJohnson + cJohnnyJohnson(iJohnnyJohnson, 14)
                            End if

                            iJohnnyJohnson = iJohnnyJohnson + 1
                        End Select

                    matchFound = True
                    Exit For
                End If
            Next j
            If matchFound Then Exit For
        Next i
        If Not matchFound Then
            For f = 1 To ColumnCount
                cOrphan(iOrphan, f - 1) = Data(x, f)
            Next f
            iOrphan = iOrphan + 1
        End If
    Next x

    Sheets("CMG").Range("A2:AB" & UBound(cCMG, 1)).Value = cCMG
    Sheets("CMG").Range("AA" & iCMG + 2).Value = "Subtotal"
    Sheets("CMG").Range("AB" & iCMG + 2).Value = tCMG
    Sheets("CMG").Range("AA" & iCMG + 3).Value = "Returns"
    Sheets("CMG").Range("AB" & iCMG + 3).Value = rCMG
    Sheets("CMG").Range("AA" & iCMG + 4).Value = "Total"
    Sheets("CMG").Range("AB" & iCMG + 4).Value = tCMG - rCMG

    Sheets("Business Facilitating").Range("A2:AB" & UBound(cBusinessFacilitating, 1)).Value = cBusinessFacilitating
    Sheets("Business Facilitating").Range("AA" & iBusinessFacilitating + 2).Value = "Subtotal"
    Sheets("Business Facilitating").Range("AB" & iBusinessFacilitating + 2).Value = tBusinessFacilitating
    Sheets("Business Facilitating").Range("AA" & iBusinessFacilitating + 3).Value = "Returns"
    Sheets("Business Facilitating").Range("AB" & iBusinessFacilitating + 3).Value = rBusinessFacilitating
    Sheets("Business Facilitating").Range("AA" & iBusinessFacilitating + 4).Value = "Total"
    Sheets("Business Facilitating").Range("AB" & iBusinessFacilitating + 4).Value = tBusinessFacilitating - rBusinessFacilitating
    Sheets("Business Facilitating").Range("AA" & iBusinessFacilitating + 6).Value = "Errors"
    Sheets("Business Facilitating").Range("AB" & iBusinessFacilitating + 6).Value = bBusinessFacilitating

    Sheets("UnitedEx").Range("A2:AB" & UBound(cUnitedEx, 1)).Value = cUnitedEx
    Sheets("UnitedEx").Range("AA" & iUnitedEx + 2).Value = "Subtotal"
    Sheets("UnitedEx").Range("AB" & iUnitedEx + 2).Value = tUnitedEx
    Sheets("UnitedEx").Range("AA" & iUnitedEx + 3).Value = "Returns"
    Sheets("UnitedEx").Range("AB" & iUnitedEx + 3).Value = rUnitedEx
    Sheets("UnitedEx").Range("AA" & iUnitedEx + 4).Value = "Total"
    Sheets("UnitedEx").Range("AB" & iUnitedEx + 4).Value = tUnitedEx - rUnitedEx

    Sheets("Hap Holmstead").Range("A2:AB" & UBound(cHapHolmstead, 1)).Value = cHapHolmstead
    Sheets("Hap Holmstead").Range("AA" & iHapHolmstead + 2).Value = "Subtotal"
    Sheets("Hap Holmstead").Range("AB" & iHapHolmstead + 2).Value = tHapHolmstead
    Sheets("Hap Holmstead").Range("AA" & iHapHolmstead + 3).Value = "Returns"
    Sheets("Hap Holmstead").Range("AB" & iHapHolmstead + 3).Value = rHapHolmstead
    Sheets("Hap Holmstead").Range("AA" & iHapHolmstead + 4).Value = "Total"
    Sheets("Hap Holmstead").Range("AB" & iHapHolmstead + 4).Value = tHapHolmstead - rHapHolmstead

    Sheets("House").Range("A2:AB" & UBound(cHouse, 1)).Value = cHouse
    Sheets("House").Range("AA" & iHouse + 2).Value = "Subtotal"
    Sheets("House").Range("AB" & iHouse + 2).Value = tHouse
    Sheets("House").Range("AA" & iHouse + 3).Value = "Returns"
    Sheets("House").Range("AB" & iHouse + 3).Value = rHouse
    Sheets("House").Range("AA" & iHouse + 4).Value = "Total"
    Sheets("House").Range("AB" & iHouse + 4).Value = tHouse - rHouse

    Sheets("Johnny Johnson").Range("A2:AB" & UBound(cJohnnyJohnson, 1)).Value = cJohnnyJohnson
    Sheets("Johnny Johnson").Range("AA" & iJohnnyJohnson + 2).Value = "Subtotal"
    Sheets("Johnny Johnson").Range("AB" & iJohnnyJohnson + 2).Value = tJohnnyJohnson
    Sheets("Johnny Johnson").Range("AA" & iJohnnyJohnson + 3).Value = "Returns"
    Sheets("Johnny Johnson").Range("AB" & iJohnnyJohnson + 3).Value = rJohnnyJohnson
    Sheets("Johnny Johnson").Range("AA" & iJohnnyJohnson + 4).Value = "Total"
    Sheets("Johnny Johnson").Range("AB" & iJohnnyJohnson + 4).Value = tJohnnyJohnson - rJohnnyJohnson

    Sheets("Orphans").Range("A2:AB" & UBound(cOrphan, 1)).Value = cOrphan

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub


'TODO:
'What exactly am I subtracting on refunds? = you subtract the margin (make sure you subtract whatever they paid)
'What to do when wholesale is higher than retail. See row 98. Maybe it wont ever be a problem with prod data?
'Add a button that clears the original report.
'What do I do for "postage purchase" orders under the transaction type column
'What do I do for "Parcel" in package type column
'Why does business facilitating sum of all rows and total have a difference of .02?