Error with a For loop with arrays
 

Hello, I am trying to write the values of two arrays (one boolean and
the other integer of one dimension of 50 values each one) to a
worksheet.

the code i use is this:

For I = 0 to 10
Cells(1 + I, "A").Value = I +1
Cells(1 + I, "B").Value = Array_Bool(I)
Cells(1 + I, "C").Value = Array_Int(I)
Next I

The problem I get is that Excel gives me an error:

' Error '9' occurred in execution time. '
' Subindex out of interval '

(translated from spanish)

the third line is marked in yellow with the debug option, and if I mark
it as comment, then the same happens with the fourth line.

variables are declared this way:

Dim Array_Bool(50) As Boolean
Dim Array_Int(50) As Integer
Dim I As Integer

Also, it works if I manually write this:
Cells(25, 2).Value = Array_Bool(0)
Cells(26, 2).Value = Array_Bool(1)
Cells(27, 2).Value = Array_Bool(2)
Cells(28, 2).Value = Array_Bool(3)
Cells(29, 2).Value = Array_Bool(4)
Cells(30, 2).Value = Array_Bool(5)
Cells(31, 2).Value = Array_Bool(6)
Cells(32, 2).Value = Array_Bool(7)
Cells(33, 2).Value = Array_Bool(0)

Cells(25, 3).Value = Array_Int(0)
Cells(26, 3).Value = Array_Int(1)
Cells(27, 3).Value = Array_Int(2)
Cells(28, 3).Value = Array_Int(3)
Cells(29, 3).Value = Array_Int(4)
Cells(30, 3).Value = Array_Int(5)
Cells(31, 3).Value = Array_Int(6)
Cells(32, 3).Value = Array_Int(7)
Cells(33, 3).Value = Array_Int(0)



Shouldn't the first code and the second one produce the same result??


--
baldomero
------------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...o&userid=25680
View this thread: http://www.excelforum.com/showthread...hreadid=397769

Error with a For loop with arrays
 

