CODEWITHSUNDEEP
excelvbaworksheet-function
Eva
Eva

Reputation: 917

Unable to get the Lookup property of the WorksheetFunction class

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

Upvotes: 0

Views: 6056

Answers (2)

Gurpreet Singh Jabbal
Gurpreet Singh Jabbal

Reputation: 21

In this case error is coming due to lookup value is not available in array. IF we write formula in excel file and we don't have lookup value in array then it simply return #N/A but in VBA it return Unable to get lookup property of the worksheetfunction class. instead of #N/A. So, You need to add On error resume next on top of it.

Second way is instead formula you can use loop

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149325

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

Upvotes: 0

Related Questions