CODEWITHSUNDEEP
arraysvbaexcelfor-loop
user2512664
user2512664

Reputation: 11

For-Next Loop and Arrays - VBA

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

Upvotes: 1

Views: 9975

Answers (5)

Nithin Bairi
Nithin Bairi

Reputation: 11

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

Upvotes: 1

John Alexiou
John Alexiou

Reputation: 29244

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

Upvotes: 0

MattD
MattD

Reputation: 1

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

Upvotes: 0

Andy G
Andy G

Reputation: 19367

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

Upvotes: 4

mango
mango

Reputation: 5636

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.

Upvotes: 0

Related Questions