looping in VBA.

[kelly mort]

Hello All,
Can somebody explain to me how to loop through these controls in the macro below???
Thanks
Kelly

Dim Drng As Range, lrRng As Range
With Sheet1
    Set Drng = .Range("B7")
    Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
    lrRng.Value = Application.Max(Drng.EntireColumn) + 1
    lrRng.Offset(0, 1).Value = Me.Rw2.Value
    lrRng.Offset(0, 2).Value = Me.Rw3.Value
    lrRng.Offset(0, 3).Value = Me.Rw4.Value
    lrRng.Offset(0, 4).Value = Me.Rw5.Value
    lrRng.Offset(0, 5).Value = Me.Rw6.Value
    lrRng.Offset(0, 6).Value = Me.Rw7.Value
    lrRng.Offset(0, 7).Value = Me.Rw8.Value
    lrRng.Offset(0, 8).Value = Me.Rw9.Value
    lrRng.Offset(0, 9).Value = Me.Rw10.Value
    lrRng.Offset(0, 10).Value = Me.Rw11.Value
    lrRng.Offset(0, 11).Value = Me.Rw12.Value
End With

 

[Norie]

Try this.

Dim Drng As Range, lrRng As Range
Dim I As Long

    With Sheet1
        Set Drng = .Range("B7")
        Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
        lrRng.Value = Application.Max(Drng.EntireColumn) + 1
        For I = 1 To 11
            lrRng.Offset(0, I).Value = Me.Controls("Rw" & I + 1).Value
        Next I
    End With

 

[kelly mort]

Wow thanks. I was killing myself with some double loop and it was not working.
Haha
Thanks again.
Kelly