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]

Anyway' Please take your time and do a detailed analysis for additions and corrections. May be we incorporate everthing on your old "Scan_Code". Thank You

 

[AD4GC]

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

I can see how that was misconstrued, I was not nearly thorough enough in my question, please accept my apologies.

Anyway' Please take your time and do a detailed analysis for additions and corrections. May be we incorporate everthing on your old "Scan_Code". Thank You

I think you're asking me to double check to make sure there is nothing else needed or wanted, correct?

In the photo below, the title "Discount" changes to "Gift Certificate" in my full invoice when a GC barcode is scanned, but not in this test invoice.
I believe the only other issue I need to resolve is figuring out how to get the total that is circled to match the actual total (Sum of subtotal, shipping, and tax) if the actual total is less than the GC value, but if the actual total is more than the GC value to only show the GC value. In this example, the GC is worth $20, but the item being purchased is $16, leaving $4 balance, I want to try to get the circled "Discount" value to reflect how much of the GC is used toward the purchase if the final price is less than the GC value.
This may even be achievable with a formula, but I have tried many and so far I haven't found one to work this way.

 

[Sam_D_Ben]

For the actual discount, please apply this formula in cell F47 and test it. [Also Please update your Excel version on your profile]. Thank You

=IFERROR(MIN($F$44, LOOKUP(1, 0/(ISNUMBER(MATCH(Invoice!$A$13:$A$43, 'GC Register'!$A$1:$A$100, 0))), Invoice!$C$13:$C$43)), "")