# Iterating through different values of variables

#### abberyfarm

##### Well-known Member
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Norie

##### Well-known Member
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?

#### Weaver

##### Well-known Member
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

#### abberyfarm

##### Well-known Member
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

#### Weaver

##### Well-known Member
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``````

#### abberyfarm

##### Well-known Member
Thanks very much. That's great works perfect.

Replies
5
Views
237
Replies
3
Views
198
Replies
1
Views
232
Replies
2
Views
269
Replies
7
Views
223

1,196,027
Messages
6,012,947
Members
441,740
Latest member
abaz21

### 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.

### Which adblocker are you using?

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

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