0

I'm rather green with programming, and I found some code on stackoverflow. Original source: Insert Dynamically in Excel using image from server path It works, but if the loop gets to an empty cell, it crashes. (Run-time error '1004') I believe that skipping the empty cells should avoid the issue. I'm thinking it's something simple, but I can't find it online. I tried using GoTo to skip past the error but it's seems to skip everything. I apologize if I'm not including enough information.

Sub ImageUpdateF()
' inserts the picture files listed in column G into the 
' column G cells in the workbook.

Dim cell          As Range
Dim sFile         As String
Dim shpPic        As Shape
Dim ws            As Worksheet: Set ws = ActiveSheet

With ws
    For Each cell In .Range(.Range("G2"), .Cells(.Rows.Count, "G").End(xlUp))
        sFile = cell.Text

        If ActiveCell.Value = vbNullString Then
                GoTo NextIteration
        ElseIf Len(Dir(sFile)) Then
            Set shpPic = .Shapes.AddPicture2(sFile, msoFalse, msoTrue, 0, 0, -1, -1, 1)
            shpPic.LockAspectRatio = msoTrue

            With cell.Offset(, 0)
                If shpPic.Height > .Height Then shpPic.Height = .Height
                If shpPic.Width > .Width Then shpPic.Width = .Width

                shpPic.Top = .Top + .Height / 2 - shpPic.Height / 2
                shpPic.Left = .Left + .Width / 2 - shpPic.Width / 2
            End With
        End If
'label
NextIteration:

    Next cell
End With

End Sub

Array setup

| Column F | Column G | Column H

| -------- | --------- | --------

| file1 loc| file1.2 loc| file1.3 loc

| file2 loc| Empty | Empty

| file3 loc| file3.2 loc| Empty

I also tried putting an Next cell statement after the initial If statement, but I got an error. Like I said, I'm pretty green.

DavidO
  • 1
  • 2
  • 1
    FYI, I've edited the tags on your question: because the site is so busy, it's important to tag questions with things that will get the attention of the people most able to help you. In this case, that's people who know about VBA and Excel, not people who know about loops and if statements as abstract concepts. – IMSoP May 03 '23 at 13:38

1 Answers1

2

You just need to check the cell value (not the activecell as that doesn't change and could be any cell) and then you can dispense with the Goto, which is not generally considered good practice.

Sub ImageUpdateF()
' inserts the picture files listed in column G into the
' column G cells in the workbook.

Dim cell          As Range
Dim sFile         As String
Dim shpPic        As Shape
Dim ws            As Worksheet: Set ws = ActiveSheet

With ws
    For Each cell In .Range(.Range("G2"), .Cells(.Rows.Count, "G").End(xlUp))
        If Not cell.Value = vbNullString Then
            sFile = cell.Text 'do you mean text and not value?
            Set shpPic = .Shapes.AddPicture2(sFile, msoFalse, msoTrue, 0, 0, -1, -1, 1)
            shpPic.LockAspectRatio = msoTrue

            With cell.Offset(, 0) 'this offset looks redundant
                If shpPic.Height > .Height Then shpPic.Height = .Height
                If shpPic.Width > .Width Then shpPic.Width = .Width
                shpPic.Top = .Top + .Height / 2 - shpPic.Height / 2
                shpPic.Left = .Left + .Width / 2 - shpPic.Width / 2
            End With
        End If
    Next cell
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Thanks @SJR. It's putting in the images now. It's still outputting the same error message though. On your comments: The cell.Text & cell.Value work that same. I don't know enough on offsets to know if it's redundant. Note: I did change the For statement to: "For Each cell In .Range(.Range("F2:H2"), .Cells(.Rows.Count, "F").End(xlUp))" to read an array of cells. BTW, is there a something I can add that will delete the text from the cell after reading it? I'm hoping that's possible because it's a file that will be updated regularly. – DavidO May 04 '23 at 12:41
  • I found the .Clear function to clear out completed picture inserts and (after tweaking my table) everything is working great! No errors. @SJR thanks again for you help! – DavidO May 04 '23 at 13:31