# Creating an dynamic array from 3 parameters

#### philou54520

##### New Member
Hi all,

I am really a beginner and a little bit ashamed because I could not find after extensive search how to generate a simple array (One dimension) but depending on 3 parameters. I would like to create a serial dilution array where the inputs are: Concentration of stock solution, dilutional factor and number of dilutions. Easy right? I can't manage to do this.

For example:
Stock solution concentration: 5000
Dilutional factor: 2
Number of dilutions: 7

Should generate an array like this:
{5000, 2500, 1250, 625, 312.5, 156.25, 78.125, 39.0625}

Phil

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Haluk_

##### Rules violation
Maybe this;

Code:
``````Sub Test()
Dim myArr(1 To 7)
a = 5000
b = 2
c = 7

temp = 5000

myArr(1) = a

For i = 2 To c
temp = temp / b
myArr(i) = temp
Next

'Test for the 3rd value of the array
MsgBox myArr(3)
End Sub``````

#### philou54520

##### New Member
Thanks a lot! But I forgot to mention that I would rather not use VBA. Do you think it is possible only with excel functions? Such as Index, Indirect, row something like this?

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Edit: Sorry, hadn't seen the last 2 posts.

The following would produce a vba array (ary) holding the values you want. Is that what you are after?
Code:
``````Sub CreateArray()
Dim ary As Variant
Dim i As Long

Const Concentation As Long = 5000
Const Dilution As Long = 2
Const NumDilutions As Long = 7

ReDim ary(1 To NumDilutions + 1)
For i = 0 To NumDilutions
ary(i + 1) = Concentation / (Dilution ^ i)
Next i
End Sub``````

Last edited:

#### Haluk_

##### Rules violation

Ignore the previous code and try this;

Code:
``````Sub Test()
Dim myArr(1 To 8)
a = 5000
b = 2
c = 7

temp = a

myArr(1) = a

For i = 2 To c + 1
temp = temp / b
myArr(i) = temp
Next

'Test for the 3rd value of the array
MsgBox myArr(8)
End Sub``````

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks a lot! But I forgot to mention that I would rather not use VBA. Do you think it is possible only with excel functions? Such as Index, Indirect, row something like this?
What are you going to do with that array?
It would be helpful to know more about what you have, where and what you are trying to achieve, and where.

Last edited:

#### philou54520

##### New Member
Basically it is just for generating a template for ELISA tests (which are used in biology). I would like the members of my lab to use the same EXCEL templates they just have to enter the stock solution and not doing inefficient repetitive tasks

#### philou54520

##### New Member
Better without VBA coding if possible

#### philou54520

##### New Member
Perhaps this:-
With you numerical data in "B1 to B3" try this for results starting "D1".
Code:
``````[COLOR=Navy]Sub[/COLOR] MG25May55
[COLOR=Navy]Dim[/COLOR] Conc  [COLOR=Navy]As[/COLOR] Double, Fac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Dil [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Conc = Range("B1"): Fac = Range("B2"): Dil = Range("B3")
ReDim Ray(1 To Dil + 1)
Ray(1) = Conc
[COLOR=Navy]For[/COLOR] n = 2 To Dil + 1
Conc = Conc / Fac
Ray(n) = Conc
[COLOR=Navy]Next[/COLOR] n
Range("D1").Resize(Dil + 1) = Application.Transpose(Ray)
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]``````
Regards Mick

Better without VBA coding if possible. But maybe it is not possible to loop without VBA...

Replies
4
Views
802

1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

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