How do I determine an Array's index?

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:
Upvote 0
Hmm Where am I defining which columns I'm working with? Wouldn't it be something like
Code:
set av = range("C:T")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top