如何在For循环中正确使用Worksheets.Cells并解决取值为空问题？

修正后的可运行代码 #

Sub GetCobraData()
    Dim wsRollUp As Worksheet
    Dim wsCobra As Worksheet
    Dim wsSheet1 As Worksheet
    Dim y As Long, x As Long, a As Long
    Dim reference As String
    Dim myrange As Range
    Dim marks1 As Variant, marks2 As Variant
    
    ' 提前绑定工作表，避免重复查找提升效率
    Set wsRollUp = ThisWorkbook.Worksheets("Software-Roll Up (VSL)")
    Set wsCobra = ThisWorkbook.Worksheets("Monthly Cobra Data")
    Set wsSheet1 = ThisWorkbook.Worksheets("Sheet1")
    
    ' 确保循环次数为数值类型
    y = CLng(wsRollUp.Range("G1").Value) - 1
    ' 动态获取查找范围的最后一行，避免无效超大范围
    Set myrange = wsCobra.Range("J2:O" & wsCobra.Cells(wsCobra.Rows.Count, "J").End(xlUp).Row)
    
    For x = 0 To y
        a = 8 + 2 * x
        ' 明确获取单元格的内容，这是解决reference空值的核心
        reference = wsRollUp.Cells(a, 2).Value
        
        ' 处理VLookup找不到匹配值的情况，避免代码崩溃
        On Error Resume Next
        marks1 = Application.VLookup(reference, myrange, 5, False)
        marks1 = IIf(IsError(marks1), "", marks1)
        
        marks2 = Application.VLookup(reference, myrange, 6, False)
        marks2 = IIf(IsError(marks2), "", marks2)
        On Error GoTo 0
        
        ' 写入结果到Sheet1
        wsSheet1.Cells(4 + x, 2).Value = marks1
        wsSheet1.Cells(4 + x, 3).Value = marks2
    Next x
End Sub

核心问题说明与优化点 #

• 解决reference空值的关键：原代码中reference = Worksheets(...).Cells(a,2)是把单元格对象本身赋值给变量，而非单元格的内容。必须添加.Value（或.Text，按需选择）才能获取单元格的字符串/数值，这是导致变量为空的直接原因。
• 提升代码效率：把工作表对象提前绑定、查找范围移到循环外定义，避免每次循环重复执行Worksheets()和大范围引用操作，减少资源消耗。
• 错误防护：用Application.VLookup替代WorksheetFunction.VLookup，配合错误处理，在找不到匹配值时返回空值而非直接报错崩溃。
• 显式变量声明：所有变量提前用Dim声明，避免隐式类型转换带来的潜在问题，符合VBA最佳实践。

单元格递增的替代写法 #

如果想要更直观的递增逻辑，除了Cells(a,2)，还可以用Offset方法：

' 从B8开始，每次向下偏移2行，和a=8+2*x效果完全一致
reference = wsRollUp.Range("B8").Offset(2 * x, 0).Value

这种写法同样能轻松实现单元格位置递增，且代码可读性更强。

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