on error resume next gives wrong result when inserting pictures

Question

I created a code to insert pictures into a cell from a link (next to that cell). Sometimes a picture is deleted in the file it links to. I get an error 400, but when I put 'on error resume next' it leaves the last cell with a right link empty and puts that picture in the cell with a wrong link. Also the last cell with a right link is empty.

The position of 'on error resume next' does not matter (before loop, or at any place in the loop)

How can I avoid that? Just skip the wrong link and put pictures at the right positions?

Sub InsertPictures()

    Call DeleteAllPicturesInRange

    Dim pic As String
    Dim myPicture As Picture
    Dim rng As Range
    Dim cl As Range

    Set rng = Range("J5:J124")

    For Each cl In rng

        pic = cl.Offset(0, 1)

        Set myPicture = ActiveSheet.Pictures.Insert(pic)

        With myPicture
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = cl.Width
            .Height = cl.Height
            .Top = Rows(cl.Row).Top
            .Left = Columns(cl.Column).Left
        End With

    Next

End Sub

Answer 1

Adding one line of code empties the variables and ensures you are not using the variable across loops. basically you have two variables that you run the risk of using by default in the next loop if you use on error resume next and they are pic and mypicture a good practice is to clear these variables as soon as you are done with them and since they are by default used in the next loop as a new value is not set. Does that make sense?

Note - to clear a range variable you have to assign it to another range hence Cell(1,1) set it to anyother cell that matches your need

Set myPicture = Nothing

pic = Cell(1,1)

Sub InsertPictures()

Call DeleteAllPicturesInRange

Dim pic As String
Dim myPicture As Picture
Dim rng As Range
Dim cl As Range

Set rng = Range("J5:J124")

    For Each cl In rng

    pic = cl.Offset(0, 1)

    Set myPicture = ActiveSheet.Pictures.Insert(pic)

    With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = cl.Width
        .Height = cl.Height
        .Top = Rows(cl.Row).Top
        .Left = Columns(cl.Column).Left
    End With

    Set myPicture = Nothing
    pic = cell(1,1)
Next

End Sub

Answer 2

Thanks for the help. I added on error resume next in combination with Set mypicture = Nothing and it works!!!

Sub InsertPictures()

Call DeleteAllPictures

Dim Pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("J5:J124")
For Each cl In rng

Pic = cl.Offset(0, 1)

    On Error Resume Next

    Set myPicture = ActiveSheet.Pictures.Insert(Pic)

    With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = cl.Width
        .Height = cl.Height
        .Top = Rows(cl.Row).Top
        .Left = Columns(cl.Column).Left
    End With

Set myPicture = Nothing

Next

End Sub