J
JohnP
Hi,
I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.
I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what today's
date is). See code below. In reformatting however the formula is pushed onto
a new line of code and the usual "_" does not seem to read as me wanting the
code to continue on the next line.
Is it possible to have a formula that is simply too long?
I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better way
of doing everything that I do I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more simple
solution.
(I haven't finished reformatting after I encountered the problem.)
EXCEL FORMULA
=(SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))
REFORMATTED VBA CODE
ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_
<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"
Thanks in advance,
JohnP
I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.
I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what today's
date is). See code below. In reformatting however the formula is pushed onto
a new line of code and the usual "_" does not seem to read as me wanting the
code to continue on the next line.
Is it possible to have a formula that is simply too long?
I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better way
of doing everything that I do I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more simple
solution.
(I haven't finished reformatting after I encountered the problem.)
EXCEL FORMULA
=(SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))
REFORMATTED VBA CODE
ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<>"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_
<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"
Thanks in advance,
JohnP