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]

Alright, I'll check it. Do you want to calculate the Total in invoice sheet using the formula: (Subtotal - Discount + Shipping) × Tax%?

Also, how would you like to update the balance amount in the GC register (Column I)? When rescanning, should the available GC balance update dynamically in the invoice sheet (C14)—for example, showing $4 instead of $20?

 

[AD4GC]

Alright, I'll check it. Do you want to calculate the Total in invoice sheet using the formula: (Subtotal - Discount + Shipping) × Tax%?

Also, how would you like to update the balance amount in the GC register (Column I)? When rescanning, should the available GC balance update dynamically in the invoice sheet (C14)—for example, showing $4 instead of $20?

You are very kind and generous with your time on this matter.

The "Total" (F49) should be Subtotal + Shipping + Tax - Discount

Yes sir, when the GC is scanned again, the value in Invoice "C" should reflect the balance that is remaining in GC Register (Column I), i.e. $4 instead of $20. I did already change the scan_code to pull the Balance (Column I) instead of the GC value (Column G) into the Invoice (Column C) when GC barcode is scanned, I don't know how to get it to re-tally the use and balance to reflect the second scan, basically re-tally with each scan until the GC issued value completely used... if that makes sense.

Thanks,
Greg

 

[Sam_D_Ben]

Please try this new "Scan_Code". Thank You

Sub Scan_CodeUpdated()

    ' Created by Computer Man (419-355-0011) March 2024
    ' Updated by MrExcel.Com February(2025)
    
    Dim wsInvoice As Worksheet, wsUPC As Worksheet, wsGC As Worksheet, sku As String, foundRng As Range
    Dim rowInvoice As Long, rowUPC As Variant, rowGC As Variant, gcRow As Variant
    Dim gcIssuedValue As Double, subtotal As Double, appliedGC As Double, remainingBalance As Double, currentUsed As Double
    
    Set wsInvoice = Worksheets("Invoice")
    Set wsUPC = Worksheets("UPC-SKU")
    Set wsGC = Worksheets("GC Register")

    wsInvoice.Unprotect
    Rows("13:43").Hidden = False

    Do
        sku = InputBox("Scan or Type Barcode (Press OK when done)")

        If sku = "" Then Exit Do

        Set foundRng = wsInvoice.Range("A13:A43").Find(sku, , , xlWhole)
        If Not foundRng Is Nothing Then
            rowInvoice = foundRng.Row
        Else
            rowInvoice = wsInvoice.Cells(43, 1).End(xlUp).Row + 1
        End If

        wsUPC.Unprotect

        rowUPC = Application.Match(sku, wsUPC.Columns(1), 0)
        
        If Not IsError(rowUPC) Then
            If wsUPC.Cells(rowUPC, 5).Value = 0 Then
                MsgBox "Out of stock!", vbOKOnly, "WARNING"
                wsUPC.Protect
                Exit Do
            End If

            With wsInvoice
                .Cells(rowInvoice, "A").Value = sku
                .Cells(rowInvoice, "B").Resize(1, 3).Value = wsUPC.Cells(rowUPC, "B").Resize(1, 3).Value
                .Cells(rowInvoice, "E").Value = .Cells(rowInvoice, "E").Value + 1
            End With

            wsUPC.Cells(rowUPC, "E").Value = wsUPC.Cells(rowUPC, "E").Value - 1
            wsUPC.Cells(rowUPC, "F").Value = wsUPC.Cells(rowUPC, "F").Value + 1

            If wsUPC.Cells(rowUPC, 5).Value > 0 And wsUPC.Cells(rowUPC, 5).Value < 10 Then
                MsgBox "Low Stock Alert" & vbCrLf & vbCrLf & "Qty Remaining: " & wsUPC.Cells(rowUPC, 5).Value, vbOKOnly
            End If

            wsUPC.Protect
            Exit Do
        End If

        rowGC = Application.Match(Val(sku), wsGC.Columns(1), 0)
        If Not IsError(rowGC) Then
            subtotal = wsInvoice.Range("F44").Value
            gcIssuedValue = wsGC.Cells(rowGC, "G").Value
            remainingBalance = wsGC.Cells(rowGC, "I").Value
            currentUsed = wsGC.Cells(rowGC, "H").Value

            If IsEmpty(wsGC.Cells(rowGC, "H").Value) And IsEmpty(wsGC.Cells(rowGC, "I").Value) Then
                remainingBalance = gcIssuedValue
                appliedGC = Application.Min(gcIssuedValue, subtotal)
            ElseIf remainingBalance > 0 Then
                appliedGC = Application.Min(remainingBalance, subtotal)
            Else
                MsgBox "This Gift Card has no remaining balance and is now invalid.", vbOKOnly, "Invalid Gift Card"
                Exit Do
            End If

            wsInvoice.Cells(rowInvoice, "C").Value = appliedGC

            With wsInvoice
                .Cells(rowInvoice, "A").Value = sku
                .Cells(rowInvoice, "B").Value = wsGC.Cells(rowGC, "B").Value
            End With

            wsGC.Cells(rowGC, "H").Value = wsGC.Cells(rowGC, "H").Value + appliedGC
            wsGC.Cells(rowGC, "I").Value = wsGC.Cells(rowGC, "G").Value - wsGC.Cells(rowGC, "H").Value

            wsInvoice.Range("F47").Value = appliedGC

            If wsGC.Cells(rowGC, "I").Value <= 0 Then
                MsgBox "Gift Card Fully Used and Now Invalid!", vbOKOnly, "NOTICE"
                Exit Do
            End If

            Exit Do
        End If

        MsgBox "No matching product or GC found.", vbOKOnly, "ERROR"

    Loop
    
    wsInvoice.Protect

