Comparing Data in Two Excel Workbooks, Need help to get it functioning properly.

[JasonEnsor]

Hey Guys,

Firstly I apologise for the poorly constructed title, didn't quite know how to categorise this issue.

I am pulling my hair out here on this one, can anybody tell me where I am going wrong? I have a sheet with user details on it called Swimmer Details. When a new payment is made for a swimmer I log the details on a separate workbook named depending on the day of payment, and the sheet is named by month. So currently I would be using the Sunday.xlsm workbook and the February tab. each payment entry stores the name of the swimmer, the year and what time the swim class is that they are on, then some extra data regarding payments.

My aim is to search through the Swimmer Details sheet, and check if the swimmer shows up on the current months sheet with the current year. If so I then create an invoice based on the information that is on the sheet. If the Swimmer does not show up on the sheet then I want to create an invoice that requests full payment but then I want to add data to the current months sheet 'Name, Year, Class Time and a Invoiced'

I have most of this working however I am getting duplicate emails created for Swimmers, if I find a swimmer on my current month then it registers the next swimmer as having no record on the Spreadsheet. The end result should be that every swimmer that shows up in Swimmer Details should have a row of data for the current year and month once I click to generate invoices. There should be no duplicates.

Okay so here is what I have so far......it's starting to get messy again so I apologise but I have been trying so much to get it working to no avail.

' Setup Swimmer Details Worksheet
 Set SwimmerDetails = ThisWorkbook.Sheets("Swimmer Details")
 SwimmerDetails.Activate
        
 With SwimmerDetails

' Find Last Row in Swimmer Details 
[tab]LastRow = SwimmerDetails.Cells(Rows.Count, 1).End(xlUp).Row

' Loop Through Swimmers to Create Swimmer Object
[tab]For Each SwimmersData In SwimmerDetails.Range("A3:A" & LastRow)
[tab][tab]SwimmersData.Rows.EntireRow.Select
[tab][tab]SwimmerT.SwimmerRow = ActiveCell.Row
[tab][tab]SwimmerT.SwimmerName = .Cells.Range(NameColumn & SwimmerT.SwimmerRow).Value
[tab][tab]SwimmerT.ClassTime = .Cells.Range(SwimTimeColumn & SwimmerT.SwimmerRow).Value
[tab][tab]SwimmerT.SwimDay = .Cells.Range(SwimDayColumn & SwimmerT.SwimmerRow).Value
[tab][tab]SwimmerT.isLittleSwimmer = .Cells.Range(LittleSwimmerColumn & SwimmerT.SwimmerRow).Value
[tab][tab]SwimmerT.ContactPrefs = SwimmerDetails.Range(DefaultContactColumn & SwimmerT.SwimmerRow)

' Create an Instance of Word Template
[tab][tab]Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath)

' Set Payments sheet up         
[tab][tab]Set Payments = PaymentData.Sheets(CurMonth)
[tab][tab]Payments.Activate
       
' Get Last Row in Payments         
[tab][tab]tmpLastRow = Payments.Cells(Rows.Count, 1).End(xlUp).Row

' Loop Through Payments                
[tab][tab]For Each invoiceRange In Payments.Range("A2:A" & tmpLastRow)
[tab][tab][tab]invoiceRange.Rows.EntireRow.Select
[tab][tab][tab]invoiceCurRow = ActiveCell.Row
[tab][tab][tab]invoiceName = Payments.Cells.Range("A" & invoiceCurRow).Value
[tab][tab][tab]YearValue = Payments.Cells.Range("B" & invoiceCurRow).Value
               
' Compare Swimmer Name in both worksheets and Compare Invoice Year and Year Selected from UserForm     
[tab][tab][tab]If (StrComp(invoiceName, SwimmerT.SwimmerName, vbTextCompare) = 0) And (StrComp(YearValue, curYear, vbTextCompare) = 0) Then
[tab][tab][tab][tab]MsgBox "We Found " & SwimmerT.SwimmerName & " Invoice Date : " & YearValue
                            
 [tab][tab][tab][tab]If SwimmerT.isLittleSwimmer Then
 [tab][tab][tab][tab][tab]SwimmerFee = Format(CDbl(5.25), "#,##0.00")
[tab][tab][tab][tab]Else
[tab][tab][tab][tab][tab]SwimmerFee = Format(CDbl(8.5), "#,##0.00")
[tab][tab][tab][tab]End If
            
[tab][tab][tab][tab]Dim Wks(1 To 5) As String
[tab][tab][tab][tab] Wks(1) = Payments.Range("D" & invoiceCurRow)
[tab][tab][tab][tab] Wks(2) = Payments.Range("E" & invoiceCurRow)
[tab][tab][tab][tab] Wks(3) = Payments.Range("F" & invoiceCurRow)
[tab][tab][tab][tab] Wks(4) = Payments.Range("G" & invoiceCurRow)
[tab][tab][tab][tab]Wks(5) = Payments.Range("H" & invoiceCurRow)
[tab][tab][tab][tab]Payments.Range("L" & invoiceCurRow).Value = "True"
            
