Unable to get the Lookup property of the WorksheetFunction class

Question

Following code:

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

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.

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

Answer 1

Is this what you are trying?

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