Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT IN VBA
hello,
Using the search, I have tried to find a way to program SUMPRODUCT in VBA, but I am not sure I am using it properly. Can you check what I am doing wrong. I do not want to copy the formula in Excel, i am after the final value. Thanks Caroline For i = 1 To 200 Dim EvalRange As Range Set EvalRange = Range(Cell1.Offset((i - 1) * 23, 2), Cell1.Offset(i * 23 - 1, 2)) Dim sumrange As Range Set sumrange = Range(Cell1.Offset((i - 1) * 23, X), Cell1.Offset(i * 23 - 1, X)) For j = 1 To 3 minAge = Range("Ageband")(j, 0).Value maxAge = Range("Ageband")(j, 1).Value Cell2.Offset((i - 1) * j, 3).Value = Evaluate("SUMPRODUCT((EvalRange=minAge),(EvalRang e<MaxAge),(sumrange))") Next Next PS; cell1 and cell2 are defined as range etc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT IN VBA
You cannot use the variables within the string being evaluated, you have to
use their values Evaluate("SUMPRODUCT((" & EvalRange.Address & "=" & minAge & "),(" & EvalRange.Address & "<" & MaxAge & ")," & sumrange.Address & ")") -- __________________________________ HTH Bob "caroline" wrote in message ... hello, Using the search, I have tried to find a way to program SUMPRODUCT in VBA, but I am not sure I am using it properly. Can you check what I am doing wrong. I do not want to copy the formula in Excel, i am after the final value. Thanks Caroline For i = 1 To 200 Dim EvalRange As Range Set EvalRange = Range(Cell1.Offset((i - 1) * 23, 2), Cell1.Offset(i * 23 - 1, 2)) Dim sumrange As Range Set sumrange = Range(Cell1.Offset((i - 1) * 23, X), Cell1.Offset(i * 23 - 1, X)) For j = 1 To 3 minAge = Range("Ageband")(j, 0).Value maxAge = Range("Ageband")(j, 1).Value Cell2.Offset((i - 1) * j, 3).Value = Evaluate("SUMPRODUCT((EvalRange=minAge),(EvalRang e<MaxAge),(sumrange))") Next Next PS; cell1 and cell2 are defined as range etc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct | New Users to Excel |