For-Next Loop and Arrays - VBA

Question

I keep making the same mistake, and I have no idea what it is. Every time I make a For-Loop dealing with arrays (usually, trying to read one array and write the values in a second array), it just takes the very last value from the first array and writes it in every single slot of the second array.

Here's a copy of the easiest/shortest one I'm working on. It's supposed to read the values out of A1:A10 and write them to B1:K1.

Sub Problem1()
    Dim a(1 To 10) As Single
    Dim b(1, 10) As Single
    Dim i As Integer



    For i = 1 To 10
        a(i) = Range("A" + CStr(i)).Value
    Next i

    For i = 1 To 10
        b(1, i) = a(i)
    Next i

    Range("B1:K1") = b(1, 10)

End Sub

Andy G · Accepted Answer · 2013-06-23 00:52:28Z

4

Range("B1:K1") = b(1, 10)

this only copies a single array-element.

You could do the following:

Range("B1:K1").Value = Application.WorksheetFunction _
    .Transpose(Range("A1:A10"))

To store the original values in an array would (using my approach) require it to be declared as a Variant, unfortunately:

Sub Problem1()
    Dim a As Variant

    a = Range("A1:A10")

    Range("B1:K1") = Application.WorksheetFunction.Transpose(a)
End Sub

If you still want to make use of both arrays:

Sub Problem1()
    Dim a As Variant
    Dim b(1 To 10) As Single
    Dim i As Integer

    a = Range("A1:A10")
    For i = 1 To 10
        b(i) = a(i, 1)
    Next i
    Range("B1:K1") = b
End Sub

edited Jun 23, 2013 at 0:52

answered Jun 23, 2013 at 0:37

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

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

Comments

Chris · Accepted Answer · 2018-02-09 12:49:28Z

1

Sub Problem1()
    Dim a(1 To 10) As Single
    Dim b(1, 10) As Single
    Dim i As Integer

    For i = 1 To 10
        a(i) = Range("A" + CStr(i)).Value
    Next i

    For i = 1 To 10
        b(1, i) = a(i)
    Next i

    Range("B1:K1") = b()
End Sub

edited Feb 9, 2018 at 12:49

Chris

3247 silver badges23 bronze badges

answered Dec 11, 2014 at 10:48

Nithin Bairi

111 bronze badge

1 Comment

Nithin Bairi Over a year ago

i am not that much perfect in vba. but the above is giving the output what you are looking for

mango · Accepted Answer · 2013-06-23 00:35:29Z

0

For what you want, the better way that you could go about it would be:

Sub Problem1()

Dim a(1 To 10) As Single
Dim i As Integer

For i = 1 To 10
    a(i) = Range("A" + CStr(i)).Value
Next i

Range("B1:K1") = a
End Sub

As for what's wrong with your current implementation, it's that you assigned one single to the entire range of a group of cells. better would be to loop through the contents of the array to output it individually into each cell. But vba handles this natively (outputting an array to a range of cells) as illustrated by my example above.

answered Jun 23, 2013 at 0:35

mango

5,6364 gold badges32 silver badges42 bronze badges

Comments

MattD · Accepted Answer · 2013-06-23 01:13:05Z

0

Sub Problem1()
Dim i, j As Integer
j = 2

For i = 1 To 10
    ActiveSheet.Cells(1, j).Value = ActiveSheet.Range("A" & i)
    j = j + 1
Next i
End Sub

answered Jun 23, 2013 at 1:13

MattD

11 bronze badge

1 Comment

Doug Glancy Over a year ago

You could eliminate j and just use i + 1. Also, note that you are declaring i as a Variant here. See this Chip Pearson page and scroll down to "Pay Attention To Variables Declared With One Dim Statement."

John Alexiou · Accepted Answer · 2013-06-24 00:40:24Z

0

Try to make the steps as clear as possible, and read/write all values in one step to avoid having the output change the input (overlapping cells). The flow should be [Read]->[Calc]->[Write].

Sub Problem1()
    Dim a() as Variant, b() as Variant, i as Integer

    'Get Values
    a = Range("A1").Resize(10,1).Value2

    'Transform the arrays in any way you need.
    'Make sure the intent is clear
    ReDim b(1 to 1, 1 to 10)
    For i=1 to 10
        b(1,i) = a(i,1)
    Next i

    'Set Values
    Range("B1").Resize(1,10).Value2 = b

End Sub

answered Jun 24, 2013 at 0:40

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Collectives™ on Stack Overflow

For-Next Loop and Arrays - VBA

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related