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??

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

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 <[email protected]>,
[email protected] 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
>
>

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 <[email protected]>,
[email protected] 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

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 <[email protected]>,
> [email protected] 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
> >
> >
>

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 .
>
>
> --
> baldomero
> ------------------------------------------------------------------------
> baldomero's Profile: http://www.excelforum.com/member.php...o&userid=25680
> View this thread: http://www.excelforum.com/showthread...hreadid=397769
>
>

In article <[email protected]>,
[email protected] 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