How can I put my VBA code into a loop?

[chrisignm]

Hey, I got a working code, but it's very confusing and long. Could you please give me hints how to make a loop out of it?

Sub ViewListLevel2()
Dim txt1 As Shape, txt2 As Shape, txt3 As Shape, txt4 As Shape, txt5 As Shape, txt6 As Shape, MaxRows As Long
Set txt1 = ActiveSheet.Shapes("View1")
Set txt2 = ActiveSheet.Shapes("View2")
Set txt3 = ActiveSheet.Shapes("View3")
Set txt4 = ActiveSheet.Shapes("View4")
Set txt5 = ActiveSheet.Shapes("View5")
Set txt6 = ActiveSheet.Shapes("View6")
Worksheets("Calc").Range("R4").Calculate
If Worksheets("Calc").Range("R4").Value = 1 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L29").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L30").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L31").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L32").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L33").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L34").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 2 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L35").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L36").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L37").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L38").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L39").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L40").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 3 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L41").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L42").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L43").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L44").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L45").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L46").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 4 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L47").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L48").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L49").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L50").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L51").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L52").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 5 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L53").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L54").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L55").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L56").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L57").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L58").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 6 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L59").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L60").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L61").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L62").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L63").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L64").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 7 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L65").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L66").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L67").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L68").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L69").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L70").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 8 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L71").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L72").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L73").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L74").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L75").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L76").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 9 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L77").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L78").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L79").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L80").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L81").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L82").Value
End With


ElseIf Worksheets("Calc").Range("R4").Value = 10 Then
With txt1
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L83").Value
End With
With txt2
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L84").Value
End With
With txt3
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L85").Value
End With
With txt4
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L86").Value
End With
With txt5
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L87").Value
End With
With txt6
        .TextFrame.Characters.Text = Worksheets("Calc").Range("L88").Value
End With
End If


End Sub

Thanks

 

[Molden]

I've not tested this and you may be able to trim it down further but I would do

Sub ViewListLevel2()

Dim txt1 As Shape, txt2 As Shape, txt3 As Shape, txt4 As Shape, txt5 As Shape, txt6 As Shape, MaxRows As Long

Set txt1 = ActiveSheet.Shapes("View1")
Set txt2 = ActiveSheet.Shapes("View2")
Set txt3 = ActiveSheet.Shapes("View3")
Set txt4 = ActiveSheet.Shapes("View4")
Set txt5 = ActiveSheet.Shapes("View5")
Set txt6 = ActiveSheet.Shapes("View6")

    myArray = Array(txt1, txt2, txt3, txt4, txt5, txt6)
    
    Worksheets("Calc").Range("R4").Calculate

    If Worksheets("Calc").Range("R4").Value = 1 Then

       n = 29

        For Each x In myArray
        
 
        
            With x
                
                .TextFrame.Characters.Text = Worksheets("Calc").Cells(n, 12).Value
                n = n + 1
                
            End With
        
        Next x
    
    End If

End Sub