End Sub

 

[AD4GC]

Please try this new "Scan_Code". Thank You

Sub Scan_CodeUpdated()

    ' Created by Computer Man (419-355-0011) March 2024
    ' Updated by MrExcel.Com February(2025)
   
    Dim wsInvoice As Worksheet, wsUPC As Worksheet, wsGC As Worksheet, sku As String, foundRng As Range
    Dim rowInvoice As Long, rowUPC As Variant, rowGC As Variant, gcRow As Variant
    Dim gcIssuedValue As Double, subtotal As Double, appliedGC As Double, remainingBalance As Double, currentUsed As Double
   
    Set wsInvoice = Worksheets("Invoice")
    Set wsUPC = Worksheets("UPC-SKU")
    Set wsGC = Worksheets("GC Register")

    wsInvoice.Unprotect
    Rows("13:43").Hidden = False

    Do
        sku = InputBox("Scan or Type Barcode (Press OK when done)")

        If sku = "" Then Exit Do

        Set foundRng = wsInvoice.Range("A13:A43").Find(sku, , , xlWhole)
        If Not foundRng Is Nothing Then
            rowInvoice = foundRng.Row
        Else
            rowInvoice = wsInvoice.Cells(43, 1).End(xlUp).Row + 1
        End If

        wsUPC.Unprotect

        rowUPC = Application.Match(sku, wsUPC.Columns(1), 0)
       
        If Not IsError(rowUPC) Then
            If wsUPC.Cells(rowUPC, 5).Value = 0 Then
                MsgBox "Out of stock!", vbOKOnly, "WARNING"
                wsUPC.Protect
                Exit Do
            End If

            With wsInvoice
                .Cells(rowInvoice, "A").Value = sku
                .Cells(rowInvoice, "B").Resize(1, 3).Value = wsUPC.Cells(rowUPC, "B").Resize(1, 3).Value
                .Cells(rowInvoice, "E").Value = .Cells(rowInvoice, "E").Value + 1
            End With

            wsUPC.Cells(rowUPC, "E").Value = wsUPC.Cells(rowUPC, "E").Value - 1
            wsUPC.Cells(rowUPC, "F").Value = wsUPC.Cells(rowUPC, "F").Value + 1

            If wsUPC.Cells(rowUPC, 5).Value > 0 And wsUPC.Cells(rowUPC, 5).Value < 10 Then
                MsgBox "Low Stock Alert" & vbCrLf & vbCrLf & "Qty Remaining: " & wsUPC.Cells(rowUPC, 5).Value, vbOKOnly
            End If

            wsUPC.Protect
            Exit Do
        End If

        rowGC = Application.Match(Val(sku), wsGC.Columns(1), 0)
        If Not IsError(rowGC) Then
            subtotal = wsInvoice.Range("F44").Value
            gcIssuedValue = wsGC.Cells(rowGC, "G").Value
            remainingBalance = wsGC.Cells(rowGC, "I").Value
            currentUsed = wsGC.Cells(rowGC, "H").Value

            If IsEmpty(wsGC.Cells(rowGC, "H").Value) And IsEmpty(wsGC.Cells(rowGC, "I").Value) Then
                remainingBalance = gcIssuedValue
                appliedGC = Application.Min(gcIssuedValue, subtotal)
            ElseIf remainingBalance > 0 Then
                appliedGC = Application.Min(remainingBalance, subtotal)
            Else
                MsgBox "This Gift Card has no remaining balance and is now invalid.", vbOKOnly, "Invalid Gift Card"
                Exit Do
            End If

            wsInvoice.Cells(rowInvoice, "C").Value = appliedGC

            With wsInvoice
                .Cells(rowInvoice, "A").Value = sku
                .Cells(rowInvoice, "B").Value = wsGC.Cells(rowGC, "B").Value
            End With

            wsGC.Cells(rowGC, "H").Value = wsGC.Cells(rowGC, "H").Value + appliedGC
            wsGC.Cells(rowGC, "I").Value = wsGC.Cells(rowGC, "G").Value - wsGC.Cells(rowGC, "H").Value

            wsInvoice.Range("F47").Value = appliedGC

            If wsGC.Cells(rowGC, "I").Value <= 0 Then
                MsgBox "Gift Card Fully Used and Now Invalid!", vbOKOnly, "NOTICE"
                Exit Do
            End If

            Exit Do
        End If

        MsgBox "No matching product or GC found.", vbOKOnly, "ERROR"

    Loop
   
    wsInvoice.Protect

End Sub

Thanks for the reply, I have been out-of -town and just got home. I have tried it and so far what I am seeing it works perfectly! I will do a little more testing with it tomorrow to make sure, but I am going to mark this as the solution!
Thanks a ton!
Greg