Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate - Sumproduct GRRR
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!A 79,2) &"03"&MainListing!B79)*(iMIS_DATA!C2:C5802=MainLis ting! 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(lngActiv ityNum) lngFunctionNum = rFunctionNum & ":" & Left (rFunctionNum, 1) & wb0.Worksheets ("iMIS_DATA").UsedRange.Rows.Count Set rngFunctionNum = Workbooks ("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunct ionNum) lngFunctionAtts = rFunctionAtts & ":" & Left (rFunctionAtts, 1) & wb0.Worksheets ("iMIS_DATA").UsedRange.Rows.Count Set rngFunctionAtts = Workbooks ("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunct ionAtts) '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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate - Sumproduct GRRR
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=MainLi sting! 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(lngActi vityNum) lngFunctionNum = rFunctionNum & ":" & Left (rFunctionNum, 1) & wb0.Worksheets ("iMIS_DATA").UsedRange.Rows.Count Set rngFunctionNum = Workbooks ("74020_1.xls").Sheets("iMIS_DATA").Range(lngFunc tionNum) 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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate - Sumproduct GRRR
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 M wrote: 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=MainL isting! 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. . . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
evaluate | Excel Worksheet Functions | |||
Why won't Sumproduct funciton evaluate this data | Excel Worksheet Functions | |||
evaluate #¡VALUE! and #!DIV/0! and other errors.... | Excel Worksheet Functions | |||
Disable auto-formatting dates.... GRRR! | Excel Discussion (Misc queries) | |||
Disable auto-formatting dates.... GRRR! | Excel Worksheet Functions |