Hello, I use the following function to get a year to date total for a
specific month:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$375=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B$3:B$375<=DATEVALUE("10/31")),'Daily Reading Master Log'!AE$3:AE$375)

Question: How can i put this into a module that says if Cell B4 = October
(for example), do the above calculation and enter the value is Cell AI 7?
--
Carlee

Hi Carlee,

I am not sure that I really understand what it is you want to do. Do you
mean that you have the literal spelling of "October" in cell B4 and want to
change the formula to use that? If so, then you can change DATEVALUE section
of formula to following:-

DATEVALUE(B4&"31")

or do you want to use VBA code to test if B4 contains "October" and if it
does then put the formula you have in cell AI7. If this is correct then the
code is something like this:-

If Range("B4") = "October" Then

Range("AI7").FormulaR1C1 = "=SUMPRODUCT(--('Daily Reading Master
Log'!R3C[-33]:R375C[-33]=DATEVALUE(""01/01"")),--('Daily Reading Master
Log'!R3C[-33]:R375C[-33]<=DATEVALUE(""31/10"")),'Daily Reading Master
Log'!R3C[-4]:R375C[-4])"

End If

Note: There are no line breaks in the above line with the formula.

The easy way to get the VBA formula code is to create the formula in AI7 in
the interactive mode and when you are satisfied that it is correct, turn on
the VBA recorder. Make a minimal dummy adjustment to the formula like delete
and replace the last bracket, press Enter and turn off the recorder.

Feel free to get back to me if this still has not answered your question.

Regards,

OssieMac





"Carlee" wrote:

Hello, I use the following function to get a year to date total for a
specific month:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$375=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B$3:B$375<=DATEVALUE("10/31")),'Daily Reading Master Log'!AE$3:AE$375)

Question: How can i put this into a module that says if Cell B4 = October
(for example), do the above calculation and enter the value is Cell AI 7?
--
Carlee

Hi there,
Sorry for the confusion. I have resolved this issue.
--
Carlee


"OssieMac" wrote:

Hi Carlee,

I am not sure that I really understand what it is you want to do. Do you
mean that you have the literal spelling of "October" in cell B4 and want to
change the formula to use that? If so, then you can change DATEVALUE section
of formula to following:-

DATEVALUE(B4&"31")

or do you want to use VBA code to test if B4 contains "October" and if it
does then put the formula you have in cell AI7. If this is correct then the
code is something like this:-

If Range("B4") = "October" Then

Range("AI7").FormulaR1C1 = "=SUMPRODUCT(--('Daily Reading Master
Log'!R3C[-33]:R375C[-33]=DATEVALUE(""01/01"")),--('Daily Reading Master
Log'!R3C[-33]:R375C[-33]<=DATEVALUE(""31/10"")),'Daily Reading Master
Log'!R3C[-4]:R375C[-4])"

End If

Note: There are no line breaks in the above line with the formula.

The easy way to get the VBA formula code is to create the formula in AI7 in
the interactive mode and when you are satisfied that it is correct, turn on
the VBA recorder. Make a minimal dummy adjustment to the formula like delete
and replace the last bracket, press Enter and turn off the recorder.

Feel free to get back to me if this still has not answered your question.

Regards,

OssieMac





"Carlee" wrote:

Hello, I use the following function to get a year to date total for a
specific month:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$375=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B$3:B$375<=DATEVALUE("10/31")),'Daily Reading Master Log'!AE$3:AE$375)

Question: How can i put this into a module that says if Cell B4 = October
(for example), do the above calculation and enter the value is Cell AI 7?
--
Carlee