Creating an dynamic array from 3 parameters

philou54520

New Member
Joined
May 25, 2018
Messages
8
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}

Thanks for your help!

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
Joined
Apr 25, 2018
Messages
13
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
Joined
May 25, 2018
Messages
8
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
Joined
May 28, 2005
Messages
50,498
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 25, 2018
Messages
13

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
50,498
Office Version
  1. 365
Platform
  1. Windows
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.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

For some reason I had missed your "No VBA" post, which has made mine redundant !!!
 
Last edited:

philou54520

New Member
Joined
May 25, 2018
Messages
8
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
Joined
May 25, 2018
Messages
8
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...

 

Forum statistics

Threads
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.
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
Top