CashAPP

Sub cashAPP()

Dim barFile As Variant, paymentFile As Variant, barBook As Workbook, paymentBook As Workbook, payments As Worksheet, billing As Worksheet
Dim x As Integer, y As Integer, outputCounter As String, m As String, cashAPP As Workbook

Set cashAPP = ActiveWorkbook

'User sets billing file
barFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls;*.xlsx", Title:="SELECT BILLING FILE")
Set barBook = Workbooks.Open(barFile)
Set billing = barBook.Worksheets(1)

'User sets payment file
paymentFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", Title:="SELECT PAYMENT RECORD FILE")
Set paymentBook = Workbooks.Open(paymentFile)
Set payments = paymentBook.Worksheets(1)

'Initializations
outputCounter = 2
m = "A" & outputCounter
x = 2
y = 2

'Copying the billing header to so I can keep track of which lines have had payments applied
billing.Rows(1).EntireRow.Copy cashAPP.Worksheets(1).Range("A1")

'Test message boxes to make sure the code is executing this far
MsgBox payments.Cells(2, 13)
MsgBox billing.Cells(2, 17)

'While the billing file still has data...
Debug.Print "first value = " & billing.Cells(x, 5).Value
Do While billing.Cells(x, 5) <> ""
    Debug.Print "value = " & billing.Cells(x, 5).Value & " address = " & billing.Cells(x, 5).Address
    
    '...and that particular line has an invoice #
    If billing.Cells(x, 17) <> "" Then
        Debug.Print "test = " & billing.Cells(x, 17)
        'Then looping through the payment file...
        Do While payments.Cells(y, 1) <> ""
            Debug.Print "Payments Begin --------->"
            '...to see if the invoices match
            If payments.Cells(y, 13) = billing.Cells(x, 17).Value Then
                Debug.Print "payment.cells y,13 were equal"
                'moving the amount from the "unpaid" column to the "paid" column
                'and setting the unpaid amount to 0
                billing.Cells(x, 21) = billing.Cells(x, 23)
                billing.Cells(x, 23) = 0
    
                'copying the row that had a payment applied
                billing.Rows(x).EntireRow.Copy cashAPP.Worksheets(1).Range(m)
                outputCounter = outputCounter + 1
            Else
                Debug.Print "payment.cells y,13 were not equal"
            End If
    
            y = y + 1
        Loop
    Else
        Debug.Print "cells x, 17 were empty"
    End If
x = x + 1
Loop

End Sub