Excel VBA: FOR…on error goto NEXT

Question

I use a For ... Next loop to insert pictures to an Excel worksheet. The name of the picture comes from column B. Sometimes, there is no corresponding picture on the hard disk. In such cases, Excel will throw an error message and stop running the codes. First, I tried "On Error Resume Next". This is not good because all the codes after the error occurs are executed. So, I tried the following code to try to avoid running codes when the picture is not found. This, however, only catches the first error. Excel still throws an error message ("unable to get the insert property of the pictures class") when the second time a picture is not found. All I want is if an error occurs, Excel would skip the rest of the code and go to the next case. How can this be done? Thanks for any help.

......
On Error GoTo gotoNext
For Each cell In rng
......
Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & _
Replace(cell.Value, "/", "-") & ".jpg") 'when the picture is not found, Excel throws an error
......
gotoNext:
Err.Clear
Next

Answer 1

You can quickly check the existence of the image file with the Dir command. It will return the name of the file (hence a returned string length greater than zero) if it is found.

For Each cell In rng
  if cbool(len(dir(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg"))) then
    Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & "\" & Replace(cell.Value, "/", "-") & ".jpg")
  end if
next cell