If, Then, Else Test for Invoice number before adding records to list.

[JohnZ1156]

I have a simple invoice sheet. On it there is a pregenerated invoice nuumber. After filling in the invoice data , I have a macro that will copy the details to an "Invoice List" sheet, however, I don't want it to copy if it has already copied the data. Best way I can think of is to check for the invoice number.

For example:
If the current invoice number (InvNo) is equal to the maxumum invoice number on the "Invoice List" sheet, (Column A)

then message, "Invoice already exists", and do nothing.

Else copy data to "List sheet" and clear invoice sheet ready for next entry.

Here are my variables:
Current Invoice number = InvNo
Invoice number column in list sheet is column A

 

[earthworm]

I have a simple invoice sheet. On it there is a pregenerated invoice nuumber. After filling in the invoice data , I have a macro that will copy the details to an "Invoice List" sheet, however, I don't want it to copy if it has already copied the data. Best way I can think of is to check for the invoice number.

For example:
If the current invoice number (InvNo) is equal to the maxumum invoice number on the "Invoice List" sheet, (Column A)

then message, "Invoice already exists", and do nothing.

Else copy data to "List sheet" and clear invoice sheet ready for next entry.

Here are my variables:
Current Invoice number = InvNo
Invoice number column in list sheet is column A

Use (iserror (vlookup in the macro to run only if the result is true. it means that if the value is not present in list then run macro only.

 

[Alex Blakenburg]

Something like this:-

Sub TestLookForInvNo()

    Dim InvNo As Long
    Dim wsInvList As Worksheet
    
    InvNo = 11        ' Feed in your Invoice Number
    
    Set wsInvList = Worksheets("Invoice List")
    
    If IsError(Application.Match(InvNo, wsInvList.Columns("A"), 0)) = False Then
        MsgBox "Invoice already exists"
        Exit Sub
    End If
    
    ' Do something

End Sub