VBA to input data into a second sheet using formulas

[AD4GC]

I have this question posted over at excelforum also and so far for the past four days there has been no help.
Help with VBA to input data into a second sheet using formulas

I really have no idea where to start trying to stitch something together for this.

I have a Honey and Honeybee business, I am getting ready to start offering gift certificates and gift cards (gift cards are online and handled by Square). I have a sheet ("GC Register") for gift certificate serial numbers (barcodes) this is separate from my general items "UPC-SKU" sheet.

Barcodes are scannned into Column A of "Invoice" in rows 13 through 46.
On the Gift Certificate Register ("GC Register") sheet, I have formulas to calculate how much of the gift certificate was used and to tally a balance based on if Column A of the GC Register sheet is >0 OR (with a countif) GC Register A:A = Invoice A13:A43. Being they are IF statements, unfortunately they are dynamic so when I run my macro to print, save and create a new invoice, the values from the formulas are no longer present. And here lies my query.

I need help with creating a macro to search or match if a gift certificate serial number has been entered on the invoice, find the cooresponding serial number and input the values of how much of the gift certificate was used and a balance.

The formulas I am using are:

Amount Used: ("GC Register" Column H)
=IF(OR(A2>0,(COUNTIF(Invoice!$A$13:$A$43,A2))),IF(G2>SUM(Invoice!$F$13:$F$43),SUM(Invoice!$F$13:$F$43),IF(SUM(Invoice!$F$13:$F$43)>G2,G2)))

Balance: ("GC Register" Column I)
=IF(COUNTIF(Invoice!$A$13:$A$43,A:A),IF(SUM(Invoice!$F$13:$F$43)>=G2,0,SUM(G2-SUM(Invoice!$F$13:$F$43))))

I used the OR function in the above formula only to always display a value and not "FALSE" in the cell.
I hope this makes sense to somebody else..... LOL

Thanks for looking,
Greg

 

[Sam_D_Ben]

Hi,
Unable to work on your file, it was acting up and for that reason dint open it fully. Can you please copy paste, "scan_code" module here.

Please check this in a different module and see if it work. Run this after "Scan_Code". Thank You

Sub Update_Gift_Certificate_Usage()
    Dim wsInvoice As Worksheet, wsGC As Worksheet
    Dim lastRowGC As Long, invRow As Long, gcRow As Long
    Dim gcBarcode As String, issuedValue As Double
    Dim totalUsed As Double, balanceLeft As Double
    Dim isProtected As Boolean, totalInvoiceAmount As Double
    Dim found As Range

    Set wsInvoice = Worksheets("Invoice")
    Set wsGC = Worksheets("GC Register")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    isProtected = wsInvoice.ProtectContents
    If isProtected Then wsInvoice.Unprotect

    lastRowGC = wsGC.Cells(Rows.Count, 1).End(xlUp).Row
    totalInvoiceAmount = Application.WorksheetFunction.Sum(wsInvoice.Range("F13:F43"))

    If totalInvoiceAmount = 0 Then GoTo Cleanup

    For invRow = 13 To 43
        gcBarcode = Trim(wsInvoice.Cells(invRow, "A").Value)
        If gcBarcode <> "" Then
            Set found = wsGC.Range("A2:A" & lastRowGC).Find(gcBarcode, LookAt:=xlWhole)
            If Not found Is Nothing Then
                gcRow = found.Row
                
                issuedValue = wsGC.Cells(gcRow, "G").Value
                totalUsed = wsGC.Cells(gcRow, "H").Value
                balanceLeft = wsGC.Cells(gcRow, "I").Value

                wsGC.Cells(gcRow, "H").Value = Application.WorksheetFunction.Min(totalInvoiceAmount, issuedValue)
                wsGC.Cells(gcRow, "I").Value = issuedValue - wsGC.Cells(gcRow, "H").Value

                wsGC.Cells(gcRow, "H").NumberFormat = "$#,##0.00"
                wsGC.Cells(gcRow, "I").NumberFormat = "$#,##0.00"
            End If
        End If
    Next invRow

Cleanup:
    If isProtected Then wsInvoice.Protect

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[AD4GC]

