Average of one range based on date in one column

[Matt]

Lets say my sheet looks like this after the data is
entered and the advanced filter hides row 6. Which has
the number value of 2. And a Text3 value that is not equal
to xx. Although the date value is 1/3/04

A B C D E F
1 Text3
2 xx
3
4 Name Date Text1 Text2 Text3 number
5 " " 1/1/04 " " " " xx 12
7 " " 1/5/04 " " " " xx 15
---------------------------------------------------
8 Jan Avg 14
9 Feb Avg

What I want is to create in VB the algorithm that will
insert the average formula into cell F8 inserting only the
range values that are in January lets say and Text3.
Or b/c the data is filtered just based on the visible date
values take that dates corresponding number and place that
in the Average range holder. I want cell F8 to look like
this = Avg(F5,F7) but I want to have it automatically done
by searching the visible date values in column B and then
taking the corresponding number in column F and placing it
into the range the the Average funciton will specify.
I have thought about doing an advanced filter with the
copy to range in another sheet but to me there is to much
information and calculations that are done by automation,
defined names etc.... that would be wiped out once that
copy to was done.
If anyone understands what I am looking to do please
respond. Thank you.

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range, cell as Range

set rng = Range(Range("F5"),ActiveCell.offset(-1,0))
set rng1 = nothing
for each cell in rng
if cell.Entirerow.Hidden = False _
and Format(cell.offset(0,-3),"mmm") = _
left(Activecell.offset(0,-5),3) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
End if
Next
ActiveCell.Formula = "=Average(" & rng1.Address & ")"

A lot would depend on how and when you plan to run the macro. This assumes
F8 is the activeCell.

 

[Matt]

Thank You Tom.

-----Original Message-----
Dim rng as Range, rng1 as Range, cell as Range

set rng = Range(Range("F5"),ActiveCell.offset(-1,0))
set rng1 = nothing
for each cell in rng
if cell.Entirerow.Hidden = False _
and Format(cell.offset(0,-3),"mmm") = _
left(Activecell.offset(0,-5),3) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
End if
Next
ActiveCell.Formula = "=Average(" & rng1.Address & ")"

A lot would depend on how and when you plan to run the macro. This assumes
F8 is the activeCell.


--
Regards,
Tom Ogilvy





.