Assign VBA Macro to apply discount on protected cell with formula

[Gtasios4]

Hi all,

I have the below quotation tool and in column O i have the line total with has the hidden formula: =M4*N4..

However i want to assign a Macro in -1%, so as to apply 1% discount in the each "line total" and then give the total. I also want to perform the same with 2% discount

I've a VBA code for inserting rows and keeping format & formulas. Would the discount vba code also work when I will insert rows?

Thanks in advance!!

 

[Snakehips]

@Gtasios4 Would the discount vba code also work when I will insert rows?

I think the answer is probably but, it depends.
I assume:
Either 0%, 1% or 2% discount is to be applied to all lines of the same invoice.
You want to keep formulas rather than overwrite with hard values.
So your discount macros will need to either make reference to a 'Discount ' value indicator stored in a cell OR apply a modified formula to suit.
Therefore, I think you would need a 'Full Price' macro as well as -1% and -2%.
Formulas using the hard coded discount % would be like =M4*N4 , =M4*N4*(1-(1)%) , =M4*N4*(1-(2)%)

You could perhaps not use vba, other than your existing?
Below shows 3 Option buttons that are Linked to Q3
The Option selected will return 1, 2 or 3 in Q3, which can be hidden or have text set to white so not be visible.
You then just need a fixed formula in O, like =M4*N4*(1-($Q$3-1)%)

Book1
	M	N	O	P	Q	R	S
2
3	Qty	Unit price			2
4	5	5	24.75
5	2	6	11.88
6			0.00
7			0.00
8		Total:	36.63
Sheet1

Cell Formulas
Range	Formula
O4:O7	O4	=M4*N4*(1-($Q$3-1)%)
O8	O8	=SUM(INDIRECT("O4:O"&ROW()-1))

 

[Gtasios4]

Thanks for the reply and the suggestion . How can I create the below buttons?

 

[Snakehips]

They are Option or Radio buttons.
In short, give or take the UI of your version of Excel:
Developer Tab > Insert or Controls Section > Click to Insert Forms Option Button
Click and drag on sheet to draw / size the button.
Repeat for 2 more or duplicate the first one twice.
In this form, you can only select one at a time.
Right click any one of them > Format Control > Enter Address of the cell you wish to link to (eg Q3)
Then when you select one you will see value 1, 2, or 3 in Q3 or wherever.
You can change the caption on the control either by double-clicking caption or right click > Edit Text
Change the one that creates 1 to be caption for full price, one that creates 2 for -1% and third for -2%
Position them as suits.
Hide the Q3? column if you wish or place an object over it or set its text colour to white or..

The formula I gave should then hopefully react to whichever Option you select.

If all else fails, Google to find video instruction of which there will be loads

Hope that helps.

 

[Gtasios4]

They are Option or Radio buttons.
In short, give or take the UI of your version of Excel:
Developer Tab > Insert or Controls Section > Click to Insert Forms Option Button
Click and drag on sheet to draw / size the button.
Repeat for 2 more or duplicate the first one twice.
In this form, you can only select one at a time.
Right click any one of them > Format Control > Enter Address of the cell you wish to link to (eg Q3)
Then when you select one you will see value 1, 2, or 3 in Q3 or wherever.
You can change the caption on the control either by double-clicking caption or right click > Edit Text
Change the one that creates 1 to be caption for full price, one that creates 2 for -1% and third for -2%
Position them as suits.
Hide the Q3? column if you wish or place an object over it or set its text colour to white or..

The formula I gave should then hopefully react to whichever Option you select.

If all else fails, Google to find video instruction of which there will be loads

Hope that helps.

Thank you so much for the help! It really helps me to automate my quotation tool. I have one question though..

In N Row I use an IF formula which brings us the unit price via VLOOKUP from another sheet and runs a logical test IF it founds a normal price or a promo price. If I want to apply your abovementioned formula | =M4*N4*(1-($Q$3-1)%) | on N column instead of O, how can I do that while keeping the logical test?

 

[Snakehips]

I haven't tested but might this be what you want?

=IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;VLOOKUP(A4;PRODUCT_INFO!A:C;3;0);VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))*(1-($Q$3-1)%)

 

[Gtasios4]

I haven't tested but might this be what you want?

=IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;VLOOKUP(A4;PRODUCT_INFO!A:C;3;0);VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))*(1-($Q$3-1)%)

Thank you for your valuable help! much appreciated! It works

 

[Gtasios4]

I haven't tested but might this be what you want?

=IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;VLOOKUP(A4;PRODUCT_INFO!A:C;3;0);VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))*(1-($Q$3-1)%)

Hi Snakehips,

I am facing a problem.. I've changed in the above formula the table array of the VLOOKUP to another sheet named SΕRVERS & OPTIONS as followed

=IFERROR(IF(VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:D;4;0)=0;VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:C;3;0);VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:D;4;0))*(1-($S$2-1)%)

However, I get the values of the column 4, I don't get the values of the column 3 (A5;'SΕRVERS & OPTIONS'!A:C;3;0)

In the sheet "SERVER & OPTIONS" in column 3 I have the value formula =VLOOKUP(A4;PRODUCT_INFO!A:C;3;0) while in column 4 I have the value formula =IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;"-";VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))

Any hint on how to solve it?

 

[Gtasios4]

I haven't tested but might this be what you want?

=IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;VLOOKUP(A4;PRODUCT_INFO!A:C;3;0);VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))*(1-($Q$3-1)%)

Dear Snakehips,

I am coming back into this thread, because I face a problem with the above code, when I change the table array into another sheet, I don't get the value of the value if true VLOOKUP(A4;'OPTIONS'!A:C;3;0)

Any feedback why that's happening?

 

[Snakehips]

Hi,
In Server & Options sheet you have formula that returns '-' if no discount price.

while in column 4 I have the value formula =IF(VLOOKUP(A4;PRODUCT_INFO!A:D;4;0)=0;"-";VLOOKUP(A4;PRODUCT_INFO!A:D;4;0))

Yet your lookup formula is testing for possibility of price in D being 0
Try changing formula to test for '-' !!

=IFERROR(IF(VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:D;4;0)="-";VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:C;3;0);VLOOKUP(A5;'SΕRVERS & OPTIONS'!A:D;4;0))*(1-($S$2-1)%);"????)