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