Sumproduct in VBA.

[abdulncr]

Hi,


I am trying to use sumproduct in VBA. i managed to write below code, it is not working. could any one assist on this.

[pre]

Sub processreport()
Sheets("Report").Select
Range("B3").Select
Do While ActiveCell.Value <> ""

On Error Resume Next
ActiveCell.Offset(0, 3).Value = Application.WorksheetFunction.SumProduct((Sheets("Paste segment").Range("B3:C1000") = Range("b" & ActiveCell.Row).Value) * (Sheets("Paste segment").Range ("F3:F1000" = Range("d" & ActiveCell.Row).Value) * (Sheets("Paste segment").Range("G3:G1000"))
On Error GoTo 0
ActiveCell.Offset(1, 0).Select
Loop
End Sub

[/pre]
Regards

Abdul

 

[dan_l]

If I recall correctly:


sumproduct doesn't play so nice with vba. You have to put it in as text. It's not so much fun.

 

[Debraj]

Hi Abdul,


Can you please upload your workbook.. so that we can decide the structure of structure of "Paste Segment" or "Report Data" and can verify Data types for the same..


Wait.. wait.. Before that can you CLOSE the parenthesis for below range...

[pre]

ActiveCell.Offset(0, 3).Value = _
Application.WorksheetFunction.SumProduct( _
(Sheets("Paste segment").Range("B3:C1000") = _
Range("b" & ActiveCell.Row).Value) * _
(Sheets("Paste segment").Range("F3:F1000") = _
Range("d" & ActiveCell.Row).Value) * _
Sheets("Paste segment").Range("G3:G1000"))

[/pre]
If solved.. please inform..

Regards,

Deb