I am trying to create 50-100 instances of this code

[brunob]

The basic/core code is as follows:

{
On Error Resume Next
Set Shape = ActiveSheet.Shapes("Picture 1")
On Error GoTo 0

If Shape Is Nothing Then
Exit Sub
End If

If Range("Q4").Value < 0.025 Then

ActiveSheet.Shapes("Picture 1").Select
Selection.Cut

End If
}

I am trying to create 50-100 instances of this code where each instance changes a bit. For example, the 2nd code instance must be like this:

{
On Error Resume Next
Set Shape = ActiveSheet.Shapes("Picture 2")
On Error GoTo 0

If Shape Is Nothing Then
Exit Sub
End If

If Range("Q5").Value < 0.025 Then

ActiveSheet.Shapes("Picture 2").Select
Selection.Cut

End If
}

... and the 3rd:

{
On Error Resume Next
Set Shape = ActiveSheet.Shapes("Picture 3")
On Error GoTo 0

If Shape Is Nothing Then
Exit Sub
End If

If Range("Q6").Value < 0.025 Then

ActiveSheet.Shapes("Picture 3").Select
Selection.Cut

End If
}

As you see:

Q4 corresponds to Picture 1
Q5 corresponds to Picture 2
Q6 corresponds to Picture 3
...
Q50 corresponds to Picture 47
...
Q103 corresponds to Picture 100

I am at a loss for writing a more elegant code rather than repeating this for 50-100 times! Could you please show me how to do it?
Thanks!

[jcis]

Welcome to the Forums

You need to create a new Sub and pass whatever is changing as parameter (it seems you will need to use 2 String parameters), then you can call your Sub as many times as you want. You could also create a loop to avoid calling the sub line by line multiple times, for each iteration you would create 2 Strings variables in code then sending them as parameters and it looks like simply concatenating the corresponding number to each variable (creating the corresponding control name) for each iteration should be enough for you.

[MarkT]

Maybe you could loop it like this

Dim i As Integer
Dim strRange As String
Dim strPicture As String


    For i = 1 To 100
        strPicture = "Picture " & i
        Set Shape = ActiveSheet.Shapes(strPicture)

        If Shape Is Nothing Then
            Exit Sub
        End If

        strRange = "Q" & (i + 3)
        If Range(strRange).Value < 0.025 Then
            ActiveSheet.Shapes(strPicture).Select
            Selection.Cut
        End If

    Next i

[brunob]

Thanks, MarkT! It was very, very helpful and I could have never thought of this code by myself since I am struggling to learn VB coding as I go with my task requirements (that's why my knowledge of VB is very shallow ). I put it in my spreadsheet and tested it over many hours and it runs great!

Thank you all, again, for replying to my post, and I'm "really" glad I joined this forum!

[firoz.raj]

for 50 instance try the following way .

Dim i As Integer
Dim strRange As String
Dim strPicture As String


    For i = 1 To 50
        strPicture = "Picture " & i
        Set Shape = ActiveSheet.Shapes(strPicture)

        If Shape Is Nothing Then
            Exit Sub
        End If

        strRange = "Q" & (i + 3)
        If Range(strRange).Value < 0.025 Then
            ActiveSheet.Shapes(strPicture).Select
            Selection.Cut
        End If

    Next i