Evaluate - Sumproduct GRRR

P

Paul M.

Hi Everyone,

Just having some problems with the evaluate, sumproduct
function in XL 10.

Within a Worksheets I can enter an array formula to
return the sumproduct of a series of columns of data
according to the criteria. I would rather that instead of
inserting the ArrayFormula, that I can insert the
calculated value. This Sheet has over 1400 rows of data,
which are populated by code which strips several
workbooks according to selection criteria.

1. Sheet 1 ["Main_Listing"] - Data Table
2. Sheet 2 ["iMIS_DATA"]- Data Import (ODBC)

This is the working Array Formula:
=SUMPRODUCT((iMIS_DATA!A2:A5802=LEFT(MainListing!A79,2)
&"03"&MainListing!B79)*(iMIS_DATA!C2:C5802=MainListing!
C79)*(iMIS_DATA!G2:G5802))

Where the first condition evaluates to :"VI034190"
Where the Second Condition evaluates to: "F006"
Where the cell value returns: 14

Following is the expert from the code, which is giving me
greif. I have found the previous posts helpful in
troubleshooting, but I can't get any further from here.
Rather than using the cell value, I have several
variables declared (which initially populated the cell
anyway). It keeps returning Error 2029 which I have
identified as a name error, but just can't figure out
why. I'm sure that it must be a declaration error. Any
help would be greatly appreciated. Note: I have seriusly
amputated this code, but I've made sure that all the
declarations are there, just some of the pre-calcs may
not. Where this is the case I have indicated the variable
value that is returned.
----------------------------------------------------------
Sub GenerateReportMain()
On Error Resume Next
Dim wb0 as Workbook
Dim rngActivityNum As Range
Dim rngFunctionNum As Range
Dim rngFunctionAtts As Range
Dim lngActivityNum As String
Dim lngFunctionNum As String
Dim lngFunctionAtts As String
Dim iFuncNum As String, iActivityNo as String

