Results 1 to 6 of 6

Thread: Issue getting the value from an array instead of index
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Issue getting the value from an array instead of index

    Code:
    Sub GetOutPut()
    
    Dim V1, V2, V3, V4, V5
    Dim TheList 
    Dim output
    
    
    V1 = "This One"
    V2 = "The Boy"
    V3 = "That One"
    V4 = "Car Race"
    V5 = "Cash Money"
    
    TheList = Array(V1, V2, V3, V4, V5)
    
    Output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))
    
    MsgBox TheList(output)
    
    End Sub
    My aim is to pull randomly from the list meanwhile its showing me the list index instead. How do I get the value instead. Say

    "The Boy" instead of 1?
    Last edited by kelly mort; Sep 23rd, 2019 at 02:25 PM. Reason: I just figured it out
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue getting the value from an array instead of index

    Like this?

    Code:
    Sub GetOutPut()
    
    
    Dim V1, V2, V3, V4, V5
    Dim TheList
    Dim output
    
    V1 = "This One"
    V2 = "The Boy"
    V3 = "That One"
    V4 = "Car Race"
    V5 = "Cash Money"
    
    
    TheList = Array(V1, V2, V3, V4, V5)
    
    
    output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))
    
    
    MsgBox TheList(output)
    
    
    End Sub
    Last edited by Finalfight40; Sep 23rd, 2019 at 02:27 PM.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  3. #3
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue getting the value from an array instead of index

    Quote Originally Posted by Finalfight40 View Post
    Like this?

    Code:
    Sub GetOutPut()
    
    
    Dim V1, V2, V3, V4, V5
    Dim TheList
    Dim output
    
    V1 = "This One"
    V2 = "The Boy"
    V3 = "That One"
    V4 = "Car Race"
    V5 = "Cash Money"
    
    
    TheList = Array(V1, V2, V3, V4, V5)
    
    
    output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))
    
    
    MsgBox TheList(output)
    
    
    End Sub

    Yes. I just figured it out few seconds after the post. Have been on this for the past 1 hour.
    There Is Always A Better Way

  4. #4
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue getting the value from an array instead of index

    It's always the way haha.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,378
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Issue getting the value from an array instead of index

    Just a comment: vba has its own random number generation facility so there is really no need to revert to the worksheet one
    Code:
    output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))
    output = LBound(TheList) + Int(Rnd() * (UBound(TheList) + 1))
    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 # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Issue getting the value from an array instead of index

    Quote Originally Posted by Peter_SSs View Post
    Just a comment: vba has its own random number generation facility so there is really no need to revert to the worksheet one
    Code:
    output = Application.WorksheetFunction.RandBetween(LBound(TheList), UBound(TheList))
    output = LBound(TheList) + Int(Rnd() * (UBound(TheList) + 1))

    Okay. I will amend that ASAP
    There Is Always A Better Way

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
  •