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]

I thought maybe using the match function but tell it look by column using the Header Name for the column. In this case the header is Invoice Number.

However, I am having issues on how to would right the if statement.

 

[Oberon70]

Okay, I am going to take a break. I have mostly been playing around with setting a table and column as a variable, but I haven't been able to figure out how i can check if a value (invoice number) is already in the column "Invoice Number". I have trying to figure out a way by using the Name in the Header of the table.

Sub MatchTest()

Dim xy As String
Dim lo As ListObject
Dim InvoiceColmn As ListColumn
Dim rngDataBodyRange As Range

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

Set lo = DBaseInv.ListObjects("InvoiceDataBase")
Set InvoiceColmn = lo.ListColumns("Invoice Number")
Set rngDataBodyRange = InvoiceColmn.DataBodyRange

Debug.Print lo.Name

End Sub

 

[Alex Blakenburg]

Using your code in the post above see if this gives you what you need.

Sub MatchTest()

    Dim xy As String
    Dim lo As ListObject
    Dim InvoiceColmn As ListColumn
    Dim rngDataBodyRange As Range
    
    '------------- added these 2 definitions -------------
    Dim DatabaseBook As Workbook
    Dim DBaseInv As Worksheet
    
    Set DatabaseBook = Workbooks.Open(Filename:="G:\Invoice_Database_Sheet.xlsx")
    Set DBaseInv = DatabaseBook.Sheets("Invoices_Database")
    Set DatabaseSheetTransactions = DatabaseBook.Sheets("Transactions_Database")
    
    Set lo = DBaseInv.ListObjects("InvoiceDataBase")
    Set InvoiceColmn = lo.ListColumns("Invoice Number")
    Set rngDataBodyRange = InvoiceColmn.DataBodyRange
    
    '----------------------------------------------------------------------
    'Added to your post # 3
    '----------------------------------------------------------------------
    Dim rowInvNo As Long
    Dim InvoiceNum As Variant   ' I would normally use long or string but the main module has this as a variant
    
    InvoiceNum = 7506               ' Added for test purposes only            
    
    With Application
        rowInvNo = .IfError(.Match(InvoiceNum, rngDataBodyRange, 0), 0)
    End With
    
    If rowInvNo = 0 Then
        MsgBox "Invoice No: " & InvoiceNum & "  not found"
    Else
        MsgBox "Invoice No: " & InvoiceNum & "  already exists"
    End If
    '----------------------------------------------------------------------
    
    Debug.Print lo.Name

End Sub

 

[Oberon70]

Thank you that works perfectly

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[Oberon70]

Okay, it worked perfectly when I used the InvoiceNum for Test purposes, but as soon as I removed InvoiceNum = 7506 it wouldn't find a match. The invoice number is taken from the statement, but also this will be used for different type of statements and some statements will have numbers, letters and symbols. example RIR-2906. Any idea why the match doesn't work?

 

[Alex Blakenburg]

Start with picking an empty cell and putting = PointToInvoiceDatabaseInvoiceNo = PointToTransactionInvoiceNo
Do you get TRUE ?

If not in the case of RIR-2906 check the length on both cells, to see if they are they same or whether one has additional spaces in it.
For a number check both cells to see if they are being treated as a number or text you can use IsNumber or IsText.

 

[Oberon70]

Okay, so I wrote the below code and it has come back false.

Sub testvariables()

Dim yy As String
Dim yz As String

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

yy = InvoiceNum = DBaseInv.Range("D5").Value
Debug.Print yy

End Sub

The cell on the Database spreadsheet is set to General, but the cell on the original invoice is set to text.
I changed the Database Column for Invoice Number to Text and it has come back as True.

The code you wrote is now working perfectly.

 

[Alex Blakenburg]

Great work troubleshooting it.
We just need to make sure, we are not adding any manual steps in terms of refreshing the data.
If we are and you can’t figure out to automate it, come back to us.