P
PaulW
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...
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...