Handling an array
 

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

Handling an array
 

On Mar 7, 1:47*pm, "Otto Moehrbach"
wrote:
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

Handling an array
 

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.
--
Gary''s Student - gsnu2007L


"Otto Moehrbach" wrote:

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

Handling an array
 

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
wrote in message
...
On Mar 7, 1:47 pm, "Otto Moehrbach"
wrote:
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

Handling an array
 

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 wrote:

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

--

Dave Peterson

Handling an array
 

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
"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson