Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumproduct for External Source
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumproduct for External Source
? 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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumproduct for External Source
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) "PaulW" wrote in message ... 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumproduct for External Source
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 "Tom Ogilvy" wrote: ? 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External data source | Excel Discussion (Misc queries) | |||
ListBox external source | Excel Programming | |||
External Database Source | Excel Worksheet Functions | |||
UNC for external data source | Excel Discussion (Misc queries) | |||
External Data Source | Excel Programming |