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

combine worksheet functions to make macro to automate everything
 

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

combine worksheet functions to make macro to automate everythi
 

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

combine worksheet functions to make macro to automate everythi
 

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

combine worksheet functions to make macro to automate everythi
 

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