VBA Excel Transform to loop

Question

Here i have my hundred line of code please enlighten me about how do i put line code into loop here is my try but i wont work out still trying

 If sp.Name Like "Rounded Rectangle*" Or sp.Name Like "Oval*" Then


        For i = 11 To 100

        x = i - 9

        Sheet2.Shapes.Range(Array("Rounded Rectangle " + i)).TextFrame.Characters.Text = Sheet1.Range("A" + x)

        Next i


    End If

and repeat until X = 110

in this case how can i change it in to correct loop please advice

thank you

Gary's Student · Accepted Answer · 2014-02-19 04:07:11Z

1

This is a general approach to making a loop to cover a string variable....say we want to loop over Shape("Rectangle 1")....Shape("Rectangle 2")....Shape("Rectangle 3)..... , etc.

Dim str As String, i As Long
For i = 11 To 100
    str = "Rectangle " & CStr(i)
    Sheets2.Shapes(str)................
Next i

and use a similar approach to make "A2"..."A3".........

answered Feb 19, 2014 at 4:07

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

eathapeking Over a year ago

Sub Adjust() Dim ws As Worksheet Dim sp As Shape Set ws = Sheet2 For Each sp In ws.Shapes If sp.Name Like "Rounded Rectangle*" Or sp.Name Like "Oval*" Then Dim strs As String Dim str As String, i As Long For i = 11 To 100 str = "Rectangle " & CStr(i) strs = CStr(i - 9) Sheet2.Shapes.Range(str).TextFrame.Characters.Text = Sheet1.Range("A" & strs) Next i End If Next End Sub

Patrick Lepelletier · Accepted Answer · 2014-02-20 14:27:53Z

0

simple math:

For i = 11 To 100

change to

For i = 11 To 119

answered Feb 20, 2014 at 14:27

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

VBA Excel Transform to loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related