EXCEL VBA for permutation and combinations calculations

Question

I am completely new to Excel VBA, and I'm trying to write code to do a simple task.

There are 3 Worksheets in an Excel: Summary, Discount, Price

Worksheet Summary contains data as below:

Dropdown (Data in B1 and B3):

Type: Supermarket,Hopcoms
Product:Dairy,Meat,Combo
Category:Chicken,Eggs,Eggs+Chicken

Worksheet Discount contains data as below:

Worksheet Price contains data as below:

Tasks:

1. User in Summary Sheet selects Data in B1:B3 through drop down list.
2. Based on the selection , Discount in % should be selected for corresponding combination from Discount Sheet For Ex : If user selects Supermarket-Combo-Eggs+Chicken in Summary Sheet(from #1) , total discount availed should be 13%(as per Discount Sheet).
3. User navigates to Price sheet and enters data in Total Price column for each customer
4. When User clicks Calculate Discount button now 13% discount should be applied on Total Price for all customer and discount amount should be updated in Discount Availed column
5. Total Discount price provided to all customers should be updated next to T.Discount.

Apologies for any mistakes, could anyone please resolve this? Please let me know for any questions

Answer 1

Test the next code, please. But before that, please insert another column in Price worksheet, between existing "Customer" (B:B) and "Total Price" (C:C) and name it "Price". There the user will fill the standard price (without any discount) and in "Total Price" the the price will be calculated applying the discount:

Sub TestApplyDiscount()
 Dim shS As Worksheet, shD As Worksheet, shP As Worksheet, lastRowP As Long
 Dim strType As String, strProd As String, strCat As String, i As Long, j As Long
 Dim rowType As Long, rowProd As Long, rowCat As Long, disc As Double
 
 Set shS = Worksheets("Summary")
 Set shD = Worksheets("Discount")
 Set shP = Worksheets("Price")
 lastRowP = shP.Range("A" & Rows.count).End(xlUp).Row
 
 strType = shS.Range("B1").value
 strProd = shS.Range("B2").value
 strCat = shS.Range("B3").value
 
 With WorksheetFunction
       rowType = .Match(strType, shD.Range("A1:A19"), 0)

        rowProd = .Match(strProd, shD.Range("B" & rowType & ":B" & rowType + 8), 0)
        rowProd = rowProd + rowType - 1
        
         rowCat = .Match(strCat, shD.Range("C" & rowProd & ":C" & rowProd + 3), 0)
         rowCat = rowCat + rowProd - 1
         
         disc = shD.Range("D" & rowCat).value
 End With
 For i = 2 To lastRowP
    shP.Range("E" & i).value = disc & "%"
    shP.Range("D" & i).FormulaR1C1 = "=RC[-1]-(RC[-1]*RC[1])"
 Next i
End Sub

I am waiting for some feedback...