Hi,
Unable to work on your file, it was acting up and for that reason dint open it fully. Can you please copy paste, "scan_code" module here.

Please check this in a different module and see if it work. Run this after "Scan_Code". Thank You

Sub Update_Gift_Certificate_Usage()
    Dim wsInvoice As Worksheet, wsGC As Worksheet
    Dim lastRowGC As Long, invRow As Long, gcRow As Long
    Dim gcBarcode As String, issuedValue As Double
    Dim totalUsed As Double, balanceLeft As Double
    Dim isProtected As Boolean, totalInvoiceAmount As Double
    Dim found As Range

    Set wsInvoice = Worksheets("Invoice")
    Set wsGC = Worksheets("GC Register")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    isProtected = wsInvoice.ProtectContents
    If isProtected Then wsInvoice.Unprotect

    lastRowGC = wsGC.Cells(Rows.Count, 1).End(xlUp).Row
    totalInvoiceAmount = Application.WorksheetFunction.Sum(wsInvoice.Range("F13:F43"))

    If totalInvoiceAmount = 0 Then GoTo Cleanup

    For invRow = 13 To 43
        gcBarcode = Trim(wsInvoice.Cells(invRow, "A").Value)
        If gcBarcode <> "" Then
            Set found = wsGC.Range("A2:A" & lastRowGC).Find(gcBarcode, LookAt:=xlWhole)
            If Not found Is Nothing Then
                gcRow = found.Row
               
                issuedValue = wsGC.Cells(gcRow, "G").Value
                totalUsed = wsGC.Cells(gcRow, "H").Value
                balanceLeft = wsGC.Cells(gcRow, "I").Value

                wsGC.Cells(gcRow, "H").Value = Application.WorksheetFunction.Min(totalInvoiceAmount, issuedValue)
                wsGC.Cells(gcRow, "I").Value = issuedValue - wsGC.Cells(gcRow, "H").Value

                wsGC.Cells(gcRow, "H").NumberFormat = "$#,##0.00"
                wsGC.Cells(gcRow, "I").NumberFormat = "$#,##0.00"
            End If
        End If
    Next invRow

Cleanup:
    If isProtected Then wsInvoice.Protect

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Thanks for the help, I will go try it now. Here is the Scan_Code

Sub Scan_Code()
'created by Computer Man (419-355-0011) March 2024
Worksheets("Invoice").Unprotect
Rows("13:46").Hidden = False
10
Application.ScreenUpdating = True
    sku = InputBox("Scan or Type Barcode")
Application.ScreenUpdating = False

If sku = "" Then

For n = 21 To 46 'show \hide row with data
    If Cells(n, "A") = "" Then Rows(n).Hidden = True
Next n
Application.ScreenUpdating = True
Worksheets("Invoice").Protect

Exit Sub
End If

Set foundRng = Range("A13:A47").Find(sku, , , xlWhole)
If foundRng Is Nothing Then
    r = [A47].End(xlUp).Row + 1
Else
    r = foundRng.Row
End If

Dim rs As Worksheet
Dim rr As Variant
Set rs = Worksheets("UPC-SKU")
rs.Unprotect
rr = Application.Match(sku, rs.Columns(1), 0)
If Not IsError(rr) Then
    If rs.Cells(rr, 5) = 0 Then MsgBox "Out of stock!", vbOKOnly, "WARNING": Exit Sub
If Not IsError(rr) Then
'r = Register Column, rr = Invoice Column, rs = worksheet set
Cells(r, "A") = sku
Cells(r, "B") = rs.Cells(rr, "B") 'desc
Cells(r, "C") = rs.Cells(rr, "C") 'price
Cells(r, "D") = rs.Cells(rr, "D") 'tax
If rs.Cells(rr, "E").value > 0 Then Cells(r, "E") = Cells(r, "E") + 1 'increase qty
rs.Cells(rr, "E") = rs.Cells(rr, "E") - 1 'Decrease Inventory
rs.Cells(rr, "F") = rs.Cells(rr, "F") + 1
 If rs.Cells(rr, 5) > 0 And rs.Cells(rr, 5) < 10 Then
        msg = MsgBox("Low Stock Alert" & vbCrLf & _
        vbCrLf & "Qty Remaining:  " & rs.Cells(rr, 5).value, vbOKOnly)
