Creating an dynamic array from 3 parameters

[philou54520]

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

 

[Haluk_]

Maybe this;

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]

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]

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?

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

 

[Haluk_]

Ignore the previous code and try this;

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]

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]

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

 

[philou54520]

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]

Better without VBA coding if possible

 

[philou54520]

Perhaps this:-
With you numerical data in "B1 to B3" try this for results starting "D1".

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