[tab][tab][tab][tab]Dim CarriedFrom
[tab][tab][tab][tab]CarriedFrom = Payments.Range("J" & SwimmerT.SwimmerRow)
[tab][tab][tab][tab]Dim CarriedTo
[tab][tab][tab][tab]CarriedTo = Payments.Range("K" & SwimmerT.SwimmerRow)

[tab][tab][tab][tab]Dim Counter
[tab][tab][tab][tab]Dim wkNo
[tab][tab][tab][tab]Dim Wk
            
[tab][tab][tab][tab]Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath)
[tab][tab][tab][tab]Dim InvoiceTotal As Double
InvoiceTotal = 0

' For Loop to populate word goes here (Removed to reduce code)                                                 
' Pass Data to Word Bookmarks
' Generate Email                                     
[tab][tab][tab][tab]Dim EmailType As String
[tab][tab][tab][tab]EmailType = "Invoice"
[tab][tab][tab][tab]Call SendAsEmail(EmailType, SwimmerT.SwimmerName, CurMonth, curYear, SwimmerT.ContactPrefs, FileName)
[tab][tab][tab][tab]wordDoc.Close SaveChanges:=False
[tab][tab][tab]End If
[tab][tab][tab]Payments.Activate
                    
[tab][tab]Next invoiceRange
                
[tab][tab][tab]If invoiceRange Is Nothing Then   
[tab][tab][tab][tab]MsgBox "We Didn't Find A Record"
' Didn't Find A record so add data to the worksheet and generate full invoice
[tab][tab][tab][tab]invoiceIRow = Payments.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
[tab][tab][tab][tab]Payments.Cells(invoiceIRow, 1).Value = SwimmerT.SwimmerName
[tab][tab][tab][tab]Payments.Cells(invoiceIRow, 2).Value = curYear
[tab][tab][tab][tab]Payments.Cells(invoiceIRow, 3).Value = SwimmerT.ClassTime
[tab][tab][tab][tab]Payments.Cells(invoiceIRow, 12).Value = "True"
                    
[tab][tab][tab][tab]If SwimmerT.isLittleSwimmer Then
[tab][tab][tab][tab][tab]SwimmerFee = Format$(CDbl(5.25), "0.00")
[tab][tab][tab][tab]Else
[tab][tab][tab][tab][tab]SwimmerFee = Format$(CDbl(8.5), "0.00")
[tab][tab][tab][tab]End If


[tab][tab][tab][tab]Dim InvoiceTotal1 As Double
[tab][tab][tab][tab]InvoiceTotal1 = 0
[tab][tab][tab][tab]Dim Counter1
[tab][tab][tab][tab]Dim wkNos

' Set up Word Document and Pass Data Code Removed from here
[tab][tab][tab][tab]Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath)

' Send Invoice Email
[tab][tab][tab][tab]Dim EmailType1 As String
[tab][tab][tab][tab]EmailType1 = "Invoice"
[tab][tab][tab][tab]Call SendAsEmail(EmailType1, SwimmerT.SwimmerName, CurMonth, curYear, SwimmerT.ContactPrefs, FileName1)
[tab][tab][tab][tab]wordDoc.Close SaveChanges:=False

' Activate Current Month Sheet                    
[tab][tab][tab][tab]Payments.Activate
                    
[tab][tab][tab]End If
[tab][tab][tab]ThisWorkbook.Activate
[tab][tab]Next SwimmersData
        
[tab]End With
    
    wordApp.Quit
    PaymentData.Close 1
    
' Unload Userform
    Unload Me

I know I am close, I just can't fathom what I am doing wrong so any help would be appreciated. Normally this wouldn't be an issue as an invoice would be sent out after service has been provided however this project requires an invoice to be sent out first detailing cost of swim lessons for the month. If anything just ask. I know my code is duplicating certain aspects but I want to get it fully functioning before I clean it up again.


Many Thanks

J.

 

[PHV]

I don't know for the duplicates mails; but I'm suspicious about this 2 lines:
CarriedFrom = Payments.Range("J" & SwimmerT.SwimmerRow)
CarriedTo = Payments.Range("K" & SwimmerT.SwimmerRow)

Why use SwimmerT.SwimmerRow instead of invoiceIRow ?

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

 

[JasonEnsor]

Cheers PHV for pointing that out to me....That was a Copy and Paste error on my part as I had copied data from a previous test version after destroying my once working version.

Thanks

J.