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 |
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 |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com