The second argument of your cells uses letters when it expects a number
(similar to the code you have at the bottom of your post

For I = 0 to 10
Cells(1 + I, 1).Value = I +1 'remove A
Cells(1 + I, 2).Value = Array_Bool(I) 'remove B
Cells(1 + I, 3).Value = Array_Int(I) 'remove C
Next I

--
HTH...

Jim Thomlinson


"baldomero" wrote:


Hello, I am trying to write the values of two arrays (one boolean and
the other integer of one dimension of 50 values each one) to a
worksheet.

the code i use is this:

For I = 0 to 10
Cells(1 + I, "A").Value = I +1
Cells(1 + I, "B").Value = Array_Bool(I)
Cells(1 + I, "C").Value = Array_Int(I)
Next I

The problem I get is that Excel gives me an error:

' Error '9' occurred in execution time. '
' Subindex out of interval '

(translated from spanish)

the third line is marked in yellow with the debug option, and if I mark
it as comment, then the same happens with the fourth line.

variables are declared this way:

Dim Array_Bool(50) As Boolean
Dim Array_Int(50) As Integer
Dim I As Integer

Also, it works if I manually write this:
Cells(25, 2).Value = Array_Bool(0)
Cells(26, 2).Value = Array_Bool(1)
Cells(27, 2).Value = Array_Bool(2)
Cells(28, 2).Value = Array_Bool(3)
Cells(29, 2).Value = Array_Bool(4)
Cells(30, 2).Value = Array_Bool(5)
Cells(31, 2).Value = Array_Bool(6)
Cells(32, 2).Value = Array_Bool(7)
Cells(33, 2).Value = Array_Bool(0)

Cells(25, 3).Value = Array_Int(0)
Cells(26, 3).Value = Array_Int(1)
Cells(27, 3).Value = Array_Int(2)
Cells(28, 3).Value = Array_Int(3)
Cells(29, 3).Value = Array_Int(4)
Cells(30, 3).Value = Array_Int(5)
Cells(31, 3).Value = Array_Int(6)
Cells(32, 3).Value = Array_Int(7)
Cells(33, 3).Value = Array_Int(0)



Shouldn't the first code and the second one produce the same result??


--
baldomero
------------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...o&userid=25680
View this thread: http://www.excelforum.com/showthread...hreadid=397769

Error with a For loop with arrays
 

Actually, the Cells property accepts a letter as a column. It would be
pretty easy to try it yourself. In the immediate window:

?cells(1,"a").value

will give you the value of cell A1 in the activesheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
The second argument of your cells uses letters when it expects a number
(similar to the code you have at the bottom of your post

For I = 0 to 10
Cells(1 + I, 1).Value = I +1 'remove A
Cells(1 + I, 2).Value = Array_Bool(I) 'remove B
Cells(1 + I, 3).Value = Array_Int(I) 'remove C
Next I

Error with a For loop with arrays
 

Do you by any chance have Option Base 1 at the top of the module?

When you get the error, in the VBE, check View | Locals Window. In
there expand the entry for Array_Bool. What is the index for the first
entry?

Alternatively, specify the lower bound yourself

Dim Array_Bool(0 to 50) As Boolean

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hello, I am trying to write the values of two arrays (one boolean and
the other integer of one dimension of 50 values each one) to a
worksheet.

the code i use is this:

For I = 0 to 10
Cells(1 + I, "A").Value = I +1
Cells(1 + I, "B").Value = Array_Bool(I)
Cells(1 + I, "C").Value = Array_Int(I)
Next I

The problem I get is that Excel gives me an error:

' Error '9' occurred in execution time. '
' Subindex out of interval '

(translated from spanish)

the third line is marked in yellow with the debug option, and if I mark
it as comment, then the same happens with the fourth line.

variables are declared this way:

Dim Array_Bool(50) As Boolean
Dim Array_Int(50) As Integer
Dim I As Integer

Also, it works if I manually write this:
Cells(25, 2).Value = Array_Bool(0)
Cells(26, 2).Value = Array_Bool(1)
Cells(27, 2).Value = Array_Bool(2)
Cells(28, 2).Value = Array_Bool(3)
Cells(29, 2).Value = Array_Bool(4)
Cells(30, 2).Value = Array_Bool(5)
Cells(31, 2).Value = Array_Bool(6)
Cells(32, 2).Value = Array_Bool(7)
Cells(33, 2).Value = Array_Bool(0)

Cells(25, 3).Value = Array_Int(0)
Cells(26, 3).Value = Array_Int(1)
Cells(27, 3).Value = Array_Int(2)
Cells(28, 3).Value = Array_Int(3)
Cells(29, 3).Value = Array_Int(4)
Cells(30, 3).Value = Array_Int(5)
Cells(31, 3).Value = Array_Int(6)
Cells(32, 3).Value = Array_Int(7)
Cells(33, 3).Value = Array_Int(0)



Shouldn't the first code and the second one produce the same result??


--
baldomero
------------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...o&userid=25680
View this thread: http://www.excelforum.com/showthread...hreadid=397769

Error with a For loop with arrays
 

That's not it Jim, letters for the column argument are perfectly acceptable,
so that is not the cause (at least in English language versions), although,
I'll bet that if your suggestion will work.

I think that it is something to do with that the language version, but
without a Spanish or opther language version, it is difficult to try.

Baldermo,

Record a simple macro that selects a cell What does the code say? Does it
say Range("A1"Q).Select say, or something else.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
The second argument of your cells uses letters when it expects a number
(similar to the code you have at the bottom of your post

For I = 0 to 10
Cells(1 + I, 1).Value = I +1 'remove A
Cells(1 + I, 2).Value = Array_Bool(I) 'remove B
Cells(1 + I, 3).Value = Array_Int(I) 'remove C
Next I

--
HTH...

Jim Thomlinson


"baldomero" wrote:


Hello, I am trying to write the values of two arrays (one boolean and
the other integer of one dimension of 50 values each one) to a
worksheet.

the code i use is this:

For I = 0 to 10
Cells(1 + I, "A").Value = I +1
Cells(1 + I, "B").Value = Array_Bool(I)
Cells(1 + I, "C").Value = Array_Int(I)
Next I

The problem I get is that Excel gives me an error:

' Error '9' occurred in execution time. '
' Subindex out of interval '

(translated from spanish)

the third line is marked in yellow with the debug option, and if I mark
it as comment, then the same happens with the fourth line.

variables are declared this way:

Dim Array_Bool(50) As Boolean
Dim Array_Int(50) As Integer
Dim I As Integer

Also, it works if I manually write this:
Cells(25, 2).Value = Array_Bool(0)
Cells(26, 2).Value = Array_Bool(1)
Cells(27, 2).Value = Array_Bool(2)
Cells(28, 2).Value = Array_Bool(3)
Cells(29, 2).Value = Array_Bool(4)
Cells(30, 2).Value = Array_Bool(5)
Cells(31, 2).Value = Array_Bool(6)
Cells(32, 2).Value = Array_Bool(7)
Cells(33, 2).Value = Array_Bool(0)

Cells(25, 3).Value = Array_Int(0)
Cells(26, 3).Value = Array_Int(1)
Cells(27, 3).Value = Array_Int(2)
Cells(28, 3).Value = Array_Int(3)
Cells(29, 3).Value = Array_Int(4)
Cells(30, 3).Value = Array_Int(5)
Cells(31, 3).Value = Array_Int(6)
Cells(32, 3).Value = Array_Int(7)
Cells(33, 3).Value = Array_Int(0)



Shouldn't the first code and the second one produce the same result??


--
baldomero
------------------------------------------------------------------------
baldomero's Profile:
http://www.excelforum.com/member.php...o&userid=25680
View this thread:
http://www.excelforum.com/showthread...hreadid=397769

Error with a For loop with arrays
 

Well I'll be darned... I only use cells when I need to increment through a
range so I have always used numbers. (Even then I normally use a range object
and offsets.)But you are absolutely correct. Thanks...
--
HTH...

Jim Thomlinson


"Tushar Mehta" wrote:

Actually, the Cells property accepts a letter as a column. It would be
pretty easy to try it yourself. In the immediate window:

?cells(1,"a").value

will give you the value of cell A1 in the activesheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
The second argument of your cells uses letters when it expects a number
(similar to the code you have at the bottom of your post

For I = 0 to 10
Cells(1 + I, 1).Value = I +1 'remove A
Cells(1 + I, 2).Value = Array_Bool(I) 'remove B
Cells(1 + I, 3).Value = Array_Int(I) 'remove C
Next I

Error with a For loop with arrays
 

The select cell code is this:

" Range("H20").Select "

Also note that it manually works if I write

cells...value=Array_Bool(0)
...
cells...valur=Array_Bool(8)

as the range is declared with array of (50), that is from 0 to 50, thi
way it is not an option base related question, just I don't understan
why the FOR LOOP fails if the manual versin does exactly the same.

Also, I am sure (99%) that the Office version affects only to functio
in cells, that is worksheetfunction.WhateverFunctionYouWanToUse(). I
VBA code, it doesn't matter what language version you are using,
think :confused:

--
baldomer
-----------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=39776

Error with a For loop with arrays
 

0 to 50 is 51 items which could be a problem.
--
HTH...

Jim Thomlinson


"baldomero" wrote:


The select cell code is this:

" Range("H20").Select "

Also note that it manually works if I write

cells...value=Array_Bool(0)
...
cells...valur=Array_Bool(8)

as the range is declared with array of (50), that is from 0 to 50, this
way it is not an option base related question, just I don't understand
why the FOR LOOP fails if the manual versin does exactly the same.

Also, I am sure (99%) that the Office version affects only to function
in cells, that is worksheetfunction.WhateverFunctionYouWanToUse(). In
VBA code, it doesn't matter what language version you are using, I
think :confused: .


--
baldomero
------------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...o&userid=25680
View this thread: http://www.excelforum.com/showthread...hreadid=397769

Error with a For loop with arrays
 

In article ,
says...
Well I'll be darned... I only use cells when I need to increment through a
range so I have always used numbers. (Even then I normally use a range object
and offsets.)But you are absolutely correct. Thanks...

Yes, I rarely (never?) use Cells unless it is with number,number
indexing. Otherwise, I use Range("A1"). This is one of those
(infrequent?) instances where the software is a lot more flexible that
the documentation would lead one to believe. The same applies to the
Range property. It accepts a lot more variations in its arguments than
the documentation would lead one to believe.

Of course, that does leave people vulnerable to changes by MS. If it
breaks something that works but is not documented...{grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions