Replicate cascaded calculation in VBA

[Laurelb]

Dear All,

I hope you are all well,

I need some help

How to replicate the calculation below in VBA?

initial value 10.000.000

10.000.000 * (1-2%) = 9.800.000

9.800.000 * (1-4%) = 9.408.000

here is my code:

Please Login or Register  to view this content.

here is my file too cascaded.xlsm

thanks in advance,

Laurel

[Marc L]

Sub Cascaded() 
    Dim BASE#, R&, RES# 
    Application.ScreenUpdating = False 
 
    With Sheet1 
        BASE = .[D9].Value 
 
        With .Range("C11", .Cells(.Rows.Count, 3).End(xlUp)(1, 7)).Rows 
             .AutoFilter 1, "Frozen":  .AutoFilter 2, "Ristorante":  .AutoFilter 5, "Yes" 
 
            For R = 2 To .Count 
                With .Item(R) 
                    If Not .Hidden Then 
                                                         RES = BASE * (1 - .Cells(7).Value) 
                        If .Cells(6).Value = "Yes" Then BASE = RES 
                    End If 
                End With 
            Next 
 
             .AutoFilter 
        End With 
    End With 
 
    Application.ScreenUpdating = True 
    MsgBox Format(RES, "#,##0"), , "   Result :" 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

[Laurelb]

Hello Marc,

Thanks for you quick reply, and sorry for my late replay, but have been traveling and had no time to test the code till now,

you code work, thanks for that, when I firstly create this post I thought that with some replies i would be able to develop the rest of the code, but.... no luck, tried almost a million times and I come up with the same result, failure

Maybe you can help me on this,

On the enclosed file sheet 1, you will find a table, which will have several categories and several subcategories, also several conditions, which can be on-invoice and off-invoice, cascaded or non-cascaded.

In this table will contain the conditions for a specific customer, these conditions can be different from categories to categories, and inside the same category it can have a specific condition for a sub-category. The conditions might be in % or value, depending on the condition type, the calculation should have this in mind when it goes through.

On sheet 2 I have a table with a list of products, existing in that customer and the respective gross price, this table also contains "category", "sub-category", for filter purposes.

In this table I have a few columns, which will have the results of such calculations, like for example column F (Promo discounts), column G (Non promo discounts), and column H (Net invoice), these three columns will show me the total of the conditions on-invoice of the table in sheet 1.

your code works good, thanks for that, but it seems that for what I need, I can´t make it work, when I try to continue to develop the calculation, I crash and burn

Enclosed you find the latest file, which has already some more things than the first file.


I also included a page showing what should be the result of the calculation, maybe with this you can check and see if there is any chance of doing this.

I just need an example that works with all these variables, then I will do the rest for the remaining columns.

Here is the code I´ve been working

Please Login or Register  to view this content.

and here is the latest file cascaded.xlsm

Thanks a lot in advance,

Lauren

[Marc L]

Your initial code has just a lack of logic …

Now for your amended need, I can't visualize it.
Write every rule to understand what you need to calculate …
For further help, try to detail each calculation and result.