I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Where is the Redim statement Arne? I would have expected to see it within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number
of
iterations in the loop. My problem is that I ReDim the array using the
number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Um, the ReDim statement is the second line of her code.. ;)

Also note typo:
Redim Preserver ary (r)
should be
Redim Preserve ary (r)



Bob Phillips wrote:
Where is the Redim statement Arne? I would have expected to see it within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number
of
iterations in the loop. My problem is that I ReDim the array using the
number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Bob Phillips wrote:
Where is the Redim statement Arne? I would have expected to see it within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

I think it's probably much more efficient to ReDim Preserve once at the
end, a la

Dim ary()
reDim ary(n)
Do Until . . .
ary(r) = some_value
r=r+1
Loop
ReDim Preserve ary(r-1)

where n is a number big enough to accommodate all possible iterations,
rather than ReDim Preserve in each iteration of the loop.

Alan Beban

Yeah, makes sense, but Sod's law will say that one time you don't dim it big
enough.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
Where is the Redim statement Arne? I would have expected to see it
within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

I think it's probably much more efficient to ReDim Preserve once at the
end, a la

Dim ary()
reDim ary(n)
Do Until . . .
ary(r) = some_value
r=r+1
Loop
ReDim Preserve ary(r-1)

where n is a number big enough to accommodate all possible iterations,
rather than ReDim Preserve in each iteration of the loop.

Alan Beban

I remember seeing Myrna Larson's code a long time ago.

She would redim in groups of 100 keeping track of how many were used:

I found one sample where she used 20, but the theory would still hold:

Function GetFileList(FileSpec As String, FileNames() As String) As Integer
Dim Max As Integer
Dim NumFiles As Integer
Dim AFile As String

Max = 0
NumFiles = 0

AFile = Dir$(FileSpec)
Do While Len(AFile)
NumFiles = NumFiles + 1
If NumFiles Max Then
Max = Max + 20
ReDim Preserve FileNames(1 To Max) As String
End If
FileNames(NumFiles) = AFile
AFile = Dir$
Loop
GetFileList = NumFiles
If NumFiles Then ReDim Preserve FileNames(1 To NumFiles)

End Function 'GetFileList

From:
http://groups.google.co.uk/group/mic...7ff238 229169

or
http://tinyurl.com/hkowm



Bob Phillips wrote:

Yeah, makes sense, but Sod's law will say that one time you don't dim it big
enough.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alan Beban" <unavailable wrote in message
...
Bob Phillips wrote:
Where is the Redim statement Arne? I would have expected to see it
within
the loop, something like

Do Until ...
Redim Preserver ary (r)
ary(r) = some_value
r=r+1
Loop

I think it's probably much more efficient to ReDim Preserve once at the
end, a la

Dim ary()
reDim ary(n)
Do Until . . .
ary(r) = some_value
r=r+1
Loop
ReDim Preserve ary(r-1)

where n is a number big enough to accommodate all possible iterations,
rather than ReDim Preserve in each iteration of the loop.

Alan Beban

--

Dave Peterson

Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..). Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space. I
suppose you have seen the code I have missed something but I honestly do not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Anne - what value does r have initally? If it's negative you would
get a "Subscript out or range" error.

Andrew

Arne Hegefors wrote:
Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..). Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space. I
suppose you have seen the code I have missed something but I honestly do not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

These typos are catching - "Subscript out OF range"

Andrew Taylor wrote:
Anne - what value does r have initally? If it's negative you would
get a "Subscript out or range" error.

Andrew

Arne Hegefors wrote:
Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..). Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space. I
suppose you have seen the code I have missed something but I honestly do not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Hello! Yes I think there might have been a problem with r previously in the
code. I really dont get it but after having made some changes it all works
fine now. At least that part of the code! Thanks for all your help!

"Andrew Taylor" skrev:

These typos are catching - "Subscript out OF range"

Andrew Taylor wrote:
Anne - what value does r have initally? If it's negative you would
get a "Subscript out or range" error.

Andrew

Arne Hegefors wrote:
Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..). Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space. I
suppose you have seen the code I have missed something but I honestly do not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the number of
iterations in the loop. My problem is that I ReDim the array using the number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Arne,

That was me, I must have a cold as well.

I cannot reproduce your problem, but there are at least 2 problems in your
code, you create a sparse array, and you overwrite the data.

Maybe this will work better.

ReDim strMaturityArray(0 To iArray)
Do Until IsEmpty(rngSecId.Offset(r, lngTypeColumn))
If rngSecId.Offset(r, lngTypeColumn).Text = strGovBond Then
ReDim Preserve strMaturityArray(0 To iArray)
strMaturityArray(iArray) = Mid(rngSecId.Offset(r, 0), _
InStr(InStr(1, rngSecId.Offset(r, 0), " ")
+ 1, _
rngSecId.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(iArray)
iArray = iArray + 1
End If
r = r + 1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..).
Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space.
I
suppose you have seen the code I have missed something but I honestly do
not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the
number of
iterations in the loop. My problem is that I ReDim the array using the
number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in
advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop

Thanks Bob! No problem! I will look into your code! Many thanks again!

"Bob Phillips" skrev:

Arne,

That was me, I must have a cold as well.

I cannot reproduce your problem, but there are at least 2 problems in your
code, you create a sparse array, and you overwrite the data.

Maybe this will work better.

ReDim strMaturityArray(0 To iArray)
Do Until IsEmpty(rngSecId.Offset(r, lngTypeColumn))
If rngSecId.Offset(r, lngTypeColumn).Text = strGovBond Then
ReDim Preserve strMaturityArray(0 To iArray)
strMaturityArray(iArray) = Mid(rngSecId.Offset(r, 0), _
InStr(InStr(1, rngSecId.Offset(r, 0), " ")
+ 1, _
rngSecId.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(iArray)
iArray = iArray + 1
End If
r = r + 1
Loop

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
Hello Bob! I have a ReDim at the second line in my loop. Am I missing
something (I have a terrible cold and cannot really think straight..).
Every
time I run the loop I increase the array using the variable r. At the very
beginning of the loop I redim the array so that I will always have space.
I
suppose you have seen the code I have missed something but I honestly do
not
see it. Please, any help is appreciated. Thank you very much!

"Arne Hegefors" skrev:

I am redimming an array in a loop. I do not know in before hand how many
times the loop will run. the array must have the siame size as the
number of
iterations in the loop. My problem is that I ReDim the array using the
number
of iterations as upper boundary. However I get the error message "index
outside of interval". I do not understand how this can happen. Perhaps
someone can help me? Any help is appeciated! Thanks very much in
advance!

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
ReDim strMaturityArray(0 To r)
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
strMaturityArray(r) = Mid(rngSecID.Offset(r, 0), InStr(InStr(1,
rngSecID.Offset(r, 0), " ") + 1, rngSecID.Offset(r, 0), " ") + 1, 4)
MsgBox strMaturityArray(r)
End If
r = r + 1
Loop