Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Creating an dynamic array from 3 parameters

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating an dynamic array from 3 parameters

    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

  2. #2
    Rules violation
    Join Date
    Apr 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    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

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    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?

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,389
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    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 by Peter_SSs; May 25th, 2018 at 06:49 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Rules violation
    Join Date
    Apr 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    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

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,389
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    Quote Originally Posted by philou54520 View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,032
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    For some reason I had missed your "No VBA" post, which has made mine redundant !!!
    Last edited by MickG; May 25th, 2018 at 06:55 AM.

  8. #8
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    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

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    Better without VBA coding if possible

  10. #10
    New Member
    Join Date
    May 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating an dynamic array from 3 parameters

    Quote Originally Posted by MickG View Post
    Perhaps this:-
    With you numerical data in "B1 to B3" try this for results starting "D1".
    Code:
    Sub MG25May55
    Dim Conc  As Double, Fac As Long, Dil As Long, n As Long
    Conc = Range("B1"): Fac = Range("B2"): Dil = Range("B3")
    ReDim Ray(1 To Dil + 1)
    Ray(1) = Conc
    For n = 2 To Dil + 1
        Conc = Conc / Fac
        Ray(n) = Conc
    Next n
    Range("D1").Resize(Dil + 1) = Application.Transpose(Ray)
    End Sub
    Regards Mick
    Better without VBA coding if possible. But maybe it is not possible to loop without VBA...


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •