Find MAX in Column Range ignoring Error values using Evaluate Syntax

Question

I have defined a range called Rng. The Columns(2) of Rng contains numbers and #N/A error values. I want to find the MAX value in this column and then compare if it is >0.

Col A    Col B
--------------
ansdlsd  1
sdlsds   1
lgdfkf   2
fdlksd   2
sdlsdks  3
sds sds  5
sdlkjq   #N/A
eowmf    #N/A
pwpwo    #N/A
qwdds    #N/A
ospw     #N/A
dlsls    #N/A
qpdmf    #N/A

I tried using the shorthand syntax for Application.Evaluate i.e. [], but the code returns a Type Mismatch Error 13 - (Error 2029 in Watch Window).

If [MAX(IF(NOT(ISNA(Rng.Columns(2))),Rng.Columns(2)))] > 0 Then
    do something...
End If

I also tried :

If Application.Aggregate(4, 6, Rng.Columns(2), 1) > 0 Then...

got error : Error 2015 : Variant/Error.

I am able to get the MAX using the Excel Array Formula (Ctrl+Shft+Entr) in a worksheet:

{=MAX(IF(NOT(ISNA(B2:B14)),B2:B14))}

I suppose it has to do with the Rng.Columns(2) not being evaluated correctly by the [] brackets.

Or is something else the cause of this error?

Answer 1

Try this:

Sub GetMax()

Dim rng As Range, lr As Long
With Sheet1 'Change accordingly
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A1:B" & lr)
    If Application.Aggregate(4, 6, rng.columns(2)) > 0 Then
        Debug.Print "Max is higher than 0"
    Else
        Debug.Print "Max is not higher than 0"
    End If
End With

End Sub

If you want to use .Evaluate you can change this line: If Application.Aggregate(4, 6, rng.columns(2)) > 0 Then, for this: If .Evaluate("AGGREGATE(4,6," & rng.Columns(2).Address & ")") > 0 Then

The reason this works is that we can utilize MAX within the AGGREGATE function and tell it to ignore error values.

---

Why did your attempts not succeed:

The reason your attempt with [] failed is because you cannot use these brackets to evaluate a formula with variables. You'll need to actually use Application.Evaluate. Secondly, you'll need to feed the formula the actual address. I've implemented the .Evaluate version in my answer above.

The reason your attempt with AGGREGATE raised an error is because you needed to omit the last parameter within the MAX function of AGGREGATE. If you would try it on a worksheet, it would also raise a #VALUE error. You simply don't need to input a index number since MAX, as the name suggests, returns just one value.

Answer 2

for those who have old ms excel version (<2010) that not supports AGGREGATE worksheetfunction:

Sub GetMax_oldstyle()
    Dim rng$, lr As Long
    With Sheet1 'Change accordingly
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        rng = .Range("A1:B" & lr).Columns(2).Address(0, 0)
        If .Evaluate("MAX(IF(NOT(ISNA(" & rng & "))," & rng & "))") > 0 Then
            Debug.Print "Max is higher than 0"
        Else
            Debug.Print "Max is not higher than 0"
        End If
    End With
End Sub

test: