0

I've written some code that copies images in Excel. It checks image position in relation to fist list and copies it next to the other list:

Sub PictureCopierMacroTest()

    Dim pic As picture
    Dim picDestination As String
    Dim picHeight As Integer
    
    For Each pic In ActiveSheet.Pictures

        picDestination = pic.TopLeftCell.EntireRow.Cells(1, 1).Value
        picDestination = Range("C2:C11").Find(picDestination, LookIn:=xlValues, LookAt:=xlWhole).Address 'This line has to handle errors by skipping entire loop iteration.
        picHeight = pic.Height - (pic.Height Mod 15) + 15
        ActiveSheet.Shapes(pic.Name).Copy
        Range(picDestination).Offset(0, 1).Select
        ActiveSheet.Paste
        Range(picDestination).Select
        If Selection.RowHeight < picHeight Then Selection.RowHeight = picHeight

    Next pic
    
End Sub

However, there's an error if the second list is incomplete, like in the example below: enter image description here

I do not know how to handle that error. I've tried:

  • On Error Resume Next - Doesn't work, because it skips just one line instead of the whole loop iteration.
  • On Error Go To Label - Doesn't work either. I can put "Skip:" at the end of the loop, but for some reason it'll only work once. I've seen other replies mentioning putting in an additional Resume, but then it doesn't compile.

What is the proper syntax to handle an error in this situation?

Jakub Szyguła
  • 29
  • 1
  • 1
    `Dim rng As Range`, `Set rng = Range("C2:C11").Find(picDestination, LookIn:=xlValues, LookAt:=xlWhole)`, `If Not rng Is Nothing Then` ... `End If`. You need to test if the `Find` succeeded, and only if it succeeded work with the `.Address` of the found cell. Or in this case, you don't need `.Address`. Just refer to `rng.Offset(0,1)`. – BigBen Jul 03 '23 at 12:35

0 Answers0