Applying discount to original cell while retaining formula

JaydenT

New Member
Joined
Dec 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everyone! I am new to VBA and I would like to apply a discount (e.g. 10%) to a cell value in my quotation sheet while retaining (and show) the formula in the same cell at the same time.

Is anyone able to help on this formula?

For example:

Say cell B3 contains $1,000, if I were to apply a 10% discount to it via VBA formula can the end result in the same B3 cell be =1000*90%

Something like this:

Excel.jpg


Thank you.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You can but it's a wacky way to do it. A more common way to do this would be to have a cell containing the discount, and a cell containing the price. Let's say for this example the base price is in C3 and the discount is in D3. You would type 1000 into C3, and start with a discount of 0% in D3. Your formula in B3 is

Excel Formula:
=(1-D3)*C3

It starts out as $1000 and then when you type 10% into D3, it is updated to $900.

Anyway, the answer to your question exactly as you asked it is:

VBA Code:
Range("B3").Formula = "=" & Range("B3").Value & "*90%"
 
Upvote 0
Solution
You can but it's a wacky way to do it. A more common way to do this would be to have a cell containing the discount, and a cell containing the price. Let's say for this example the base price is in C3 and the discount is in D3. You would type 1000 into C3, and start with a discount of 0% in D3. Your formula in B3 is

Excel Formula:
=(1-D3)*C3

It starts out as $1000 and then when you type 10% into D3, it is updated to $900.

Anyway, the answer to your question exactly as you asked it is:

VBA Code:
Range("B3").Formula = "=" & Range("B3").Value & "*90%"
Hi yes, I needed this as I already have a working quotation sheet that was currently done being manually and would like to keep it that way for the time being for simplicity sake.

This is exactly what I needed and it works! Thank you so much for your help!!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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