CODEWITHSUNDEEP
arraysvbaexcelfor-loop
Lisa Ann
Lisa Ann

Reputation: 3485

For loop which returns a wrong array of elements

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?

Upvotes: 3

Views: 100

Answers (3)

Ricardo Appleton
Ricardo Appleton

Reputation: 679

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)

Upvotes: 0

John Alexiou
John Alexiou

Reputation: 29244

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.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 5

Related Questions