adding a % Discount

[Giggzz]

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

 

[Tazguy37]

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]

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]

You could use a _Change event rather than a button:

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]

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]

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]

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

 

[Tazguy37]

The range "Target" wouldn't be defined in a standalone module. You'll probably have to resort to something like ActiveCell.

 

[Giggzz]

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]

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.....