End If
End If

GoTo 10

Else
    Set rt = Worksheets("GC Register")
    rr = Application.Match(val(sku), rt.Columns(1), 0)
    If Not IsError(rr) Then
        Cells(r, "A") = sku
        Cells(r, "B") = rt.Cells(rr, "B") 'desc
        Cells(r, "C") = rt.Cells(rr, "G") 'balance
    Else: GoTo 999
End If

GoTo 10

'''error checking
999
Application.ScreenUpdating = True

With Update_Or_Add_New
.Width = 350
.Height = 255
.Show
   End With

GoTo 10

End If
End Sub

 

[AD4GC]

Please check this in a different module and see if it work. Run this after "Scan_Code". Thank You

The Code works exactly like I asked for when I ran it from the module window. Thank you!

Please forgive my ignorance, what do you mean by "run this after the "Scan_Code"? How do I do that?

Also, is there a way to put into this code a line or something that will take the value in column C of the Invoice row when a GC barcode is scanned and input the GC amount that is used into "F50" (my cell designated for discounts or Gift Certificates) but not to exceed the amount of the issued value of the gift certificate?
For example, the gift certificate is for $20, Customer John buys a bottle of honey for $14, I am wanting "F50" to read "$14" instead of the $20 issued value of the GC, and likewise if Customer John buys two bottles of honey at $14 each, $28 total, that "F50" would read $20 because the GC is only worth $20....? Is something like this possible? I have been playing with different formulas and can't seem come up with anything that works for both scenarios.

Thanks,
Greg

 

[Sam_D_Ben]

I am sorry, your file is clean, someother file was acting up. {"run this after the "Scan_Code"? How do I do that?} - I mean like calling both macros.

Well, i'll give it to you. Please assign this "Scan_Code_New" to the "scan code" tab in the Invoice sheet. I hope you know how to assign it. And ref: F50, will check. Thank You

Sub Scan_Code_New()
    Application.ScreenUpdating = False

    Call Scan_Code
    Call Update_Gift_Certificate_Usage

    Application.ScreenUpdating = True
    
End Sub
Sub Scan_Code()

'created by Computer Man (419-355-0011) March 2024
Worksheets("Invoice").Unprotect
Rows("13:43").Hidden = False
10
Application.ScreenUpdating = True
    sku = InputBox("Scan or Type Barcode")
Application.ScreenUpdating = False

If sku = "" Then

For n = 21 To 43 'show \hide row with data
    If Cells(n, "A") = "" Then Rows(n).Hidden = True
Next n
Application.ScreenUpdating = True
Worksheets("Invoice").Protect

Exit Sub
End If

Set foundRng = Range("A13:A44").Find(sku, , , xlWhole)
If foundRng Is Nothing Then
    r = [A44].End(xlUp).Row + 1
Else
    r = foundRng.Row
End If

Dim rs As Worksheet
Dim rr As Variant
Set rs = Worksheets("UPC-SKU")
rs.Unprotect
rr = Application.Match(sku, rs.Columns(1), 0)

If Not IsError(rr) Then
'r = Register Column, rr = Invoice Column, rs = worksheet set
Cells(r, "A") = sku
Cells(r, "B") = rs.Cells(rr, "B") 'desc
Cells(r, "C") = rs.Cells(rr, "C") 'price
Cells(r, "D") = rs.Cells(rr, "D") 'tax
Cells(r, "E") = Cells(r, "E") + 1 'increase qty

GoTo 10

Else
    Set rt = Worksheets("GC Register")
    rr = Application.Match(val(sku), rt.Columns(1), 0)
    If Not IsError(rr) Then
        Cells(r, "A") = sku
        Cells(r, "B") = rt.Cells(rr, "B") 'desc
        Cells(r, "C") = rt.Cells(rr, "G") 'balance
    Else: GoTo 999
End If

GoTo 10

'''error checking
999
Application.ScreenUpdating = True

With Update_Or_Add_New
.Width = 350
.Height = 255
.Show
   End With

GoTo 10

End If
End Sub

