? cverr(xlErrRef)
Error 2023

I couldn't get your formula to work in code. I think you would need to open
the workbook you want to query.

--
Regards,
Tom Ogilvy


"PaulW" wrote:

Sub final()

ar1 = "'S:\Main Files\Daily Productivity\Current\[Current
Admin.xls]Data'!$A$1:$A$30000"
ar2 = "'S:\Main Files\Daily Productivity\Current\[Current
Admin.xls]Data'!$E$1:$E$30000"

ans = Calendar1.Value

If Not IsNumeric(ans) Then
ans = """" & ans & """"
End If
pct = Application.Evaluate("SUMPRODUCT((" & ar1 & "=" _
& ans & ")*(" & ar2 & "))")

With Label1
.Caption = Format(pct, "0%")
End With

End Sub

When this gets run it throws up a Run-Time error '13', and when I debug it
shows me that pct's value = Error 2023.
Anyway to get this to work? As a last resort I suppose I can enter the
formula into a new workbook then delete that workbook once it has the
answer...