VBA to input data into a second sheet using formulas

AD4GC

New Member
Joined
Feb 2, 2017
Messages
18
Office Version
  1. 2007
Platform
  1. Windows
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
 
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?
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top