hi guys,
first of all I am new in VBA!
i have this worksheet as an example
<tbody>
</tbody>
I wanted to count types of goods and i wanted to calculate how much money has spent, so this works correctly, but when I filter for example person "X" cell G9 will show 1.833 instead of 3 kinds of goods and the SUMPRODUCT will still calculate hidden rows.
i have found a way in other sites by defining name-ranges but it won't work for me because i am upgrading my original worksheets daily and new rows are added everyday.
i tried to write a simple VBA code for SUMPRODUCT (wish i could access the exact code because I'm not at my office)
here is the algorithm: (I'm not writing the exact code. this is what I remember. the original one is working correctly except the red lines)
how can i write this
Does AutoFilter "Hide" the rows? (the property will be "hidden"?)
thanx
first of all I am new in VBA!
i have this worksheet as an example
A | B | C | D | E | F | G | |
1 | person | goods | price | % | $ paid | countif! | 1/countif |
2 | X | A | 100 | 30% | =D2*C2 | =COUNTIF(B:B,B2:B2) | =1/F2 |
3 | Y | A | 100 | 20% | =D3*C3 | =COUNTIF(B:B,B3:B3) | =1/F3 |
4 | Z | A | 100 | 10% | =D4*C4 | =COUNTIF(B:B,B4:B4) | =1/F4 |
5 | X | B | 80 | 50% | =D5*C5 | =COUNTIF(B:B,B5:B5) | =1/F5 |
6 | Y | B | 80 | 40% | =D6*C6 | =COUNTIF(B:B,B6:B6) | =1/F6 |
7 | Z | C | 70 | 20% | =D7*C7 | =COUNTIF(B:B,B7:B7) | =1/F7 |
8 | X | D | 90 | 70% | =D8*C8 | =COUNTIF(B:B,B8:B8) | =1/F8 |
9 | =SUMPRODUCT(C2:C8,D2:D8) | =SUBTOTAL(109,E2:E8) | =SUBTOTAL(109,G2:G8) |
<tbody>
</tbody>
I wanted to count types of goods and i wanted to calculate how much money has spent, so this works correctly, but when I filter for example person "X" cell G9 will show 1.833 instead of 3 kinds of goods and the SUMPRODUCT will still calculate hidden rows.
i have found a way in other sites by defining name-ranges but it won't work for me because i am upgrading my original worksheets daily and new rows are added everyday.
i tried to write a simple VBA code for SUMPRODUCT (wish i could access the exact code because I'm not at my office)
here is the algorithm: (I'm not writing the exact code. this is what I remember. the original one is working correctly except the red lines)
function Sumpro (range1 as range, range2 as range)
dim rows1 rows2 n r col1 col2 as integer
sumpro=0
rows1=range1.cells.count
rows2=range2.cells.count
col1=range1.column
col2=range2.column
r=range1.row
if rows1=rows2 then
dim rows1 rows2 n r col1 col2 as integer
sumpro=0
rows1=range1.cells.count
rows2=range2.cells.count
col1=range1.column
col2=range2.column
r=range1.row
if rows1=rows2 then
for n=1 to rows1
if activeworksheets.rows(n+r-1).hidden=false
sumpro=sumpro+application.worksheetfunction.index(range1,n,1)*application.worksheetfunction.index(range2,n,1)
end if
if activeworksheets.rows(n+r-1).hidden=false
sumpro=sumpro+application.worksheetfunction.index(range1,n,1)*application.worksheetfunction.index(range2,n,1)
end if
next n
endif
end function
end function
how can i write this
Does AutoFilter "Hide" the rows? (the property will be "hidden"?)
thanx