Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How do I determine an Array's index?

  1. #1
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default How do I determine an Array's index?

    I'm toying around with using arrays instead of working in ranges.
    Here is a simple array

    Code:
    Sub dotest()
    Dim DirArray As Variant
    starttime = Timer
    DirArray = Range("c:t").Value
    For Each x In DirArray
    'Debug.Print x
    Next
    MsgBox Round(Timer - starttime, 2)
    End Sub
    While I want to load the entire c:t, what if I wanted a conditional statement in the FOR loop such as:

    Code:
    if x.column() = 3  then....
    <== that syntax is wrong I know.
    for more go to rodericke.com/xlsuper

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How do I determine an Array's index?

    Be easier (and faster) to iterate through the rows and columns directly, and avoid the coercion to object and back to scalar:

    Code:
    Sub Rod()
      Dim av            As Variant
      Dim fSec          As Single
      Dim i             As Long     ' row
      Dim j             As Long     ' col
    
      av = Range("C:T").Value2
    
      fSec = Timer
      For i = 1 To UBound(av, 1)
        For j = 1 To UBound(av, 2)
          Debug.Print av(i, j)
        Next j
      Next i
    
      MsgBox Round(Timer - fSec, 2)
    End Sub
    Last edited by shg; May 21st, 2018 at 01:46 PM.

  3. #3
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do I determine an Array's index?

    Hmm Where am I defining which columns I'm working with? Wouldn't it be something like
    Code:
    set av = range("C:T")
    for more go to rodericke.com/xlsuper

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How do I determine an Array's index?

    I don't follow that ...

    Once you initialize the array to that range's contents, there is nothing stored that tells you where the contents came from.

    A little explanation of what you're trying to do might help.

  5. #5
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do I determine an Array's index?

    Quote Originally Posted by shg View Post
    I don't follow that ...

    Once you initialize the array to that range's contents, there is nothing stored that tells you where the contents came from.

    A little explanation of what you're trying to do might help.
    I see you edited and added:
    Code:
    av = range("C:T").value2
    I'll have to look up value2 not certain what that is different than value

    Executing your code never reached the msgbox after 2 minutes. Should we pre-determine last populated row?

    WHAT I'M TRYING TO DO
    Right now I'm just testing if looping through a sheet's range or an array is faster. Some tests I did shows looping through an array is 4 times faster. However, I'll need to be able to determine how to handle conditions on the array and then how to write back modifications to the sheet depending on those conditions. Thus I was trying to determine the index/row,column position of the array.
    for more go to rodericke.com/xlsuper

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,985
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How do I determine an Array's index?

    I'll have to look up value2 not certain what that is different than value
    Value converts currency-formatted values to data type Currency and date-formatted values to data type Date. Value2 returns raw values (i.e., all numbers as Double)

    Executing your code never reached the msgbox after 2 minutes. Should we pre-determine last populated row?
    Writing 18M lines to the Immediate window will take a bit of time, so I'd say yes.

    Right now I'm just testing if looping through a sheet's range or an array is faster. Some tests I did shows looping through an array is 4 times faster.
    That's settled fact; it's hundreds of times faster.

    However, I'll need to be able to determine how to handle conditions on the array and then how to write back modifications to the sheet depending on those conditions. Thus I was trying to determine the index/row,column position of the array.
    Still don't know what that means.
    Last edited by shg; May 21st, 2018 at 02:31 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: How do I determine an Array's index?

    Does this help
    Code:
    Sub chk()
    Dim ary As Variant
    Dim R As Long, C As Long
    
    ary = Range("C1:T100")
    For C = 1 To UBound(ary, 2)
       For R = 1 To UBound(ary, 1)
          Debug.Print ary(R, C), Cells(R, C + 2)
       Next R
    Next C
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do I determine an Array's index?

    Quote Originally Posted by Fluff View Post
    Does this help
    Code:
    Sub chk()
    Dim ary As Variant
    Dim R As Long, C As Long
    
    ary = Range("C1:T100")
    For C = 1 To UBound(ary, 2)
       For R = 1 To UBound(ary, 1)
          Debug.Print ary(R, C), Cells(R, C + 2)
       Next R
    Next C
    End Sub
    I think this is getting me closer. I need to understand how Unbound(ary, 2) and Unbound(ary,1) works. I understand Unbound(ary) but not how the
    Also not certain why you're debug.print ary(R,C) and then the next column next to it? Just for visual format?
    for more go to rodericke.com/xlsuper

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: How do I determine an Array's index?

    ary(R,C) is reading from the array, whereas Cells(R, C + 2) is reading from the sheet & should show you that they're the same value.

    Ubound(ary,1) is the first dimension of the ary (i think of it as the number of rows), whilst Ubound(ary,2) is the second dimension (or number of columns)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    2,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do I determine an Array's index?

    Quote Originally Posted by Fluff View Post
    ary(R,C) is reading from the array, whereas Cells(R, C + 2) is reading from the sheet & should show you that they're the same value.

    Ubound(ary,1) is the first dimension of the ary (i think of it as the number of rows), whilst Ubound(ary,2) is the second dimension (or number of columns)
    Perfect. Thanks shg for initial help and Fluff for carrying it over the line and for using the word "whilst"

    I needed to be able to add:

    Code:
    If C = 7 Then 'or whatever index criteria I want
    'do something
    endif
    for more go to rodericke.com/xlsuper

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
  •