CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2013
BellHopByDayAmetuerCoderByNigh
BellHopByDayAmetuerCoderByNigh

Reputation: 1167

VLOOKUP() Return 1st Non Null Or Non 0 Value

I am using VLOOKUP() to return values, but am hitting an issue where the 1st value may be NULL or 0

How can I use this function (or a similar one) to return the 1st NON Null or 0 value?

Application.WorksheetFunction.VLookup(filterCon, Sheets("Ace Up Sleeve").Range("A:P"), 11, False)

Upvotes: 1

Views: 157

Answers (1)

user4039065
user4039065

Reputation:

I would be using the AGGREGATE worksheet function to accomplish this on a worksheet. Taking that into VBA, you can use Application.Evaluate(...) on the same formula.

Dim filterCon As String, fr As Variant, lr As Long, vreturn As Variant

filterCon = "findthis"

With Worksheets("Ace Up Sleeve")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    fr = Application.Evaluate("aggregate(15, 6, row(1:" & lr & ")/('Ace Up Sleeve'!K1:K" & lr & "<>0), 1)")
    If Not IsError(fr) Then
        vreturn = .Cells(fr, "K").Value
        Debug.Print vreturn
    End If
End With

Upvotes: 2

Related Questions