VBA Sumproduct for External Source

[Guest]

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

 

[Guest]

? 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.

 

[Bob Phillips]

I think that dropping the formula into a cell and then picking up the value
is the only way it will work. Excel can evaluate the formula over closed
workbooks, but VBA seems not to be able to.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

First off, cheers for spending the time to look at this.

I was wanting a userform that could tell someone the productivity for
whatever team on whatever day, and I created the productivity to be like a
list so that other sheets could easily query it with sumproducts or a pivot
table.

I guess if I can't calculate the information straight from the sheets I can
use a preset workbook to open when the userform opens, and closes when the
userform closes and have the calculations present in there.

Cheers

? 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

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