Help with VBA code for price break/discount quote form

xbxh

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
So basically what this code does is calculates the discount by category when we quote somebody. Right now when I click the button it will calculate all the price discounts based on the category and entered list price. There are 11 discounts in each category depending on qty purchased.

When I run this macro it places all 11 price breaks on the quote. I am looking to add a function where the user can choose how many price breaks to display. So if they want 3 to display on the quote they enter 3 and when they click the button only three price breaks copy over to the quote 1 instead of all 11 which is happening now. I tried a few things but cant get it to work. Any ideas would be very much appreciated!

Example of price breaks and quote tab.PNG

VBA Code:
Sub CalcPrices()
Dim Quantity(40) As String
Dim Price(40) As Single
Dim LengthCol, PriceCol, IncreaseCol, Row, n As Integer
Dim ListPrice As Single

    Sheets("Lists").Activate
    
        ListPrice = Range("I17").Value

    Range("PreviewListPrice") = ListPrice
    Worksheets("Quote 1").Range("QuoteListPrice") = Range("PreviewListPrice")
    
    'Load Quantity Brackets and corresponding discounts
    Dim DiscountCol As Integer
    If Range("InputDiscountCatagory") = "International OEM (3000)" Then
        DiscountCol = 2
    ElseIf Range("InputDiscountCatagory") = "Auth. Domestic Non-Stocking Dist. (4000)" Then
        DiscountCol = 3
    ElseIf Range("InputDiscountCatagory") = "Domestic OEM (5000)" Then
        DiscountCol = 4
    ElseIf Range("InputDiscountCatagory") = "Auth. Domestic Stocking Dist. (6000)" Then
        DiscountCol = 5
    ElseIf Range("InputDiscountCatagory") = "User (7000)" Then
        DiscountCol = 6
    ElseIf Range("InputDiscountCatagory") = "Non-Stocking Dist. (8000)" Then
        DiscountCol = 7
    ElseIf Range("InputDiscountCatagory") = "International Rep/Dist (9000)" Then
        DiscountCol = 8
    End If
    
    Dim BracketCol As Integer
    Dim Bracket(40) As String
    Dim Discount(40) As Single
    BracketCol = 1
    Row = 2
    n = 0
    Do While ActiveSheet.Cells(Row, BracketCol) <> "&END&"
        Bracket(n) = ActiveSheet.Cells(Row, BracketCol)
        Discount(n) = ActiveSheet.Cells(Row, DiscountCol)
        Row = Row + 1
        n = n + 1
    Loop
    Bracket(n) = "&END&"
    
    ' Calculate Net Prices
    Dim NetPrice(15) As Single
    Dim Disc As Single
    n = 0
    Do While Bracket(n) <> "&END&"
        Disc = Discount(n) / 100
        NetPrice(n) = ListPrice * (1 - Disc)
        n = n + 1
    Loop
    
    
    'Display brackets and net prices
    'Dim PriceCol As Integer
    Sheets("Quote 1").Activate
    Row = 17
    BracketCol = 10
    PriceCol = 11
    n = 0
     Do While Bracket(n) <> "&END&" 'And n + 1 < 12
        Worksheets("Quote 1").Range("QuoteQuantityBrackets")(n + 1) = Bracket(n)
        Worksheets("Quote 1").Range("QuoteNetPrices")(n + 1) = NetPrice(n)
               
        'ActiveSheet.Cells(Row, BracketCol) = Bracket(n)
        'ActiveSheet.Cells(Row, PriceCol) = NetPrice(n)
        'Row = Row + 1
        n = n + 1
    Loop
    
Out:
        
End Sub
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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