for next explained please

[slim]

I have the following code:

Range("A2").Select
Label1.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label2.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label3.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label4.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label5.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label6.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label7.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label8.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Label9.Caption = ActiveCell.Value
ActiveCell.Offset(1, 0).Select

which goes on for lots of instances. I know it is a newbie question but how can I shorten it so that it goes through each label in turn?

Thanks

[davers5]

It's actually not that "newbie" of a question. To do this in VB is simple; you would just create an object array and use a for next loop. However, in VBA you cannot create an object array. I had this same question a while ago and figured out how to do this but I haven't used it in while so I've forgotten. I'll be back with an answer...

Dave

[davers5]

Slim--

See this link or search control arrays in the archives.

http://www.mrexcel.com/board/viewtop...c=3611&forum=2

Dave

[Russell Hauf]

If your labels are on a worksheet, you can use something like this:

Sub SetLabels() 



'

    Dim intRow As Integer 

    Dim intNumLabels As Integer 

    Dim ole As OLEObject 

'

    ' Set intNumLabels to the number of Labels you have -

    ' let me know if the number of labels will grow/shrink on a

    ' regular basis and I can show you a dynamic way to do this

    ' (instead of explicitly setting the number of labels here in

    '  your code).

    intNumLabels = 8 

    

    ' Ok, since your Label names start with one (Label1), but your

    ' data starts in row 2, go from row 2 to the number of labels

    ' plus one (so if you have 10 labels, your data will be in rows

    ' 2 to 11.

    For intRow = 2 To intNumLabels + 1 

        ActiveSheet.OLEObjects("Label" & intRow - 1).Object.Caption = Cells(intRow, 1) 

    Next 



End Sub