In Excel If Value exists in Workbook1 & Not in Workbook2 Create new Row in Workbook2 with the Va 1

[JasonEnsor]

Hi Guy's

After my last Post I am looking at approaching my issue in a slightly different manor to see if that helps. I have 2 Workbooks SwimmerDetails and Payments. If column A of SwimmerDetails contains "Joe Bloggs" as an example and in Workbook 2 contains "Joe Bloggs" in column A and the current year in Column B then I generate an invoice, this works perfectly. However I am stumped as to how I generate a blank invoice for a Swimmer who appears in the SwimmerDetails workbook but has no payments in the Payments workbook.

How do I copy the names from SwimmerDetails that do not exist in Payments in to the Payments workbook to generate the invoices?

The issue is that every swimmer will need to be invoiced, there is only data in payments for the swimmers that have had a receipt generated for them due to part payment earlier in the month.

In theory I need to turn this:
Search for Swimmer in SwimmerDetails
[tab] Populate SwimmerClass with Name and other details
[tab][tab]Search Payments
[tab][tab][tab]if SwimmerName in SwimmerDetails matches SwimmerName in Payments AND SearchYear matches CurrentYear in Payments Then
[tab][tab][tab][tab]Populate Invoice
[tab][tab][tab]Else
[tab][tab][tab][tab]Add new Row in Payments With Name and Year and Generate Blank Invoice
Next Swimmer

in to usable code. As I stated earlier it is the else section I am struggling with the most at the moment.

Many Thanks

J.

 

[PHV]

I'd use this logic:
For Each Swimmer in SwimmerDetails
[tab]Populate SwimmerClass with Name and other details
[tab]set a boolean value to false
[tab]For Each Payment
[tab][tab]if SwimmerName in SwimmerDetails matches SwimmerName in Payments AND SearchYear matches CurrentYear in Payments Then
[tab][tab][tab]Populate Invoice
[tab][tab][tab]set boolean value to true
[tab][tab][tab]Exit For
[tab][tab]end if
[tab]next payment
[tab]if boolean = false then
[tab][tab]Add new Row in Payments With Name and Year and Generate Blank Invoice
[tab]end if
Next Swimmer


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

 

[JasonEnsor]

Thanks PHV for the suggestion, it's still not working though. It would seem that my logic is flawed. In my SwimmerDetails I currently have 2 swimmers
[tab]A[tab][tab]B
Jason[tab] 2012
Cassie[tab]2012

I loop through the SwimmerDetails in a With Loop using the following code

    LastRow = SwimmerDetails.Cells(Rows.Count, 1).End(xlUp).Row
        
    For Each SwimmersData In SwimmerDetails.Range("A3:A" & LastRow)
        SwimmersData.Rows.EntireRow.Select
        SwimmerT.SwimmerRow = ActiveCell.Row
        SwimmerT.SwimmerName = .Cells.Range(NameColumn & SwimmerT.SwimmerRow).Value
        SwimmerT.ClassTime = .Cells.Range(SwimTimeColumn & SwimmerT.SwimmerRow).Value
        SwimmerT.SwimDay = .Cells.Range(SwimDayColumn & SwimmerT.SwimmerRow).Value
        SwimmerT.isLittleSwimmer = .Cells.Range(LittleSwimmerColumn & SwimmerT.SwimmerRow).Value
        SwimmerT.ContactPrefs = SwimmerDetails.Range(DefaultContactColumn & SwimmerT.SwimmerRow)

I then loop through my payment details using

For Each invoiceRange In Payments.Range("A2:A" & tmpLastRow)
            invoiceRange.Rows.EntireRow.Select
            invoiceCurRow = ActiveCell.Row
            invoiceName = Payments.Cells.Range("A" & invoiceCurRow).Value
            YearValue = Payments.Cells.Range("B" & invoiceCurRow).Value

Inside this I have shortened my testing code to see if I can find a swimmer by using the following

 If (StrComp(invoiceName, SwimmerT.SwimmerName, vbTextCompare) = 0) Then
                If (StrComp(YearValue, curYear, vbTextCompare) = 0) Then
                    
                    FoundSwimmer = True
                    MsgBox "We Found " & SwimmerT.SwimmerName & " Invoice Date : " & YearValue
                    'Exit For ' Removing Comment Gives Found Jason, Found No one, Found Cassie
                End If
            Else
                FoundSwimmer = False
                'Exit For
            End If
            Payments.Activate
            
            If FoundSwimmer = False Then
                MsgBox "We Found No one"
            End If
        Next invoiceRange
            

        ThisWorkbook.Activate
    Next SwimmersData

My result is that it finds Jason, then it find No one, Then it Finds No one, Then it Finds Cassie. So it would seem that I am checking every Swimmer against every Payment Row in my Payments sheet.

Any ideas on how I can do this differently? I have spent all Yesterday and will spend most of today on this, so if someone could help relieve some of this stress and save what little bit of sanity I have left I would be grateful.

J.

 

[PHV]

You didn't follow my suggestion.
Sorry, Im busy for the moment but I'll see this later.

 

[JasonEnsor]

My Apologies, I think it is working now. After your last reply I relooked at what you suggested and I believe I had set my Boolean in the wrong place. After a few tests it seems to be running well.

Thank you again PHV

J.