无法获取 WorksheetFunction 类的 Lookup 属性 [英] Unable to get the Lookup property of the WorksheetFunction class

问题描述

以下代码:

For i = startRow To startRow + (nRows - 1)    
Set lookUp = Range(col & i)
Range("I" & i) = IIf(WorksheetFunction.lookUp(lookUp, Sheets("P&C curves").Columns("A")) = lookUp, 1, 0)
Range("J" & i) = IIf(WorksheetFunction.lookUp(lookUp, Sheets("G Curve").Columns("A")) = lookUp, 1, 0)      
Next i

在代码中，我第一次调用 WorksheetFunction.lookUp 一切正常，即它在找到某些内容的地方用 1 填充列，否则它会放一个零，但第二行没有工作.我收到错误:

In the code, the first time I call WorksheetFunction.lookUp everything works fine, namely it fills the column with 1 where it find something, else it puts a zero, but the second line does not work. I receive the error:

无法获取工作表函数类的查找属性.

Unable to get lookup property of the worksheetfunction class.

为什么会这样?如果我尝试直接在 Excel 工作表中使用相同的逻辑，它会起作用吗?

Why does this happen? If I try to use the same logic directly in the excel sheet it works?

非常感谢您的帮助...非常感谢

Would really appreciate any help...thanks a lot

推荐答案

这是您要尝试的吗?

For I = startRow To startRow + (nRows - 1)
    Set Lookup = Range(col & I)

    On Error Resume Next

    Ret = WorksheetFunction.Lookup(Lookup, Sheets("P&C curves").Columns("A"))
    If Err.Number <> 0 Then
        Range("I" & I) = 0
        Err.Clear
    Else
        If Ret = Lookup Then Range("I" & I) = 1 Else Range("I" & I) = 0
    End If

    Ret = WorksheetFunction.Lookup(Lookup, Sheets("G Curve").Columns("A"))
    If Err.Number <> 0 Then
        Range("J" & I) = 0
        Err.Clear
    Else
        If Ret = Lookup Then Range("J" & I) = 1 Else Range("J" & I) = 0
    End If
Next I

这篇关于无法获取 WorksheetFunction 类的 Lookup 属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！