Excel VBA For循环触发Run-Time Error 1004：应用程序定义错误

问题现象 #

运行包含For循环的VBA Sub过程时触发Run-Time Error 1004（应用程序定义或对象定义错误），移除For循环后错误消失。注：运行该Sub时循环能输出预期结果，但仍会返回错误。

完整代码 #

Sub MondayBreakfast_Ingredients()

'Select the lookup value
    Dim Breakfast As String
    Breakfast = Worksheets(1).Range("C3").Value

'Select the Ingredients table
    Dim startrow As Long
    startrow = 12

    Dim LR As Long
    LR = Worksheets(2).Cells(startrow, 2).End(xlDown).Row
    
    Dim recipe_names As Range
    Set recipe_names = Worksheets(2).Range("B" & startrow & ":B" & LR)
    
'Number of columns to loop through
    Dim currentrow As Long
    currentrow = Application.WorksheetFunction.Match(Breakfast, recipe_names, 0) + startrow - 1
    
    Dim numbercolumns As Long
    numbercolumns = Worksheets(2).Cells(currentrow, 2).End(xlToRight).Column
    
'Create loop
    For StartNumber = 2 To numbercolumns
        
        rownumber = StartNumber + 7
    
        Worksheets(1).Cells(rownumber, "E").Value = _
        Application.WorksheetFunction.VLookup(Breakfast, recipe_names.CurrentRegion, StartNumber, False)
        
    Next StartNumber
    
End Sub

报错代码段 #

'Create loop
    For StartNumber = 2 To numbercolumns
        
        rownumber = StartNumber + 7
    
        Worksheets(1).Cells(rownumber, "E").Value = _
        Application.WorksheetFunction.VLookup(Breakfast, recipe_names.CurrentRegion, StartNumber, False)
        
    Next StartNumber

问题原因 #

1. 列数计算逻辑错误：numbercolumns = Worksheets(2).Cells(currentrow, 2).End(xlToRight).Column 取的是当前行从B列向右到最后一个非空单元格的列号，这个范围可能超出recipe_names.CurrentRegion（食材表的实际有效区域）的列数。当StartNumber循环到大于CurrentRegion的列数时，VLookup的第三个参数超出有效范围，触发1004错误。
2. 未显式声明变量：StartNumber和rownumber未声明，属于变体类型，可能引发意外的类型转换问题。
3. VLookup无错误捕获：使用Application.WorksheetFunction.VLookup时，参数无效或查找失败会直接抛出运行时错误，而非返回错误值。

解决方案 #

1. 修正列数计算逻辑 #

将numbercolumns的赋值改为基于CurrentRegion的实际列数，确保循环范围不超出查找区域：

'替换原numbercolumns的赋值行
Dim numbercolumns As Long
numbercolumns = recipe_names.CurrentRegion.Columns.Count

2. 改用容错性更强的VLookup调用 #

用Application.VLookup替代Application.WorksheetFunction.VLookup，并添加错误判断，避免因查找失败触发错误：

For StartNumber = 2 To numbercolumns
    rownumber = StartNumber + 7
    Dim lookupResult As Variant
    lookupResult = Application.VLookup(Breakfast, recipe_names.CurrentRegion, StartNumber, False)
    '仅在查找成功时赋值
    If Not IsError(lookupResult) Then
        Worksheets(1).Cells(rownumber, "E").Value = lookupResult
    End If
Next StartNumber

3. 强制变量声明 #

在模块顶部添加Option Explicit，强制所有变量必须声明，避免未声明变量带来的潜在问题：

Option Explicit

Sub MondayBreakfast_Ingredients()
'...原代码内容...
End Sub

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