VBA matching, data manipulation across different worksheets

[Sandler]

I am trying to do an upload to let a general ledger system know when checks are paid (reconciled).
To do so, I must upload all checks that are not left outstanding on the bank reconciliation.
If a check is on the bank reconciliation, I need to remove it from the Bank History report that shows all checks issued.

There are multiple entities 8100, 8200, 8300, etc. The tabs that have the same exact sheet name (8100, 8200, 8300 etc.) is the bank history report formatted for the upload to work. They have Check numbers in column C and amounts in column F.

The outstanding check worksheets are entitled 8100 Rec, 8200 Rec, 8300 Rec, etc. (short for Reconciliation). In those sheets the Check number is in column A and amounts are in column B.
I am looking for VBA code that will have 2 parts.

1) Go through the worksheets, wherever it sees a tab name with 4 numbers, match it up to the 4 number + Rec tab. i.e. 8100 to 8100 Rec, 8200 to 8200 Rec.

2) Then if a check and amount from the Rec tab matches up with a check and amount from the matching tab (i.e. 8100 Rec to 8100). I want the check and matching amount to be deleted from the matching tab (i.e. 8100)

Below is an example of the original sheet with check number in C and amounts in F.

*	A	B	C	D	E	F
1	8100	CHK	18719	*	11/28/2014	15
2	8100	CHK	19449	*	1/2/2015	20
3	8100	CHK	20187	*	2/12/2015	20

<tbody>
</tbody>

 

[Sandler]

Would i have to start with a loop that matches #### to the specific #### & " " & "Rec"

Also, since i am matching multiple criteria would I use an Index Match function?

Or a simple Vlookup that would take for example tab 8100 column F subtract 8100 Rec Column B and ensure that the amount is zero before deleting the row?

Thanks

 

[Sandler]

bump

 

[Sandler]

Invalid Next Control Variable reference on Next A ??

Option Explicit


Sub ReconcilingChecks()
Dim numSheets As Integer
Dim Rec As Long
Dim a As Integer




numSheets = ActiveWorkbook.Worksheets.Count


For Rec = 1 To numSheets
 
              If Len(Worksheets(Rec).Name > 4) Then
              checkBankRec Rec, Left(Sheets(a).Name, 4)


             End If
    Next a


End Sub


Sub checkBankRec(Rec As Long, Ent As String)


Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim Rw As Long
Dim check As String
Dim amount As Long


Set wsh1 = Worksheets(Rec)
Set wsh2 = Worksheets(Ent)


For Rw = 1 To wsh1.Cells(1, 1).End(xlDown).row


        check = wsh1.Cells(Rw, 1).Value 'check in column A
        amount = wsh1.Cells(Rw, 2).Value 'check in column B


       verifyCheckAmount check, amount, wsh2


Next row


End Sub


Sub verifyCheckAmount(check As String, amount As Double, wsh As Worksheet)


Dim Rw As Long
Dim amount1 As Double


Rw = wsh.Range("C:C").Find(check).Rw


If Rw = 0 Then
    MsgBox "Check #" & check & " was not found on sheet " & wsh.Name
    Exit Sub
End If


amount1 = wsh.Cells(Rw, 6).Value ' check in column F
If amount1 <> amount Then
    MsgBox "There is a discrepancy in the Amount of Check #" & check & _
    " Of Account # " & wsh.Name
    Exit Sub
End If


               wsh.Cells(row, 3).clearcontent


                wsh.Cells(row, 6).clearcontent


       End If


End Sub