Can i loop the Array elements in VBA

Question

Can I somehow loop all this things, so that it will not take so much space and solve the program.

I have around 600, such type of MYarray elements. Suggest me something. how can i loop all these things.

I tried using a for loop and if statement within that loop .but was unsuccessful

    Myarray(1) = Sheets("DealComparison").Cells(2, 1)
    Myarray(2) = Sheets("DealComparison").Cells(3, 1)
    Myarray(3) = Sheets("DealComparison").Cells(4, 1)
    Myarray(4) = Sheets("DealComparison").Cells(5, 1)
    ..
    ..
    Myarray(600)=Sheets("DealComparison").Cells(601, 1)

so that the loop will automatically continue until there are no values.

Please help me solving this as i am new to vba.

I am not too sure about it,I think we need a for loop and a If loop with in FOR. Not too sure about it. Please help me do this Thanks

You could put the range into a 2-dimensional array in one line of code rather than looping. — SierraOscar
– SierraOscar, Commented Apr 28, 2015 at 13:33

Gary's Student · Accepted Answer · 2015-04-28 13:38:51Z

2

You could use code similar to:

Sub dural()
    Dim I As Long
    Dim Myarray(1 To 4) As Variant

    For I = 1 To 4
        Myarray(I) = Sheets("DealComparison").Cells(I + 1, 1)
    Next I
End Sub

pick your own upper limit.

EDIT#1:

If you want the code to pick the upper limit then:

Sub dural()
    Dim I As Long, N As Long
    N = Sheets("DealComparison").Cells(Rows.Count, 1).End(xlUp).Row - 1
    Dim Myarray()
    ReDim Myarray(1 To N)

    For I = 1 To N
        Myarray(I) = Sheets("DealComparison").Cells(I + 1, 1)
    Next I
End Sub

edited Apr 28, 2015 at 13:38

answered Apr 28, 2015 at 13:29

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

Tom Over a year ago

You just beat me to this. However I would use I = Ubound(Myarray)

Collectives™ on Stack Overflow

Can i loop the Array elements in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related