Excel VBA cycle

Question

How can I put this code in a cycle? For example start the cylce in the A3 cell, end of the cycle in A100 cell.

Sub CenterImages()
        With ActiveSheet.Shapes("Picture 1")
            .Top = Range("A1").Top + (Range("A1").Height - .Height) / 2
            .Left = Range("A1").Left + (Range("A1").Width - .Width) / 2
        End With
End Sub

Pᴇʜ · Accepted Answer · 2021-09-30 09:17:16Z

2

Do something like that:

Option Explicit

Public Sub CenterImages()
    Dim i As Long
    For i = 3 To 100
        CenterImage i
    Next i
End Sub

Public Sub CenterImage(ByVal Index As Long)
    With ActiveSheet.Shapes("Picture " & Index)
        .Top = Range("A" & Index).Top + (Range("A" & Index).Height - .Height) / 2
        .Left = Range("A" & Index).Left + (Range("A" & Index).Width - .Width) / 2
    End With
End Sub

Make your code generic by using an Index then call your procedure in a loop from 3 to 100 specifying that index.

answered Sep 30, 2021 at 9:17

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA cycle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related