PDA

View Full Version : Sumproduct syntax as a VBA Worksheet Function



lanhao
06-27-2006, 01:42 PM
Hi there, I was wondering how would I go about converting this into a VB code as an Application.WorksheetFunction object?

"=SUMPRODUCT(--(DPRAW!R2C7:R6553C7=R[2]C1),--(DPRAW!R2C8:R6553C8>=R3C2),--(DPRAW!R2C8:R6553C8<=R3C4))"

Any help would be appreciated on this, thanks much :)

Bob Phillips
06-27-2006, 03:56 PM
Hi there, I was wondering how would I go about converting this into a VB code as an Application.WorksheetFunction object?

"=SUMPRODUCT(--(DPRAW!R2C7:R6553C7=R[2]C1),--(DPRAW!R2C8:R6553C8>=R3C2),--(DPRAW!R2C8:R6553C8<=R3C4))"

Any help would be appreciated on this, thanks much :)
In all my time of trying, I have never successfully evaluated A SUMPRODUCT function in VBA using APplication or WorksheetFunction.

The only way I have got it to work is using Activesheet.Evaluate, such as



MsgBox Activesheet.Evaluate("SUMPRODUCT(--(DPRAW!$G$2:$G$6553=$A3)," & _
"--(DPRAW!$H$2:$H$6553>=$B$3),--(DPRAW!$H$2:$H$6553<=$D$3))")