Vba problem

[buddy2000]

When my program reaches the case statements I am having problems. It will not figure the discount. product price and tenpercent is showing the right figues when I step through the program, but they will not mutiply . Discount is remaining at zero.





Sub calculatePrices()

'Declare variables
Dim ProductId As String
Dim NproductId As String
Dim ProductPrice As String
Dim Quanity As String
Dim Descreption As String
Dim Discount As Single
Dim tenpercent As Single
tenpercent = 0.1
Dim fifteenpercent As Single
fifteenpercent = 0.15
Dim twentypercent As Single
twentypercent = 0.2
Dim thirtypercent As Single
thirtypercent = 0.3
Dim DiscountRate As Single


'User is prompted with an Input box
ProductId = InputBox(" Enter product Id", "ProductId")
If ProductId = "" Then
Exit Sub
End If
Quanity = InputBox("Enter the number of items you wish to puurchase", "Quanity")
If Quanity = "" Then
Exit Sub
End If
With Range("A1")
'Number of rows is counted
NproductId = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
'Vlook up function is used to find Description of part
Sheets("Prices").Activate
Descreption = Application.VLookup(ProductId, Range("Pricelist"), 2, False)
'Pass productId value to productprice
'Vlook up funciont to get part price
ProductPrice = Application.VLookup(ProductId, Range("Pricelist"), 3, False)
'figure discount rate to give to customer
Select Case CSng(Quanity)
Case CSng(Quanity) > 25
DiscountRate = tenpercent
Discount = CSng(ProductPrice) * tenpercent
Case CSng(Quanity) > 25 < 50
DiscountRate = fifteenpercent
Discount = CSng(ProductPrice) * fifteenpercent
Case CSng(Quanity) > 50 < 75
DiscountRate = twentypercent
Discount = CSng(ProductPrice) * twentypercent
Case CSng(Quanity) >= 100
DiscountRate = thirtypercent
Discount = CSng(ProductPrice) * thirtypercent
End Select

'Display information on sales sheet
Worksheets("Sales").Range("B1").Value = ProductId
Worksheets("Sales").Range("B3").Value = Quanity
Worksheets("Sales").Range("B2").Value = Descreption
Worksheets("Sales").Range("B5").Value = ProductPrice
Worksheets("Sales").Range("B7").Value = Discount
Worksheets("Sales").Range("B7").Value = DiscountRate
End With
End Sub

[mdmackillop]

Can you post a workbook with sample data?

[mdmackillop]

Your Select Case misses = values and your first should read <25
Select Case CSng(Quanity)
Case CSng(Quanity) < 25
DiscountRate = tenpercent
Discount = CSng(ProductPrice) * tenpercent
Case CSng(Quanity) >= 25 < 50
DiscountRate = fifteenpercent
Discount = CSng(ProductPrice) * fifteenpercent
Case CSng(Quanity) >= 50 < 75
DiscountRate = twentypercent
Discount = CSng(ProductPrice) * twentypercent
Case CSng(Quanity) >= 75 < 100
DiscountRate = ?????????
Discount = CSng(ProductPrice) * ???????????
Case CSng(Quanity) >= 100
DiscountRate = thirtypercent
Discount = CSng(ProductPrice) * thirtypercent
End Select

[buddy2000]

Thank you,

MdMcillop

The case missing the = sign is what the problem was. It works perfect now. Thank you so much it amazes me how something so small can cause a program not to work.