Lookup函数在工作表正常运行，VBA调用WorksheetFunction时返回#VALUE错误

你的问题出在VBA里直接用Application.WorksheetFunction.Lookup处理数组运算的逻辑和工作表函数不同：工作表中的LOOKUP会自动忽略数组里的错误值，但VBA的WorksheetFunction版本对错误更敏感，当1/(region <> "")生成包含错误值的数组时，就会触发#VALUE错误。

下面给两个可行的解决办法：

方法一：改用Application.Lookup #

把Application.WorksheetFunction.Lookup换成Application.Lookup，它是后期绑定的调用方式，返回变体类型，能正确处理带错误值的数组：

Function LEFTMOST_NOBLANK(region As Range) As Variant
    LEFTMOST_NOBLANK = Application.Lookup(2, 1 / (region <> ""), region)
End Function

方法二：手动遍历区域（更直观可控） #

如果不想依赖工作表函数的特性，可以直接遍历区域单元格，找到最左侧的非空单元格：

Function LEFTMOST_NOBLANK(region As Range) As Variant
    Dim cell As Range
    LEFTMOST_NOBLANK = "" ' 默认返回空值，应对区域全空的情况
    
    For Each cell In region
        If cell.Value <> "" Then
            LEFTMOST_NOBLANK = cell.Value
            Exit For ' 找到第一个非空单元格就退出，保证是最左侧的
        End If
    Next cell
End Function

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