Results 1 to 10 of 10

Thread: Get array index for selected item
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,566
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Get array index for selected item

    Code:
    Sub TestIndex()
    Dim arr, List$, i%
    
    List = "10,12,14,16,18"
    
    arr  = Split(List, ",")
    
    For i  = 10 To 18 Step 2
           Select Case i 
                      Case LBound(arr) To UBound(arr)
                       MsgBox arr 'Here, I want msgbox show say 0 to 4
           End Select
    Next i 
    End Sub
    So when the i reach 10, I want the msgbox show 0. In that other.

    I don't know what to do to get that. Can someone help?
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get array index for selected item

    HI
    the method of select For i = 10 To 18 Step 2 Select Case i
    Code:
     Select Case i 
                     Case arr(1)
                       MsgBox "0"
           End Select
    Last edited by mohadin; Sep 23rd, 2019 at 07:14 AM.

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

    Default Re: Get array index for selected item

    It is very unclear what you are actually trying to do - or why.

    In any case, if List = "10,12,10, 16,18", what result would you expect for "10" - 0 or 2?
    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,227
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Get array index for selected item

    As Peter has said, it's unclear what you are trying to do, but with your example maybe
    Code:
    Sub TestIndex()
    Dim arr, List$, i%
    
    List = "10,12,14,16,18"
    
    arr = Split(List, ",")
    
    For i = 10 To 18 Step 2
          MsgBox Application.Match(CStr(i), arr, 0) - 1
    Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Get array index for selected item

    Quote Originally Posted by mohadin View Post
    HI
    the method of select For i = 10 To 18 Step 2 Select Case i
    Code:
     Select Case i 
                     Case arr(1)
                       MsgBox "0"
           End Select
    Code:
    Sub TestIndex()
    Dim arr, List$, i%
    
    List = "10,12,14,16,18"
    
    arr  = Split(List, ",")
    
    For i  = 10 To 18 Step 2
           Select Case i 
                      Case arr(LBound(arr)) To arr(UBound(arr))
                       MsgBox arr 'Here, I want msgbox show say 0 to 4
           End Select
    Next i 
    End Sub
    Okay but I want a way to loop instead of writing many case statements. There was an error with my first code.
    There Is Always A Better Way

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

    Default Re: Get array index for selected item

    Quote Originally Posted by Fluff View Post
    As Peter has said, it's unclear what you are trying to do, but with your example maybe
    Code:
    Sub TestIndex()
    Dim arr, List$, i%
    
    List = "10,12,14,16,18"
    
    arr = Split(List, ",")
    
    For i = 10 To 18 Step 2
          MsgBox Application.Match(CStr(i), arr, 0) - 1
    Next i
    End Sub

    Yeah!!!

    You nailed it!!!!!!

    This is what I want to get!!!!!

    I appreciate that
    There Is Always A Better Way

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,227
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Get array index for selected item

    Glad we could help & thanks for the feedback.

    However if you have the same value more than once in the list, it will report the first match.
    Last edited by Fluff; Sep 23rd, 2019 at 08:14 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Get array index for selected item

    Quote Originally Posted by Peter_SSs View Post
    It is very unclear what you are actually trying to do - or why.

    In any case, if List = "10,12,10, 16,18", what result would you expect for "10" - 0 or 2?
    I think you overlooked the list.

    There is only one 10 in the list. By the way Fluff's code has taken care of the issue.
    There Is Always A Better Way

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

    Default Re: Get array index for selected item

    Quote Originally Posted by kelly mort View Post
    I think you overlooked the list
    No, I didn't overlook the list. I was asking what you would want to happen if the list was different and had repeated values.
    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

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

    Default Re: Get array index for selected item

    Quote Originally Posted by Peter_SSs View Post
    No, I didn't overlook the list. I was asking what you would want to happen if the list was different and had repeated values.
    Oh okay. Nice point over there. But that will not happen in the current universe I am living in.
    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
  •