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
 

Giggzz

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

Why wont the Target.Row work in place of G9:G18(which is the actual range)? Thanks to Taz for part of his code...
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
Code:
Private Sub CommandButton2_Click()
Dim x As Integer
x = I2
If Range("G" & Target.Row) > 0 Then Range("G" & Target.Row).Value = Range("G" & Target.Row) * (1 - Range("I2").Value)
End Sub

The 1st part of the code is highlighted "If Range("G" & Target.Row) > 0", not sure why???? [/quote]
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Giggzz said:
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.....

Try:
Code:
Range("G9").Value = Range("G9").Value * (1 - Range("I2").Value)
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
that works but I need it to apply to range G9:G18.... im trying the following, but not working either
Code:
Private Sub CommandButton2_Click()
ActiveSheet.Unprotect ("national")
    Dim x As Integer
    x = I2
    If Range("G" & ActiveCell.Row) > 0 Then
        Range("G" & ActiveCell.Row) = Range("G" & ActiveCell.Row) * (1 - Range("I2").Value)
 ActiveSheet.Protect ("national")
    End If
End Sub
I thought this would be easy to do, but sometime the esiest things are the toughest.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

How about this?:

Code:
Private Sub CommandButton1_Click()
Dim cell As Range

    For Each cell In Range("G9:G18")
        cell.Value = cell.Value * (1 - Range("I2").Value)
    Next cell
End Sub

(That's a hardcoded range, though, you might do better to use Selection). Hope that helps!

EDIT: Oooops! Thanks, Barrie!
 

Giggzz

Well-known Member
Joined
Jul 4, 2002
Messages
990
Good evening...

tired the lastest and it does work but its putting "0" in all the empty cells in column G. Any thoughts on how to fix that? Appreciate all your help....
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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