Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another rascally VBA SUMPRODUCT issue
G'day all
This is not a biggy as I am only playing with this workbook. I am not a big fan of nesting hundreds of formulas, regardless if I can turn Auto-Calc off or not; Over my works under-resourced, over-stretched network, it can take forever to make a single recalculation so I look to VBA to do most in order to side-step nesting. Moving Forward: This evaluates to cCell = EMPTY Sub Update_Col_C() Dim sSht As Worksheet Dim mysYear As Range, mysMonth As Range, mysEmp As Range, mysExp As Range, mysAmt As Range Dim tSht As Worksheet Dim mytYear As Range, mytMonth As Range, mytEmp As Range, mytExp As Range Dim c1 As Range, cCell As Range Set sSht = Worksheets("Expenses") Set mysYear = sSht.Range("$A$2:$A$5000") Set mysMonth = sSht.Range("$B$2:$B$5000") Set mysEmp = sSht.Range("$D$2:$D$5000") Set mysExp = sSht.Range("$E$2:$E$5000") Set mysAmt = sSht.Range("$H$2:$H$5000") Set tSht = Worksheets("Summary") Set mytYear = tSht.Range("$B$1") Set mytMonth = tSht.Range("$D$1") Set mytEmp = tSht.Range("$C$2") ''used syntax that failed envoking ( Type MisMatch Error 13 ): ''Set mytexp = ccell.offset(0, -2).value Set c1 = tSht.Range("C3:C134") For Each cCell In c1 With cCell .Value = WorksheetFunction.SumProduct((mysYear = mytYear) * (mysMonth = mytMonth) * (mysEmp = mytEmp) * (mysExp = mytExp) * (mysAmt)) End With Next cCell End Sub As always, thoughts, and or critique welcomed Cheers Mick. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another rascally VBA SUMPRODUCT issue
"Vacuum Sealed" wrote:
''used syntax that failed envoking ( Type MisMatch Error 13 ): ''Set mytexp = ccell.offset(0, -2).value Probably because ccell is not set before that statement, AFAIK. But perhaps you meant to put or apply this comment inside the For Each statement. In that case, the problem is that ccell.offset(0, -2).value is a value (number, text, etc), not an object. The Set statement is specifically for creating object references. Perhaps you should simply omit the word Set; that is, make it a normal assignment statement. But since you declared mytexp as Range, perhaps you should write: Set mytexp = ccell.offset(0, -2) "Vacuum Sealed" wrote: .Value = WorksheetFunction.SumProduct((mysYear = mytYear) * (mysMonth = mytMonth) * (mysEmp = mytEmp) * (mysExp = mytExp) * (mysAmt)) Syntax like (mysYear = mytYear) works in Excel because Excel creates an array of values. Likewise for syntax like x*y, where x and y are ranges or arrays. VBA does not have that capability. Instead, you must create a VBA array of values yourself. Also note that in Excel, TRUE is 1; but in VBA, True is -1. In your example, it makes no difference because you are multiplying an even number (4) of conditional expressions. But if you multipy an odd number (e.g. 3), the result will be -1 instead of 1, which is probably not what you intended. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another rascally VBA SUMPRODUCT issue
On 02/07/2012 14:37, Vacuum Sealed wrote:
Sub Update_Col_C() Dim sSht As Worksheet Dim mysYear As Range, mysMonth As Range, mysEmp As Range, mysExp As Range, mysAmt As Range Dim tSht As Worksheet Dim mytYear As Range, mytMonth As Range, mytEmp As Range, mytExp As Range Dim c1 As Range, cCell As Range Set sSht = Worksheets("Expenses") Set mysYear = sSht.Range("$A$2:$A$5000") Set mysMonth = sSht.Range("$B$2:$B$5000") Set mysEmp = sSht.Range("$D$2:$D$5000") Set mysExp = sSht.Range("$E$2:$E$5000") Set mysAmt = sSht.Range("$H$2:$H$5000") Set tSht = Worksheets("Summary") Set mytYear = tSht.Range("$B$1") Set mytMonth = tSht.Range("$D$1") Set mytEmp = tSht.Range("$C$2") ''used syntax that failed envoking ( Type MisMatch Error 13 ): ''Set mytexp = ccell.offset(0, -2).value Set c1 = tSht.Range("C3:C134") For Each cCell In c1 With cCell .Value = WorksheetFunction.SumProduct((mysYear = mytYear) * (mysMonth = mytMonth) * (mysEmp = mytEmp) * (mysExp = mytExp) * (mysAmt)) End With Next cCell End Sub It won't like this mess at all. I think you are confused and I cannot guess what your intentions are. The syntax of sumproduct is (range, range, range) with at least two identical ranges separted by commas. You possibly want to use formula array to do this computation eg. Range("I9").FormulaArray = "=SUM(R[-4]C[-3]:RC[-3],R[-4]C[-2]:RC[-2])" You might be able to capture it with the macro recorder on a good day with the wind blowing in the right direction. -- Regards, Martin Brown |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT issue | Excel Discussion (Misc queries) | |||
SUMPRODUCT Issue | Excel Worksheet Functions | |||
I believe this is a SUMPRODUCT issue | Excel Worksheet Functions | |||
Issue with sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT issue | Excel Worksheet Functions |