Hi everyone, I currently have data as follows in Excel:

Date

03/01/2012

03/01/2012

05/01/2012

07/02/2012

07/02/2012

07/02/2012

02/05/2012

etc..Basically, I have a column with all my date ranges from 01/01/2012 to 31/12/2012 with some having multiple same values as there was different data on those dates that are captured in other columns.

Currently, I'm using the following to calculate January:

=SUMPRODUCT(--(CEO!A$2:A$378<>""), --(MONTH(CEO!A$2:A$378)=1))

This formula works great, but the issue is the Range.. I have it hard set from A2 to A378 but in-reality, the spreadsheet will keep growing and A378 will probably end up much higher.

Instead of trying to hardcap at some random higher value, I was wondering if it's possible to use a Macro to insert a similar type of formula into the cell to calculate it where I can just input the last known row as the last cell value?

Thanks in advance!

✔ Best Answer

The problem is with your quotes "" VBA sees that as the end of a string and when it tries to put the formula in a cell, the formula is incorrect and Excel throws an error back to VBA.

Try this:

.FormulaR1C1 = "=SUMPRODUCT(--(CalcDate<>""""), --(MONTH(CalcDate)=1))"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Have you thought of using a Named Range?

or

You can define a Dynamic Named Range that will allow you to insert new data.See here for a pretty good example:

http://www.exceluser.com/explore/dy...

Here is how MS does it:

http://support.microsoft.com/kb/830287

MIKE

Thanks for the suggestion it did help for all the messy info i had. Instead of all the CEO!Range stuff it's just CalcDate now which helps alot for reading it. :) Unfortunately, when I try and toss it into a macro to run on a cell it still tosses an error message stating the following:

Run-time error '1004':

Application-defined or object-defined error

Code:

Sub UpdateDash()

Set dash = Sheets("Dashboard")dash.Range("B10").FormulaR1C1 = _

"=SUMPRODUCT(--(CalcDate<>""), --(MONTH(CalcDate)=1))"

End Sub

Sorry, my Macro skills are just above nil. Hopefully someone else will have a solution.

MIKE

The problem is with your quotes "" VBA sees that as the end of a string and when it tries to put the formula in a cell, the formula is incorrect and Excel throws an error back to VBA.

Try this:

.FormulaR1C1 = "=SUMPRODUCT(--(CalcDate<>""""), --(MONTH(CalcDate)=1))"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll