Excel VBA, custom search with Evaluate(MAX(IF)) condition - mismatch error

Question

I'm writing a 'custom' search macro so that user can quickly find the latest date for a corresponding project in a worksheet full with titles of communications (emails, letters, notes, etc) for different projects.

The sheet has several columns, the ones of interest are B:B in which dates are listed and F:F in which the communications are listed.

The idea is the user enters a keyword via InputBox, the macro checks F:F for the communication with that keyword and checks B:B whether that communication has the latest date for this specific keyword. The search continues until one is found. The output is a MsgBox with the title and the corresponding date.

Everything works except the condition to find the latest date, (Evaluate("MAX(IF)")) gives me a mismatch error.

I've tried WorksheetFunction.Max and WorksheetFunction.Subtotal as well, but they do not give the latest date.

Edit: thanks a lot for the edit suggestions

Sub Proj_Find()

    Dim ProjName As Range
    Dim UserInput As String
    Dim LatestDate As Double

    UserInput = InputBox(Prompt:="Give the keyword for the searched project" & vbCrLf & "Entrez un mot clé pour le projet recherché", Title:="Latest Date Search", Default:=DefaultInputString)
    Set ProjName = Cells.Find(what:=UserInput, LookIn:=xlValues, lookAt:=xlPart)

    If Not ProjName Is Nothing Then
        LatestDate = Evaluate("MAX(IF(" & ProjName & " <>"",B3:B1000))")
        MsgBox "The last communication is '" & ProjName.Value & "' - dating " & ProjName.Offset(0, -4)
    Else
        MsgBox "Project not found         " & vbCrLf & "Projet non trouvé" 
    End If

End Sub

Shouldn't it be `Evaluate("MAX(IF(" & ProjName & " <>"""",B3:B1000))")`? — Vincent G, Aug 19 '19 at 14:51

score 2 · Answer 1 · answered Aug 19 '19 at 15:05

LatestDate = Evaluate("MAX(IF(" & ProjName & " <>"",B3:B1000))")

ProjName is a Range object; that & ProjName & concatenation is therefore implicitly Let-coercing the object into a String. Make that explicit:

LatestDate = Evaluate("MAX(IF(" & ProjName.Value & " <>"",B3:B1000))")

That formula yields Error 2029 though, i.e. a #NAME! worksheet error - you're getting a type mismatch because the returned Variant/Error can't be coerced into a Double:

Dim LatestDate As Double
LatestDate = CVErr(xlErrName) ' type mismatch

The #NAME! error is because the ProjName.Value isn't enclosed in quotes, so the Excel calc engine treats it as a named range, and fails to find it.

=MAX(IF(SomeName<>"",B3:B1000))
        ^^^^^^^^ needs quotes!

The solution is, as Slai suggests, to enclose the ProjName[.Value] in double quotes, and to properly escape them (i.e. double them up).

Neat, thank you for the clarification. I'll have to reread it again later to fully grasp it :) — Draygo, Aug 19 '19 at 15:12

Excel VBA, custom search with Evaluate(MAX(IF)) condition - mismatch error

1 Answers1