Sub Update_Gift_Certificate_Usage()
    Dim wsInvoice As Worksheet, wsGC As Worksheet
    Dim lastRowGC As Long, invRow As Long, gcRow As Long
    Dim gcBarcode As String, issuedValue As Double
    Dim totalUsed As Double, balanceLeft As Double
    Dim isProtected As Boolean, totalInvoiceAmount As Double
    Dim found As Range

    Set wsInvoice = Worksheets("Invoice")
    Set wsGC = Worksheets("GC Register")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    isProtected = wsInvoice.ProtectContents
    If isProtected Then wsInvoice.Unprotect

    lastRowGC = wsGC.Cells(Rows.Count, 1).End(xlUp).Row
    totalInvoiceAmount = Application.WorksheetFunction.Sum(wsInvoice.Range("F13:F43"))

    If totalInvoiceAmount = 0 Then GoTo Cleanup

    For invRow = 13 To 43
        gcBarcode = Trim(wsInvoice.Cells(invRow, "A").Value)
        If gcBarcode <> "" Then
            Set found = wsGC.Range("A2:A" & lastRowGC).Find(gcBarcode, LookAt:=xlWhole)
            If Not found Is Nothing Then
                gcRow = found.Row
                
                issuedValue = wsGC.Cells(gcRow, "G").Value
                totalUsed = wsGC.Cells(gcRow, "H").Value
                balanceLeft = wsGC.Cells(gcRow, "I").Value

                wsGC.Cells(gcRow, "H").Value = Application.WorksheetFunction.Min(totalInvoiceAmount, issuedValue)
                wsGC.Cells(gcRow, "I").Value = issuedValue - wsGC.Cells(gcRow, "H").Value

                wsGC.Cells(gcRow, "H").NumberFormat = "$#,##0.00"
                wsGC.Cells(gcRow, "I").NumberFormat = "$#,##0.00"
            End If
        End If
    Next invRow

Cleanup:
    If isProtected Then wsInvoice.Protect

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[AD4GC]

