Comparing individual cells in a worksheet to variable range on another

[Squallclouds]

I'm developing a balancing spreadsheet to reconcile fees projected to fees received. Each month has its own worksheet that auto-populates the projected fees based on other vba code and system produced data. The trick is... The system makes no difference for vendors only labels each accrued item by a universal 9 digit control number, (same format for all venders) and each company pays on a different schedule. So Company 1 pays Aprils fees in June and Company 2 pays June's fees in July so on...

Each month's spreadsheet is broken into cell A-f [CTRL #][FeeType] [Accrual] [Accrual Total] [Total Received] [Difference]

Each invoice is stripped and broken down into 3 columns A-C [CTRL #] [Item Amount] [Amount Paid]

The invoice is loaded into a worksheet called Control.
Additionally when a number is matched from the Control worksheet I’d like to turn the number green to indicate I have accounted for the amount on my monthly worksheet.. That way I know if I was paid for something I didn’t Accrue

I need to write a VBA Macro to automatically fill the [Total Received] cell in the Current Month worksheet by matching the [Ctrl #] to the corresponding Control worksheet [CTRL #] and using the control worksheet [amount paid]

I've attached an example worksheet showing the layout for those who can view.

I am newer to VBA but am no idiot (I like to think) I can usually get things to work but I'm getting hung up on the comparison of the two worksheets and the fact thatt he worksheet changes names from month to month.

I appreciate any help. Thank you.

 

[Squallclouds]

Here is a sample code of what I was thinking... I think the logic here will work I'm just not sure of the VBA syntax.


Sub autoreceived()
Dim I, j, k As Long
Dim invoice As Worksheet
Set invoice = CTRL
Dim try As Boolean

try = False
I = 2
k = 3
j = 1

Do Until try = True

'Only at the end of the current month worksheet will there ever be 2 consecutive null values

If Cells(I, 1) <> "" And Cells(k, 1) <> "" Then

'Match respective Cells from current worksheet to the control worksheet

If Cells(I, 1) = invoice.Cells(j, 1) Then
Cells(I, 5) = invoice.cell(j, 3)
I = I + 1
k = k + 1
j = 1

Else
j = j + 1
End If

Else
try = True

End If

Loop


End Sub

 

[PHV]

You meant something like this ?

Sub autoreceived()
Dim I As Long, j As Long
For I = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(I, 4) <> "" Then
        For j = 2 To Sheets("CTRL").UsedRange.Rows.Count
            If Cells(I, 1) = Sheets("CTRL").Cells(j, 1) Then
                Cells(I, 5) = Sheets("CTRL").Cells(j, 3)
                Exit For
            End If
        Next
    End If
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886