It is even easier in VBA to call the data filter and put the results on a
separate sheet.

Adding the sumproduct formula to the cells in VBA would again be simplicity.


Sub AAAD()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
With Worksheets("NC90")
Set rng = .Range("A1"). _
CurrentRegion.Resize(, 3)
End With
Set rng2 = rng.Offset(1, 0). _
Resize(rng.Rows.Count - 1, 1)
With Worksheets("sheet3")
rng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1:C1"), _
Unique:=True
Set rng1 = .Range(.Cells(2, 1), _
..Cells(Rows.Count, 1).End(xlUp))
End With
rng1.Offset(0, 3).Formula = "=SumProduct(--(" & _
rng2.Address(External:=True) _
& "=A2),--(" & rng2.Offset(0, 1).Address(External:=True) _
& "=B2),--(" & rng2.Offset(0, 2).Address(External:=True) _
& "=C2))"
rng1.Offset(0, 3).Formula = rng1.Offset(0, 3).Value
End Sub

--
Regards,
Tom Ogilvy


mattis2k wrote in message
...

Cheers Tom,

But I was really looking for a way in VB, sorry i forgot to mention
that bit....

Thanks

Matt


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/