combine worksheet functions to make macro to automate everything

hello again

this is a function to look at the date.
Function timeleft()
timeleft = Date - 365
End Function

then this function written into excel not vba
looks at the date and sees if the value in A5 is greater than the date today,
if it is, then it shows the value in K5, else its 0.

=IF(A5>$S$5,K5,0)

then this worksheet function sums the new column, in worksheet a and b,


=SUM('2005 Archived'!P5:P59)+SUM('2004 Archived'!P5:P49)


and gives the total value from today minus 365 days.

what i would like to do is put this all in a macro.
Any suggestions would be greatly appreciated.

thanks again

mike

Sub CountStuff()
Dim v as Variant, tot as Double, rng as Range
Dim cell as Range
v = ("2005 Archived!K5:K59","2004 Archived!K5:K59")
tot = 0
for i = lbound(v) to ubound(v)
set rng = Range(v)
for each cell in rng
if cell.Offset(0,-10).Value > Date - 365 then
tot = tot + cell
end if
Next
Next
msgbox "Total is " & format(tot,"#.0")
End Sub

If you want it as a function, it would be different.

--
Regards,
Tom Ogilvy




"mike" <[email protected]> wrote in message
news:[email protected]...
> hello again
>
> this is a function to look at the date.
> Function timeleft()
> timeleft = Date - 365
> End Function
>
> then this function written into excel not vba
> looks at the date and sees if the value in A5 is greater than the date
today,
> if it is, then it shows the value in K5, else its 0.
>
> =IF(A5>$S$5,K5,0)
>
> then this worksheet function sums the new column, in worksheet a and b,
>
>
> =SUM('2005 Archived'!P5:P59)+SUM('2004 Archived'!P5:P49)
>
>
> and gives the total value from today minus 365 days.
>
> what i would like to do is put this all in a macro.
> Any suggestions would be greatly appreciated.
>
> thanks again
>
> mike

tom,

thanks for that.
however there is a syntax error on this line...
v = ("2005 Archived!K5:K59","2004 Archived!K5:K59")

any suggestions?

mike

"Tom Ogilvy" wrote:

> Sub CountStuff()
> Dim v as Variant, tot as Double, rng as Range
> Dim cell as Range
> v = ("2005 Archived!K5:K59","2004 Archived!K5:K59")
> tot = 0
> for i = lbound(v) to ubound(v)
> set rng = Range(v)
> for each cell in rng
> if cell.Offset(0,-10).Value > Date - 365 then
> tot = tot + cell
> end if
> Next
> Next
> msgbox "Total is " & format(tot,"#.0")
> End Sub
>
> If you want it as a function, it would be different.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "mike" <[email protected]> wrote in message
> news:[email protected]...
> > hello again
> >
> > this is a function to look at the date.
> > Function timeleft()
> > timeleft = Date - 365
> > End Function
> >
> > then this function written into excel not vba
> > looks at the date and sees if the value in A5 is greater than the date
> today,
> > if it is, then it shows the value in K5, else its 0.
> >
> > =IF(A5>$S$5,K5,0)
> >
> > then this worksheet function sums the new column, in worksheet a and b,
> >
> >
> > =SUM('2005 Archived'!P5:P59)+SUM('2004 Archived'!P5:P49)
> >
> >
> > and gives the total value from today minus 365 days.
> >
> > what i would like to do is put this all in a macro.
> > Any suggestions would be greatly appreciated.
> >
> > thanks again
> >
> > mike
>
>
>

changed , to & then there is type mismatch on...
for i = lbound(v) to ubound(v)




"Tom Ogilvy" wrote:

> Sub CountStuff()
> Dim v as Variant, tot as Double, rng as Range
> Dim cell as Range
> v = ("2005 Archived!K5:K59","2004 Archived!K5:K59")
> tot = 0
> for i = lbound(v) to ubound(v)
> set rng = Range(v)
> for each cell in rng
> if cell.Offset(0,-10).Value > Date - 365 then
> tot = tot + cell
> end if
> Next
> Next
> msgbox "Total is " & format(tot,"#.0")
> End Sub
>
> If you want it as a function, it would be different.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "mike" <[email protected]> wrote in message
> news:[email protected]...
> > hello again
> >
> > this is a function to look at the date.
> > Function timeleft()
> > timeleft = Date - 365
> > End Function
> >
> > then this function written into excel not vba
> > looks at the date and sees if the value in A5 is greater than the date
> today,
> > if it is, then it shows the value in K5, else its 0.
> >
> > =IF(A5>$S$5,K5,0)
> >
> > then this worksheet function sums the new column, in worksheet a and b,
> >
> >
> > =SUM('2005 Archived'!P5:P59)+SUM('2004 Archived'!P5:P49)
> >
> >
> > and gives the total value from today minus 365 days.
> >
> > what i would like to do is put this all in a macro.
> > Any suggestions would be greatly appreciated.
> >
> > thanks again
> >
> > mike
>
>
>

Sub CountStuff()
Dim v as Variant, tot as Double, rng as Range
Dim cell as Range
v = Array("2005 Archived!K5:K59","2004 Archived!K5:K59")
tot = 0
for i = lbound(v) to ubound(v)
set rng = Range(v)
for each cell in rng
if cell.Offset(0,-10).Value > Date - 365 then
tot = tot + cell
end if
Next
Next
msgbox "Total is " & format(tot,"#.0")
End Sub

Untested, so there could be other typos.

--
Regards,
Tom Ogilvy


"mike" <[email protected]> wrote in message
news:[email protected]...
> changed , to & then there is type mismatch on...
> for i = lbound(v) to ubound(v)
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > Sub CountStuff()
> > Dim v as Variant, tot as Double, rng as Range
> > Dim cell as Range
> > v = ("2005 Archived!K5:K59","2004 Archived!K5:K59")
> > tot = 0
> > for i = lbound(v) to ubound(v)
> > set rng = Range(v)
> > for each cell in rng
> > if cell.Offset(0,-10).Value > Date - 365 then
> > tot = tot + cell
> > end if
> > Next
> > Next
> > msgbox "Total is " & format(tot,"#.0")
> > End Sub
> >
> > If you want it as a function, it would be different.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "mike" <[email protected]> wrote in message
> > news:[email protected]...
> > > hello again
> > >
> > > this is a function to look at the date.
> > > Function timeleft()
> > > timeleft = Date - 365
> > > End Function
> > >
> > > then this function written into excel not vba
> > > looks at the date and sees if the value in A5 is greater than the date
> > today,
> > > if it is, then it shows the value in K5, else its 0.
> > >
> > > =IF(A5>$S$5,K5,0)
> > >
> > > then this worksheet function sums the new column, in worksheet a and
b,
> > >
> > >
> > > =SUM('2005 Archived'!P5:P59)+SUM('2004 Archived'!P5:P49)
> > >
> > >
> > > and gives the total value from today minus 365 days.
> > >
> > > what i would like to do is put this all in a macro.
> > > Any suggestions would be greatly appreciated.
> > >
> > > thanks again
> > >
> > > mike
> >
> >
> >