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