For loop which returns a wrong array of elements

Question

I have a command button with a code like this:

Dim array1()
Dim nrow As Integer

nrow = Range("C4:C241").Count

ReDim array1(nrow)

For i = 0 To nrow
    'array1(i) = Range("C" & i + 3)
    array1(i) = i
Next

Range("AY4:AY" & nrow + 3) = array1

The comment symbols ' is present in the case you wanted to switch to make some experiments.

What I get running this macro in Excel is a column of values equal to 0, which is not what I would expect to get: I would like to print array1 in the Range("AY4:AY241").

Where am I wrong?

brettdj · Accepted Answer · 2013-12-28 01:17:39Z

5

Consider using Tranpose for your intended output.

Sub dural()
    Dim array1()
    Dim nrow As Integer
    nrow = Range("C4:C241").Count
    ReDim array1(nrow)
    For i = 0 To nrow
        array1(i) = i
    Next
    Range("AY4:AY" & nrow + 3) = Application.WorksheetFunction.Transpose(array1)
End Sub

edited Dec 28, 2013 at 1:17

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered Dec 27, 2013 at 12:54

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lisa Ann Over a year ago

Well, I did not think it returned by default a row array :)

Gary's Student Over a year ago

Its actually a 2-D array!

Ricardo Appleton Over a year ago

The for loop should start at 1, since in Excel rows start at 1, and keeping it as is will result in having the values of the cells shifted up by 1 row.

John Alexiou · Accepted Answer · 2013-12-28 03:22:33Z

1

I suggest using

ReDim array1(1 to nrow, 1 to ncol)

and then

Range("C4").Resize(nrow,ncol).Value = array1

This way you make sure the array is 1-base which is what Excel likes, and the output range is exactly the size you want.

answered Dec 28, 2013 at 3:22

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Comments

Ricardo Appleton · Accepted Answer · 2013-12-30 14:02:20Z

0

I think the problem here is that you're doing for i = 0 instead of for i = 1

when i = 0, you're pointing at row 3; If you did sth like array1(i) = Range("C" & i), not adding 3, you'd probably get an error

edit

following brettdj's comment, this isn't an untested answer. It's a head up for an error in the code. To be a full answer, do as I say, starting the loop at 1 and then add, as in the accepted answer

Range("AY4:AY" & nrow + 3) = Application.WorksheetFunction.Transpose(array1)

edited Dec 30, 2013 at 14:02

answered Dec 27, 2013 at 12:44

Ricardo Appleton

67911 silver badges25 bronze badges

Collectives™ on Stack Overflow

For loop which returns a wrong array of elements

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related