Need help in filtering and then using sumproduct through VBA

[Karter]

Hi everyone

I'm trying to generate a report wherein i dump a relatively large dataset in a hidden sheet. To create the summary from this, I need to use a criteria on a one field and then sumproduct the visible records from two others

For illustration, consider the following table:

Scenario : 1,1,1,2,2,3,3,3,3
Name: a,b,c,d,f,g,h,a,d
Met1: 5,3,5,4,2,7,9,7,1
Met2: 0.5, 0.1,1.0,0.2,1.1,0.3,1.3,0.5,0.9


I need to filter the table on Scenario (say 2) and then sumproduct(met1,met2) to populate a cell via VBA code. I'm fairly new to the language so any help would be much appreciated.

Thanks.

 

[venkat1926]

the data should be like this in sheet

Sheet1

*	A	B	C	D
1	Scenario : 1	Name:	Met1:	Met2:
2	1	a	5	0.5
3	1	b	3	0.1
4	1	c	5	1
5	2	d	4	0.2
6	2	f	2	1.1
7	3	g	7	0.3
8	3	h	9	1.3
9	3	a	7	0.5
10	3	d	1	0.9

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


now try this macro and see below data

Sub test()
Dim r As Range, filt As Range, met1 As Double, met2 As Double, dest As Range
Dim scenario As Integer, destdata As Range
Set dest = Range("A1").End(xlDown).Offset(5, 0)
Set r = Range("A1").CurrentRegion
dest = "scenario": dest.Offset(0, 1) = "met1": dest.Offset(0, 2) = "met2"
scenario = InputBox("type the number within columns A e.g. 2")
r.AutoFilter field:=1, Criteria1:=scenario
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1)
met1 = WorksheetFunction.SumProduct(filt.Columns("A:A").SpecialCells(xlCellTypeVisible), filt.Columns("c:C").SpecialCells(xlCellTypeVisible))
MsgBox met1
met2 = WorksheetFunction.SumProduct(filt.Columns("A:A").SpecialCells(xlCellTypeVisible), filt.Columns("D:D").SpecialCells(xlCellTypeVisible))
MsgBox met2
Set destdata = dest.Offset(1, 0)
destdata = scenario
destdata.Offset(0, 1) = met1
destdata.Offset(0, 2) = met2
ActiveSheet.AutoFilterMode = False




End Sub