Handling an array

[Otto Moehrbach]

Excel XP & Win XP
I need some help with an array problem.
I have an array of column numbers, say, 2,4,6,8,10. This array is fixed. I
want to run a FOR loop through this array. The variable is the point at
which I want to start. IOW, my target (or starting) column may be, say, 6.
I then want to run the FOR loop through columns 6,8,10 only.
My question is then: Given the starting column, how would I code a FOR loop
through this array? Thanks for your time. Otto

 

[meh2030]

Excel XP & Win XP
I need some help with an array problem.
I have an array of column numbers, say, 2,4,6,8,10.  This array is fixed.  I
want to run a FOR loop through this array.  The variable is the point at
which I want to start.  IOW, my target (or starting)  column may be, say, 6.
I then want to run the FOR loop through columns 6,8,10 only.
My question is then:  Given the starting column, how would I code a FORloop
through this array?  Thanks for your time.  Otto

Otto,

Let me know if this is along the lines of what you are looking for.
Keep in mind though that this requires a direct match between a value
in varMyAry and lngMyNum. Also, this assumes that the array is
sorted. (If you need to sort an array, simply search for this within
the group; there are plenty of examples out there).

Best,

Matt Herbert

Sub TestIt()

Dim lngA As Long
Dim varMyAry As Variant
Dim lngMyNum As Long

varMyAry = Array(2, 4, 6, 8, 10)
lngMyNum = 6

For lngA = LBound(varMyAry) To UBound(varMyAry)
If varMyAry(lngA) >= lngMyNum Then
MsgBox "The array number is: " & varMyAry(lngA)
End If
Next

End Sub

 

[Gary''s Student]

Sub otto()
s = Array(2, 4, 6, 8, 10)
realStart = 6
For i = LBound(s) To UBound(s)
If s(i) >= realStart Then
MsgBox (i & " " & s(i))
End If
Next
End Sub

This is an example of moving thru the array, but only doing something when a
value is reached.

 

[Otto Moehrbach]

Matt, Gary
Thanks for that. I have several arrays I must work with. They are all
sorted but some are ascending and some descending, but I can handle that
easily with the help you've given me. Thanks. Otto

Excel XP & Win XP
I need some help with an array problem.
I have an array of column numbers, say, 2,4,6,8,10. This array is fixed. I
want to run a FOR loop through this array. The variable is the point at
which I want to start. IOW, my target (or starting) column may be, say, 6.
I then want to run the FOR loop through columns 6,8,10 only.
My question is then: Given the starting column, how would I code a FOR
loop
through this array? Thanks for your time. Otto

Otto,

Let me know if this is along the lines of what you are looking for.
Keep in mind though that this requires a direct match between a value
in varMyAry and lngMyNum. Also, this assumes that the array is
sorted. (If you need to sort an array, simply search for this within
the group; there are plenty of examples out there).

Best,

Matt Herbert

Sub TestIt()

Dim lngA As Long
Dim varMyAry As Variant
Dim lngMyNum As Long

varMyAry = Array(2, 4, 6, 8, 10)
lngMyNum = 6

For lngA = LBound(varMyAry) To UBound(varMyAry)
If varMyAry(lngA) >= lngMyNum Then
MsgBox "The array number is: " & varMyAry(lngA)
End If
Next

End Sub

 

[Dave Peterson]

Another one:

Dim res As Variant 'could be an error
Dim myArr As Variant
Dim myStart As Long
Dim iCtr As Long

myArr = Array(2, 4, 6, 8, 10)
myStart = 6

res = Application.Match(myStart, myArr, 0)

If IsError(res) Then
'not a valid starting position!
'what should happen?
MsgBox "No match"
Else
For iCtr = LBound(myArr) + res - 1 To UBound(myArr)
MsgBox myArr(iCtr)
Next iCtr
End If

 

[Otto Moehrbach]

Thanks Dave. That's what I was looking for. I knew I had seen the indexing
of a value within an array before but I couldn't find it. Otto