VBA为Range单元格添加自定义CusVlookup公式时触发错误的问题

咱们一步步拆解你代码里的问题，逐个修复：

1. 未定义/未赋值的Lr变量 #

你代码里用Lr来确定单元格范围，但这个变量既没声明，也没赋值（比如获取TestSheet的最后一行数据行号）。这是触发错误的核心原因——Excel根本不知道Lr代表什么数值。

2. 工作表引用的语法错误 #

公式里的Data'!A:B写法完全错误：正确的工作表引用规则是，表名无特殊字符时直接写Data!A:B；如果表名带空格/特殊字符，才需要用单引号包裹表名，比如'Data List'!A:B。你把单引号放在表名后面，会直接导致公式解析失败。

3. 不可靠的公式赋值方式 #

直接给Range赋值字符串公式（.Range(...) = "=CusVlookup(...)"）不是稳妥的做法，应该用.Formula属性来设置，这样Excel才能明确识别这是公式而非普通文本。

4. 多余且危险的Select操作 #

Sheets("Data").Select完全没必要，而且如果Data工作表被隐藏、重命名或者不存在，这行代码会直接触发错误。操作工作表根本不需要激活/选中，直接通过对象引用就能完成。

修正后的完整代码 #

Sub test()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Dim wsTest As Worksheet
    Dim Lr As Long ' 声明变量
    
    ' 直接引用工作表，避免Select操作
    Set wsTest = ThisWorkbook.Worksheets("TestSheet")
    
    ' 获取TestSheet中Z列的最后一行（根据你的公式引用Z2，这里以Z列数据行数为准）
    Lr = wsTest.Cells(wsTest.Rows.Count, "Z").End(xlUp).Row
    
    ' 正确设置公式：用.Formula属性，修正工作表引用语法
    wsTest.Range("A2:A" & Lr).Formula = "=CusVlookup(Z2,Data!A:B,2)"
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

' 补全自定义函数的逻辑（示例为多值拼接，你可根据需求调整）
Function CusVlookup(lookupval As Variant, LookupRange As Range, indexcol As Long) As String
    Dim x As Range
    Dim Result As String
    Result = ""
    
    ' 遍历查找范围的第一列，匹配目标值
    For Each x In LookupRange.Columns(1).Cells
        If x.Value = lookupval Then
            ' 拼接匹配结果（如果需要单值返回，直接赋值即可）
            If Result <> "" Then Result = Result & ", "
            Result = Result & x.Offset(0, indexcol - 1).Value
        End If
    Next x
    
    ' 指定函数返回值
    CusVlookup = Result
End Function

额外注意事项 #

• 确保Data工作表确实存在于当前工作簿，且表名拼写完全一致（Excel表名区分大小写）。
• 如果Data工作表名带空格或特殊字符，公式里的引用要改成'Data'!A:B（单引号完整包裹表名）。
• 自定义函数CusVlookup的逻辑要根据你的实际需求调整，比如如果是单值匹配，找到第一个结果后可以直接退出循环，不用继续遍历。

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