Checking if a reference number exist in a table before copying Data

[Oberon70]

Hi Everyone,

I really appreciate the help in the past as it has been very helpful.

What I am currently attempting to do is to copy invoice details into a table on another workbook. This workbook will have two sheets.

Invoice_Database and Transactions_Database.

I am currently working on Invoice_Database

I have the code taken the information from another workbook. It finds the last cell and adds one for the first blank cell. The data is then copied into the table.

I wanted to know also how would I query if an invoice number has already been entered? That way the data is not copied twice?

The sub that I am wanting this to occur in is Sub CopyInvToDataBaseTable()

Dim wb_created As Workbook
Dim ClosedBook As Workbook
Dim DatabaseBook As Workbook
Dim DatabaseSheetInvoice As Worksheet
Dim DatabaseSheetTransactions As Worksheet
Dim TransactionTable As Table
Dim InvoiceTable As Table
Dim Closedws As Worksheet
Dim FilePath, FileOnly, PathOnly As String
Dim InvoiceNum As Variant
Dim DateofInvoice As Date
Dim PortfolioCode As String
Dim AgentName As String
Dim InvoiceTotal As Double
Dim InvoiceGST As Double
Dim LastRow As Long
Dim userSelectedFile As Variant
Dim WindowTitle As String
Dim fileFilter As String
Dim FilefilterIndex As Integer
Dim RecovAmtCell As String
Dim IncCommCell As Range
Dim NetAmtCell As Range
Dim LastRowNum As Integer
Enum XLFindLast
    xlFindLastRow = 1
    xlFindLastColumn
    xlFindlastCell
End Enum

Sub Start()

Call Open_NM_Statement
Call Obtain_Inv_Details
Call CopyInvToDataBaseTable
Call CreateTable

End Sub


Sub Open_NM_Statement()

WindowTitle = "Choose the QBE Statement"

FilefilterIndex = 3

ChDrive "G"
ChDir "G:\Statements"

userSelectedFile = Application.GetOpenFilename(fileFilter, FilefilterIndex, WindowTitle)

If userSelectedFile = False Then
      
    MsgBox "No file selected."
Else

    
    Set ClosedBook = Workbooks.Open(userSelectedFile)
    FilePath = ClosedBook.FullName
    FileOnly = ClosedBook.Name
    PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))
    
    Set ClosedBook = Workbooks.Open(userSelectedFile)
    Set Closedws = ClosedBook.Sheets("Data")
    Closedws.Visible = True
 
End If


End Sub

Sub Obtain_Inv_Details()

InvoiceNum = Closedws.Range("C37").Value
DateofInvoice = Closedws.Range("C3").Value
PortfolioCode = Closedws.Range("C18").Value
AgentName = Closedws.Range("C9").Value
InvoiceTotal = FormatCurrency(ClosedBook.Sheets(1).Range("K42").Value, 2)
InvoiceGST = FormatCurrency(ClosedBook.Sheets(1).Range("X33").Value, 2)

End Sub


Sub CopyInvToDataBaseTable()

Dim LastRowNum As Integer
Dim x As Integer

Set DatabaseBook = Workbooks.Open(FileName:="G:\Invoice_Database_Sheet.xlsx")
Set DatabaseSheetInvoice = DatabaseBook.Sheets("Invoices_Database")
Set DatabaseSheetTransactions = DatabaseBook.Sheets("Transactions_Database")

DatabaseSheetInvoice.Activate
'Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Range("A" & Rows.Count).End(xlUp).Offset(0).Select
LastRowNum = ActiveCell.Row

LastRowNum = FindLast(xlFindLastRow) + 1



DatabaseSheetInvoice.Range("A" & LastRowNum).Value = AgentName
DatabaseSheetInvoice.Range("B" & LastRowNum).Value = PortfolioCode
DatabaseSheetInvoice.Range("C" & LastRowNum).Value = DateofInvoice
DatabaseSheetInvoice.Range("D" & LastRowNum).Value = InvoiceNum
DatabaseSheetInvoice.Range("E" & LastRowNum).Value = "NA"
DatabaseSheetInvoice.Range("F" & LastRowNum).Value = "NA"
DatabaseSheetInvoice.Range("G" & LastRowNum).Value = InvoiceGST
DatabaseSheetInvoice.Range("H" & LastRowNum).Value = "NA"
DatabaseSheetInvoice.Range("I" & LastRowNum).Value = InvoiceTotal

End Sub

Sub CreateTable()

Closedws.Activate

Closedws.ListObjects.Add(xlSrcRange, Closedws.Range("A$48:" & FindLast(xlFindlastCell)), , xlYes).Name = "Transactions_Table"
'Closedws.ListObjects("New_Table_Name").TableStyle = "TableStyleLight1"
End Sub

Function FindLast(ByVal FindWhat As XLFindLast, Optional ByVal TargetRange As Range) As Variant
    Dim sh               As Worksheet
    Dim RowCol(1 To 2)   As Long, i As Long

    '------------------------------------------------------------------------------------------------------------
    '                                       FindLast Function
    '                                 (update to Ron de Bruin Function)
    '------------------------------------------------------------------------------------------------------------
    'Author     | dmt32
    '------------------------------------------------------------------------------------------------------------
    'Version    | V1 June 2021
    '------------------------------------------------------------------------------------------------------------
    'Purpose    | returns from range with data, last row or last column number or, last used cell address.
    '------------------------------------------------------------------------------------------------------------
    'Parameters |  Name            | Required/Optional | Data type     |   Description
    '           |------------------------------------------------------------------------------------------------
    '           |  FindWhat         |   Required        |   Integer     |   An integer value ( 1 - 3 )
    '           |  TargetRange      |   Optional        |   Range       |   worksheet range
    '------------------------------------------------------------------------------------------------------------
    'Returns    |  Variant
    '------------------------------------------------------------------------------------------------------------
    
    If TargetRange Is Nothing Then Set TargetRange = ActiveSheet.Cells
    Set sh = TargetRange.Parent
    
    FindWhat = IIf(FindWhat > xlFindlastCell, xlFindlastCell, IIf(FindWhat < xlFindLastRow, xlFindLastRow, FindWhat))
    
    On Error Resume Next
    For i = xlRows To xlColumns
        With TargetRange.Find(what:="*", After:=TargetRange.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart, _
                            SearchOrder:=i, SearchDirection:=xlPrevious, MatchCase:=False)
            RowCol(i) = Choose(i, .Row, .Column)
            
        End With
        If RowCol(i) = 0 Then RowCol(i) = 1
    Next i
     On Error GoTo 0
     
    FindLast = IIf(FindWhat = xlFindLastRow, RowCol(xlRows), _
               IIf(FindWhat = xlFindLastColumn, RowCol(xlColumns), _
              sh.Cells(RowCol(xlRows), RowCol(xlColumns)).Address(0, 0)))
End Function

 

[Oberon70]

Thank you, I will. I have some ideas on how to resolve the above issue in the future, but going to see what happens when I move to statements from another provider, but right the above lets me ensure that I am not adding the transactions to the transaction table if the invoice has already been entered

 

[Alex Blakenburg]

Sounds good. Thanks for letting me know.