Excel VBA循环遇空单元格报错，如何实现跳过进入下一次迭代？

原代码用于将G列指定路径的图片插入对应单元格，但循环到空单元格时会触发运行时错误'1004'。尝试用GoTo语句跳过无效，以下是可行的解决方法：

原问题代码 #

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

表格结构（中文） #

问题原因与修正方案 #

原代码的核心问题是判断空单元格时误用了ActiveCell.Value——循环遍历的是cell变量，而ActiveCell不一定等于当前循环的单元格，导致判断失效。

修正后的代码（简洁版） #

Sub ImageUpdateF()
' 将G列列出的图片文件插入对应单元格

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 Trim(cell.Value) = "" Then
            GoTo NextIteration
        End If

        ' 验证文件存在后插入图片
        If Len(Dir(sFile)) Then
            Set shpPic = .Shapes.AddPicture2(sFile, msoFalse, msoTrue, 0, 0, -1, -1, 1)
            shpPic.LockAspectRatio = msoTrue

            With cell
                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
NextIteration:
    Next cell
End With

End Sub

关键修改点 #

• 将空单元格判断条件改为Trim(cell.Value) = ""，确保判断的是当前循环的单元格，同时过滤掉仅含空格的无效单元格
• 拆分判断逻辑：先处理空单元格跳过，再验证文件路径有效性，代码更清晰

内容的提问来源于stack exchange，提问作者DavidO