Set wb0 = Workbooks("74020_1.xls")
rActivityNum = "A2"
rFunctionNum = "C2"
rFunctionAtts = "G2"
lngActivityNum = rActivityNum & ":" & Left
(rActivityNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngActivityNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range(lngActivityNum)
lngFunctionNum = rFunctionNum & ":" & Left
(rFunctionNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunctionNum)
lngFunctionAtts = rFunctionAtts & ":" & Left
(rFunctionAtts, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionAtts = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunctionAtts)


'Where The following Variables Are Defined in truncated
code returned from a for i loop.
'iActivityNo = "VI034190"
'iFuncNum = "F006"
'Where Column A in the WS iMIS DATA, contains the
iActivityNo.
'Where Column C in the WS iMIS DATA, contains the iFuncNum
'Where Column G in the WS iMIS DATA, conatins the Value
to return (14).
'All Ranges are set to the same size by the UsedRange
property.

Debug.Print Evaluate("Sumproduct((" &
rngActivityNum.Address & "=" & iActivityNo & ")*(" _
& rngFunctionNum.Address & "=" & iFuncNum & ")
*(" _
& rngFunctionAtts.Address & "))")
----------------------------------------------------------

Returns - Error 2029

----------------------------------------------------------

Any help would be great, also if there is an easier way
to return the value from a conditional sum (with more
than one condition) using VB that would be great.

Thanks Again,

Paul M.
 
P

Paul M.

Thanks to anyone who may have been working on this one, i
figured it out.

Solution is to encase the declared variable in (""")
before and after the variable in the code.

Evaluate("Sumproduct((" & rngActivityNum.Address & "="""
& iActivityNo & """)*(" & rngFunctionNum.Address & " ="""
& iFuncNum & """)*(" & rngFunctionAtts.Address & "))")

This returns the value as a string as required.

Hope this helps someone else out.

Paul.
-----Original Message-----
Hi Everyone,

Just having some problems with the evaluate, sumproduct
function in XL 10.

Within a Worksheets I can enter an array formula to
return the sumproduct of a series of columns of data
according to the criteria. I would rather that instead of
inserting the ArrayFormula, that I can insert the
calculated value. This Sheet has over 1400 rows of data,
which are populated by code which strips several
workbooks according to selection criteria.

1. Sheet 1 ["Main_Listing"] - Data Table
2. Sheet 2 ["iMIS_DATA"]- Data Import (ODBC)

This is the working Array Formula:
=SUMPRODUCT((iMIS_DATA!A2:A5802=LEFT(MainListing!A79,2)
&"03"&MainListing!B79)*(iMIS_DATA!C2:C5802=MainListing!
C79)*(iMIS_DATA!G2:G5802))

Where the first condition evaluates to :"VI034190"
Where the Second Condition evaluates to: "F006"
Where the cell value returns: 14

Following is the expert from the code, which is giving me
greif. I have found the previous posts helpful in
troubleshooting, but I can't get any further from here.
Rather than using the cell value, I have several
variables declared (which initially populated the cell
anyway). It keeps returning Error 2029 which I have
identified as a name error, but just can't figure out
why. I'm sure that it must be a declaration error. Any
help would be greatly appreciated. Note: I have seriusly
amputated this code, but I've made sure that all the
declarations are there, just some of the pre-calcs may
not. Where this is the case I have indicated the variable
value that is returned.
--------------------------------------------------------- -
Sub GenerateReportMain()
On Error Resume Next
Dim wb0 as Workbook
Dim rngActivityNum As Range
Dim rngFunctionNum As Range
Dim rngFunctionAtts As Range
Dim lngActivityNum As String
Dim lngFunctionNum As String
Dim lngFunctionAtts As String
Dim iFuncNum As String, iActivityNo as String

Set wb0 = Workbooks("74020_1.xls")
rActivityNum = "A2"
rFunctionNum = "C2"
rFunctionAtts = "G2"
lngActivityNum = rActivityNum & ":" & Left
(rActivityNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngActivityNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range(lngActivityNum)
lngFunctionNum = rFunctionNum & ":" & Left
(rFunctionNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunctionNum)
lngFunctionAtts = rFunctionAtts & ":" & Left
(rFunctionAtts, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionAtts = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range (lngFunctionAtts)


'Where The following Variables Are Defined in truncated
code returned from a for i loop.
'iActivityNo = "VI034190"
'iFuncNum = "F006"
'Where Column A in the WS iMIS DATA, contains the
iActivityNo.
'Where Column C in the WS iMIS DATA, contains the iFuncNum
'Where Column G in the WS iMIS DATA, conatins the Value
to return (14).
'All Ranges are set to the same size by the UsedRange
property.

Debug.Print Evaluate("Sumproduct((" &
rngActivityNum.Address & "=" & iActivityNo & ")*(" _
& rngFunctionNum.Address & "=" & iFuncNum & ")
*(" _
& rngFunctionAtts.Address & "))")
--------------------------------------------------------- -

Returns - Error 2029

--------------------------------------------------------- -

Any help would be great, also if there is an easier way
to return the value from a conditional sum (with more
than one condition) using VB that would be great.

Thanks Again,

Paul M.
.
Click to expand...
 
D

Dave Peterson

One thing I just learned is that you can prefix evaluate with a worksheet and
it'll use the ranges on that worksheet (if all the ranges were on that one
worksheet).

rngactivitynum.parent.evaluate(....)



Paul said:
I thought that I'd just keep adding to my old post, as I
noticed that the rngX.address refers to the active
worksheet unless suffixed by (external:=True). See
amended code.

Evaluate("Sumproduct((" & rngActivityNum.Address
(external:=True) & "=""" & iActivityNo & """)*(" &
rngFunctionNum.Address(external:=True) & " =""" &
iFuncNum & """)*("& rngFunctionAtts.Address
(external:=True) & "))")

Enjoy.
-----Original Message-----
Thanks to anyone who may have been working on this one, i
figured it out.

Solution is to encase the declared variable in (""")
before and after the variable in the code.

Evaluate("Sumproduct((" & rngActivityNum.Address & "="""
& iActivityNo & """)*(" & rngFunctionNum.Address & " ="""
& iFuncNum & """)*(" & rngFunctionAtts.Address & "))")

This returns the value as a string as required.

Hope this helps someone else out.

Paul.
-----Original Message-----
Hi Everyone,

Just having some problems with the evaluate, sumproduct
function in XL 10.

Within a Worksheets I can enter an array formula to
return the sumproduct of a series of columns of data
according to the criteria. I would rather that instead of
inserting the ArrayFormula, that I can insert the
calculated value. This Sheet has over 1400 rows of data,
which are populated by code which strips several
workbooks according to selection criteria.

1. Sheet 1 ["Main_Listing"] - Data Table
2. Sheet 2 ["iMIS_DATA"]- Data Import (ODBC)

This is the working Array Formula:
=SUMPRODUCT((iMIS_DATA!A2:A5802=LEFT(MainListing!A79,2)
&"03"&MainListing!B79)*(iMIS_DATA!C2:C5802=MainListing!
C79)*(iMIS_DATA!G2:G5802))

Where the first condition evaluates to :"VI034190"
Where the Second Condition evaluates to: "F006"
Where the cell value returns: 14

Following is the expert from the code, which is giving me
greif. I have found the previous posts helpful in
troubleshooting, but I can't get any further from here.
Rather than using the cell value, I have several
variables declared (which initially populated the cell
anyway). It keeps returning Error 2029 which I have
identified as a name error, but just can't figure out
why. I'm sure that it must be a declaration error. Any
help would be greatly appreciated. Note: I have seriusly
amputated this code, but I've made sure that all the
declarations are there, just some of the pre-calcs may
not. Where this is the case I have indicated the variable
value that is returned.
--------------------------------------------------------
Click to expand...
-
-
Sub GenerateReportMain()
On Error Resume Next
Dim wb0 as Workbook
Dim rngActivityNum As Range
Dim rngFunctionNum As Range
Dim rngFunctionAtts As Range
Dim lngActivityNum As String
Dim lngFunctionNum As String
Dim lngFunctionAtts As String
Dim iFuncNum As String, iActivityNo as String

Set wb0 = Workbooks("74020_1.xls")
rActivityNum = "A2"
rFunctionNum = "C2"
rFunctionAtts = "G2"
lngActivityNum = rActivityNum & ":" & Left
(rActivityNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngActivityNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range (lngActivityNum)
lngFunctionNum = rFunctionNum & ":" & Left
(rFunctionNum, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionNum = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range (lngFunctionNum)
lngFunctionAtts = rFunctionAtts & ":" & Left
(rFunctionAtts, 1) & wb0.Worksheets
("iMIS_DATA").UsedRange.Rows.Count
Set rngFunctionAtts = Workbooks
("74020_1.xls").Sheets("iMIS_DATA").Range (lngFunctionAtts)


'Where The following Variables Are Defined in truncated
code returned from a for i loop.
'iActivityNo = "VI034190"
'iFuncNum = "F006"
'Where Column A in the WS iMIS DATA, contains the
iActivityNo.
'Where Column C in the WS iMIS DATA, contains the iFuncNum
'Where Column G in the WS iMIS DATA, conatins the Value
to return (14).
'All Ranges are set to the same size by the UsedRange
property.

Debug.Print Evaluate("Sumproduct((" &
rngActivityNum.Address & "=" & iActivityNo & ")*(" _
& rngFunctionNum.Address & "=" & iFuncNum & ")
*(" _
& rngFunctionAtts.Address & "))")
--------------------------------------------------------
Click to expand...
-
-

Returns - Error 2029

--------------------------------------------------------
Click to expand...
-
-

Any help would be great, also if there is an easier way
to return the value from a conditional sum (with more
than one condition) using VB that would be great.

Thanks Again,

Paul M.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Top