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.

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


"Matt" wrote in message
...
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.

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


"Matt" wrote in
message
...
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.


.