0

When running the code to identify if a picture/image exists in a cell, Excel keeps crashing and doesn't run the code even when I am running it step by step.

I've tried with shape.name, but same result. Can you guys think of a work around?. My Spreadsheet contains 530 rows, around 20% of them don't have pictures and I need to add one to every 'product' I apply the following loop from row = 2 to row = 530

I tried alternatives based on this page, but code keeps crashing: How to check whether any of shapes exists?

Dim rngCell As Range
Dim shp    As Shape
Dim objPic As Picture
Dim Pic_bool As Boolean 
Dim strFolder As String
Dim strFileName As String

Prod_name = Range("F" & i)
strFolder = "C:Documents/Pictures"

        strFileName = strFolder & Prod_name & ".png"
        Pic_bool = False
        Set rngCell = Range("H" & i)

            For Each shp In ActiveSheet.Shapes    'Excel crashes on this line
                If shp.TopLeftCell.Address = rngCell.Address Then
                If shp.Name = strFileName Then
                Pic_bool = True
                Exit For
                End If
            Next shp

If Pic_bool <> True Then
'Insert picture on Cell H&i
    If Dir(strFileName) <> "" Then
    Range("H" & i) = "No Picture Found"
    End If
End If

Expected Result is every row in column H validates if a picture already exist, otherwise it inserts a picture

BigBen
  • 46,229
  • 7
  • 24
  • 40
Federico
  • 13
  • 2
  • 1
    `strFolder = "C:Documents/Pictures"` is this your actual path? Looks off... – Tim Williams Apr 04 '19 at 02:49
  • Also assuming there should be a backslash before you concatenate `Prod_name`. And your shapes are named after the filepath? – BigBen Apr 04 '19 at 02:51
  • Couple of things **1.** The value of `i` is `0`. That will give you an error. **2.** There is an `End If` missing for `If shp.TopLeftCell.Address = rngCell.Address Then` – Siddharth Rout Apr 04 '19 at 03:48
  • The path of the pictures is an example. Real path is longer and has all backlashes. – Federico Apr 04 '19 at 04:55
  • please omit `If shp.Name = strFileName Then` as this is one of the variations I was trying as a work around. i = 2 is declared in the loop from i=2 to 530. I used to use this macro without issues a couple of weeks ago, but now is crashing on the same line `For Each shp In ActiveSheet.Shapes`. I wonder if it's crashing because it's consuming to much memory in the way it is analyzing the pictures – Federico Apr 04 '19 at 05:04
  • 1
    Can you please share the exact code so that we do not waste ours and your time guessing what the problem is – Siddharth Rout Apr 04 '19 at 05:45

0 Answers0