I am sorry, your file is clean, someother file was acting up. {"run this after the "Scan_Code"? How do I do that?} - I mean like calling both macros.

Well, i'll give it to you. Please assign this "Scan_Code_New" to the "scan code" tab in the Invoice sheet. I hope you know how to assign it. And ref: F50, will check. Thank You

Sub Scan_Code_New()
    Application.ScreenUpdating = False

    Call Scan_Code
    Call Update_Gift_Certificate_Usage

    Application.ScreenUpdating = True
   
End Sub
Sub Scan_Code()

'created by Computer Man (419-355-0011) March 2024
Worksheets("Invoice").Unprotect
Rows("13:43").Hidden = False
10
Application.ScreenUpdating = True
    sku = InputBox("Scan or Type Barcode")
Application.ScreenUpdating = False

If sku = "" Then

For n = 21 To 43 'show \hide row with data
    If Cells(n, "A") = "" Then Rows(n).Hidden = True
Next n
Application.ScreenUpdating = True
Worksheets("Invoice").Protect

Exit Sub
End If

Set foundRng = Range("A13:A44").Find(sku, , , xlWhole)
If foundRng Is Nothing Then
    r = [A44].End(xlUp).Row + 1
Else
    r = foundRng.Row
End If

Dim rs As Worksheet
Dim rr As Variant
Set rs = Worksheets("UPC-SKU")
rs.Unprotect
rr = Application.Match(sku, rs.Columns(1), 0)

If Not IsError(rr) Then
'r = Register Column, rr = Invoice Column, rs = worksheet set
Cells(r, "A") = sku
Cells(r, "B") = rs.Cells(rr, "B") 'desc
Cells(r, "C") = rs.Cells(rr, "C") 'price
Cells(r, "D") = rs.Cells(rr, "D") 'tax
Cells(r, "E") = Cells(r, "E") + 1 'increase qty

GoTo 10

Else
    Set rt = Worksheets("GC Register")
    rr = Application.Match(val(sku), rt.Columns(1), 0)
    If Not IsError(rr) Then
        Cells(r, "A") = sku
        Cells(r, "B") = rt.Cells(rr, "B") 'desc
        Cells(r, "C") = rt.Cells(rr, "G") 'balance
    Else: GoTo 999
End If

GoTo 10

'''error checking
999
Application.ScreenUpdating = True

With Update_Or_Add_New
.Width = 350
.Height = 255
.Show
   End With

GoTo 10

End If
End Sub

Sub Update_Gift_Certificate_Usage()
    Dim wsInvoice As Worksheet, wsGC As Worksheet
    Dim lastRowGC As Long, invRow As Long, gcRow As Long
    Dim gcBarcode As String, issuedValue As Double
    Dim totalUsed As Double, balanceLeft As Double
    Dim isProtected As Boolean, totalInvoiceAmount As Double
    Dim found As Range

    Set wsInvoice = Worksheets("Invoice")
    Set wsGC = Worksheets("GC Register")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    isProtected = wsInvoice.ProtectContents
    If isProtected Then wsInvoice.Unprotect

    lastRowGC = wsGC.Cells(Rows.Count, 1).End(xlUp).Row
    totalInvoiceAmount = Application.WorksheetFunction.Sum(wsInvoice.Range("F13:F43"))

    If totalInvoiceAmount = 0 Then GoTo Cleanup

    For invRow = 13 To 43
        gcBarcode = Trim(wsInvoice.Cells(invRow, "A").Value)
        If gcBarcode <> "" Then
            Set found = wsGC.Range("A2:A" & lastRowGC).Find(gcBarcode, LookAt:=xlWhole)
            If Not found Is Nothing Then
                gcRow = found.Row
               
                issuedValue = wsGC.Cells(gcRow, "G").Value
                totalUsed = wsGC.Cells(gcRow, "H").Value
                balanceLeft = wsGC.Cells(gcRow, "I").Value

                wsGC.Cells(gcRow, "H").Value = Application.WorksheetFunction.Min(totalInvoiceAmount, issuedValue)
                wsGC.Cells(gcRow, "I").Value = issuedValue - wsGC.Cells(gcRow, "H").Value

                wsGC.Cells(gcRow, "H").NumberFormat = "$#,##0.00"
                wsGC.Cells(gcRow, "I").NumberFormat = "$#,##0.00"
            End If
        End If
    Next invRow

Cleanup:
    If isProtected Then wsInvoice.Protect

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

You sir are awesome! I thought maybe that was what you were meaning but I wasn't sure. Thank you very kindly.

 

[AD4GC]

I am sorry, your file is clean, someother file was acting up. {"run this after the "Scan_Code"? How do I do that?} - I mean like calling both macros.

Well, i'll give it to you. Please assign this "Scan_Code_New" to the "scan code" tab in the Invoice sheet. I hope you know how to assign it. And ref: F50, will check. Thank You

Sub Scan_Code_New()
    Application.ScreenUpdating = False

    Call Scan_Code
    Call Update_Gift_Certificate_Usage

    Application.ScreenUpdating = True
   
End Sub
Sub Scan_Code()

'created by Computer Man (419-355-0011) March 2024
Worksheets("Invoice").Unprotect
Rows("13:43").Hidden = False
10
Application.ScreenUpdating = True
    sku = InputBox("Scan or Type Barcode")
Application.ScreenUpdating = False

If sku = "" Then

For n = 21 To 43 'show \hide row with data
    If Cells(n, "A") = "" Then Rows(n).Hidden = True
Next n
Application.ScreenUpdating = True
Worksheets("Invoice").Protect

Exit Sub
End If

Set foundRng = Range("A13:A44").Find(sku, , , xlWhole)
If foundRng Is Nothing Then
    r = [A44].End(xlUp).Row + 1
Else
    r = foundRng.Row
End If

Dim rs As Worksheet
Dim rr As Variant
Set rs = Worksheets("UPC-SKU")
rs.Unprotect
rr = Application.Match(sku, rs.Columns(1), 0)

If Not IsError(rr) Then
'r = Register Column, rr = Invoice Column, rs = worksheet set
Cells(r, "A") = sku
Cells(r, "B") = rs.Cells(rr, "B") 'desc
Cells(r, "C") = rs.Cells(rr, "C") 'price
Cells(r, "D") = rs.Cells(rr, "D") 'tax
Cells(r, "E") = Cells(r, "E") + 1 'increase qty

GoTo 10

Else
    Set rt = Worksheets("GC Register")
    rr = Application.Match(val(sku), rt.Columns(1), 0)
    If Not IsError(rr) Then
        Cells(r, "A") = sku
        Cells(r, "B") = rt.Cells(rr, "B") 'desc
        Cells(r, "C") = rt.Cells(rr, "G") 'balance
    Else: GoTo 999
End If

GoTo 10

'''error checking
999
Application.ScreenUpdating = True

With Update_Or_Add_New
.Width = 350
.Height = 255
.Show
   End With

GoTo 10

End If
End Sub

Sub Update_Gift_Certificate_Usage()
    Dim wsInvoice As Worksheet, wsGC As Worksheet
    Dim lastRowGC As Long, invRow As Long, gcRow As Long
    Dim gcBarcode As String, issuedValue As Double
    Dim totalUsed As Double, balanceLeft As Double
    Dim isProtected As Boolean, totalInvoiceAmount As Double
    Dim found As Range

    Set wsInvoice = Worksheets("Invoice")
    Set wsGC = Worksheets("GC Register")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    isProtected = wsInvoice.ProtectContents
    If isProtected Then wsInvoice.Unprotect

    lastRowGC = wsGC.Cells(Rows.Count, 1).End(xlUp).Row
    totalInvoiceAmount = Application.WorksheetFunction.Sum(wsInvoice.Range("F13:F43"))

    If totalInvoiceAmount = 0 Then GoTo Cleanup

    For invRow = 13 To 43
        gcBarcode = Trim(wsInvoice.Cells(invRow, "A").Value)
        If gcBarcode <> "" Then
            Set found = wsGC.Range("A2:A" & lastRowGC).Find(gcBarcode, LookAt:=xlWhole)
            If Not found Is Nothing Then
                gcRow = found.Row
               
                issuedValue = wsGC.Cells(gcRow, "G").Value
                totalUsed = wsGC.Cells(gcRow, "H").Value
                balanceLeft = wsGC.Cells(gcRow, "I").Value

                wsGC.Cells(gcRow, "H").Value = Application.WorksheetFunction.Min(totalInvoiceAmount, issuedValue)
                wsGC.Cells(gcRow, "I").Value = issuedValue - wsGC.Cells(gcRow, "H").Value

                wsGC.Cells(gcRow, "H").NumberFormat = "$#,##0.00"
                wsGC.Cells(gcRow, "I").NumberFormat = "$#,##0.00"
            End If
        End If
    Next invRow

Cleanup:
    If isProtected Then wsInvoice.Protect

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

One other note, I have found that after scanning the GC barcode, it calculates usage and balance in the GC Register, however, if it is scanned again it does not recalculate, is this possible?
Thanks, and I apologize for so many questions and requests.
Greg

 

[Sam_D_Ben]

GC is one time use, and what do you mean by recalculation, if re-scanning the same GC, try clearing the cells. I did that to prevent doubling.

 

[AD4GC]

GC is one time use, and what do you mean by recalculation, if re-scanning the same GC, try clearing the cells. I did that to prevent doubling.

If the items purchased do not sum to the amount of the GC, there is a balance remaining, I have Scan_Code_New now set to pull the balance (Column I) into the invoice instead of the issued amount, and with your code it calculates the usage and balance perfectly.

But, when Customer John comes back to buy another bottle of honey and wants to use the GC again, it will pull the balance into the new invoice, but it does not re-tally the new usage and balance, it can be scanned endless times in new invoices and it will return the initial tallied balance.

I hope that makes sense.

If Customer John had a $500 GC and "purchased" a $100 product, when I scanned the GC for the second and beyond invoice, it would return $400 as the usable balance on each one. Can this be made to re-tally the usage and balance with what has already been used or is this something I will just have to keep track of and adjust manually?

I do truly thank you for your time and effort in this matter.
Greg

 

[Sam_D_Ben]

I got your point, but that is totally a different scenario from your orginal post.

I need help with creating a macro to search or match if a gift certificate serial number has been entered on the invoice, find the cooresponding serial number and input the values of how much of the gift certificate was used and a balance.