Iterating through different values of variables

abberyfarm

Well-known Member
Joined
Aug 14, 2011
Messages
733
Hello,

I have four variables a1, a2, cr and s in the macro below.

Code:
Public Const cr As Long = 4
Sub Mode5()
      
    '4 are the constants you can vary to test different scenarios
     Const a1 As Single = 0.1   
     Const a2 As Single = 0.4
     Const s As Single = 3

It is easier if think of these variables in this format (a1, b1, cr, s).

So taking the code above as an example they can be written as (0.1, 0.4, 4, 3).

Ultimately I would like to try different combinations of these values to get a desired outcome. Currently I change the values manually each time.

Variable a can range from 0.1 - 2.5 (25 numbers)
Variable b can range from 0.1 - 2.5 (25 numbers)
Variable s can range from 1 - 10 (10 numbers)
Variable s can range from 1 -10 (10 numbers)

I would be grateful if somebody could help me write some code to iterate through different combinations of these variables. I presume it would be some sort of loop which increments each variable a certain amount each time until the max value is reached.

For example the first combination would be (0.1, 0.1, 1, 0.1) and the next would be (0.2, 0.1, 1, 0.1) and the next (0.3, 0.1, 1, 0.1) etc and the last one would be (2.5, 2.5, 10, 10).

Altogether there can be 25 x 25 x 10 x 10 = 62,500 combinations.

I hope I have explained this well enough for you to understand, if not please don't hesitate in asking questions.

Thank you for the help.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You could try declaring them as variables instead of constants.

The create loops to go through the possible values.

The first look might look something like this:
Code:
For a = 0.1 To 2.5 Step 0.1

Got stuck there though, yu'll need to clarify the rest of the variables.:)

PS Can you post what you are going to do with these values?
 
Upvote 0
Nested for..next loops

Code:
for a1 = 0.1 to 2.55 step 0.1
  for a2 = 0.1 to 2.55 step 0.1
    for cr = 1 to 10
      for s = 1 to 10
        'do the thing you need to do here
      next s
    next cr
  next a2
next a1
Note the upper limit is 2.55 on the step 0.1 loops to allow for inaccurate decimal numbering
try
Code:
for a1 = 0.1 to 2.5 step 0.1:debug.Print a1:next
in the immediate window and you may find the last number printed is 2.4
 
Upvote 0
Thank you,

I'm curious, will this get all the combinations?

It looks to me that this will start off at (0.1, 0.1, 1, 1) and the next iteration will be (0.2, 0.2, 2, 2) and then (0.3, 0.3, 3, 3) etc? Maybe I'm wrong?

I thought it might go (0.1, 0.1, 1, 1) then (0.2, 0.1, 1, 1) eventually going through 62,500 combinations?

Thank you
 
Upvote 0
If you need convincing, run this with a blank tab activated

Code:
Sub test()
    Range("A1:E1") = Array("#", "a1", "a2", "cr", "s")
    r = 2
    Application.ScreenUpdating = False
    For a1 = 0.1 To 2.55 Step 0.1
        For a2 = 0.1 To 2.55 Step 0.1
            For cr = 1 To 10
                For s = 1 To 10
                    Cells(r, 1) = r - 1
                    Cells(r, 2) = a1
                    Cells(r, 3) = a2
                    Cells(r, 4) = cr
                    Cells(r, 5) = s
                    r = r + 1
                Next s
            Next cr
        Next a2
    Next a1
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top