How do I determine an Array's index?

[Roderick_E]

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

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:

if x.column() = 3  then....

<== that syntax is wrong I know.

 

[shg]

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

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

 

[Roderick_E]

Hmm Where am I defining which columns I'm working with? Wouldn't it be something like

set av = range("C:T")

 

[shg]

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.

 

[Roderick_E]

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:

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.

 

[shg]

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.

 

[Fluff]

Does this help

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

 

[Roderick_E]

Does this help

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?

 

[Fluff]

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)

 

[Roderick_E]

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:

If C = 7 Then 'or whatever index criteria I want
'do something
endif