Using loop to output compounded values

Using loop to output compounded values

Hope someone can help with this.

I have a worksheet with the following structure:

........................Prod A.......Prod B.......Prod C
Basic Rate 1........5.5............6..............6.5
Basic Rate 2........NA.............6.3...........6.8
Basic Rate 3........6.1............6.6............7.1
Basic Rate 4........6.4............6.9............7.4
Option 1..............0.5...........NA.............0.2
Option 2..............0.2...........0.3............0.2
Option 3..............NA...........NA.............0.2
Option 4..............NA...........0.6.............NA

What I need is something that will loop through the cells in the table and show all possible valid combinations (i.e. no cell in the calculation contained NA), along with the rate (including any compounded values that have an option loading aplied to them) and return the output like this:

Prod A Basic Rate 1...........................................................5.5
Prod A Basic Rate 1 & Option 1............................................6
Prod A Basic Rate 1 & Option 1 & Option 2.............................6.2
Prod A Basic Rate 1 & Option 2............................................5.7

There should be no output for Options 3 and 4 as they are NA. The loop would continue down each row and output data as applicable, i.e. there would be no entries for Prod A Basic Rate 2 as it is NA. After completing the loop for each of the Basic Rate n rows it should move onto the next column and do the same. This would result in:

Prod B Basic Rate 1...........................................................6
Prod B Basic Rate 1 & Option 2............................................6.3
Prod B Basic Rate 1 & Option 2 & Option 4.............................6.9
Prod B Basic Rate 1 & Option 4............................................6.6

I've been trying to work my way through this but without a great deal of success. I'm relatively new to VBA and this has caused me days of quiet frustration! Any help that anyone can offer would be greatly appreciated. If this is not possible please let me know!

Thanks

Derek

Derek

How do you determine the break between the "basic rate" and the "option" entries? Or do you want it to work with exactly this format / setup?


rylo

Hi Rylo,

Thanks for replying. I have 4 cells on the worksheet that will control this. Basic Rate Start Row, Basic Rate End Row, Option Rate Start Row and Option Rate End Row. There are also two cells set aside to handle the start and end columns.

Any help that could be offered would be greatly appreciated.

Thanks

Derek

Derek

Have a look at the attached file.

I've had to make a couple of guesses about the position of your variables so hopefully you can see what I've done, and will be able to adapt to your structure.

HTH

rylo

Hi rylo,

Thanks. This is exactly what I need. Just couldn't get my head around the internal loops, but I see where I was going wrong.

Thanks again!

Derek

Rylo I am a little confused with this part of your code;
brsr = Range("H2").Value
brer = Range("I2").Value
orsr = Range("J2").Value
orer = Range("K2").Value
sc = Range("L2").Value
ec = Range("M2").Value

How is it that you don't seem to have to declared these variables ?

also in your looping you dont declare 'opt1' or 'opt2'

yet the code executes

never mind I didn't notice that you didn't have option explicit

Hi rylo,

I've been playing about with the code and plugging in different values and wonder if you could help me out of an issue that I've ran into. If all of the Options are filled in like so:

........................Prod A
Basic Rate 1........5.5
Basic Rate 2........5.8
Basic Rate 3........6.1
Basic Rate 4........6.4
Option 1..............0.5
Option 2..............0.2
Option 3..............0.6
Option 4..............1

and I need the output to give all possible combinations, i.e.:

Prod A Basic Rate 1..................................................................5.5
Prod A Basic Rate 1 & Option 1...................................................6
Prod A Basic Rate 1 & Option 1 & Option 2....................................6.2
Prod A Basic Rate 1 & Option 1 & Option 2 & Option 3.....................6.8
Prod A Basic Rate 1 & Option 1 & Option 2 & Option 3 & Option 4......7.8
Prod A Basic Rate 1 & Option 1 & Option 3....................................6.6
Prod A Basic Rate 1 & Option 1 & Option 3 & Option 4.....................7.6
Prod A Basic Rate 1 & Option 1 & Option 4....................................7.0
Prod A Basic Rate 1 & Option 2...................................................5.7
Prod A Basic Rate 1 & Option 2 & Option 3....................................6.3
Prod A Basic Rate 1 & Option 2 & Option 3 & Option 4.....................7.3
Prod A Basic Rate 1 & Option 2 & Option 4....................................6.7
Prod A Basic Rate 1 & Option 3...................................................6.1
Prod A Basic Rate 1 & Option 3 & Option 4....................................7.1
Prod A Basic Rate 1 & Option 4...................................................6.5

The lines in bold are the ones that I am really struggling with. Any idea how I could amend the code to get it to do this?

Thanks again for all your help.

Derek

Derek

Been mulling on this and can't seem to get an algorithm. If the number of options is always set at 4, then 4 nested loops would get you there. However, to do that you need to know it will always be 4 loops, and fix the code accordingly.

Hopefully someone else may be able to come up with a scalable solution.


rylo