adding a % Discount

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
I want to add a % discount button to one of my forms. My prices are located in G9:G18 and if a % discount is to be given Its inputed into I3. Anyone have a quick idea how to trans late that into VBA? Thanks
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I'd use a formula on the sheet (using an R1C1 formula) to calculate the discount and pull the discounted price into the form.
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
my price's are inputted into G9:G18 via a Vba code. So I could have any type of formula listed into that range since it would get erased. Thats why I was thinking of VBA. After the price is inputed into the range then you could input your discount into I3 and Click the button for the discount to take effect.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
You could use a _Change event rather than a button:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Count > 1 Then GoTo exitMe                    'more than 1 cell selected
    If Target.Column <> 7 Then GoTo exitMe                  'not column G
    If Target.Row > 18 Or Target.Row < 9 Then GoTo exitMe 'outside valid row range
    If Range("I3").Value = "" Then
        MsgBox "No discount entered", vbCritical, vbOKOnly
        GoTo exitMe
    End If
    Target.Value = Target.Value * (1 - Range("I3").Value)   'apply discount

exitMe:
    Application.EnableEvents = True
    
End Sub

But something like this would work either way, with a few modifications. Hope that helps!
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990

ADVERTISEMENT

The only prblem is that Im already using the Worksheet_Change and the code is a little extensive. So I thought the command button would be easier to apply.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
No problem. Just make it a standalone module, and loop through all the cells one at a time; it shouldn't bog down your worrkbook, since there aren't many.
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990

ADVERTISEMENT

getting a highlighted error at "If Target.Count > 1 Then", not sure why???
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
The range "Target" wouldn't be defined in a standalone module. You'll probably have to resort to something like ActiveCell.
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
that didn't help, couldnt something like this work

Private Sub CommandButton2_Click()
Dim x As integer
x = I2
Range(G9:G18).Value = value * X
End Sub

Im not a coder by any means but just giving it a shot :)
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
Code:
Private Sub CommandButton2_Click()
Dim x As Integer
Range("G9").Value = Range("I2") * Range("G9")
End Sub

Here what I have so far but not getting the result I need. its giving me the discount but I need to subtract it from the orginal figure.....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,844
Messages
5,627,219
Members
416,230
Latest member
jdaitchman

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
Top