VBA formula too long?

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
 
P

Peter T

Although you might not appreciate it I'd say Excel has done you a favour by
not accepting a formula longer than 255 characters.

I'm not going to attempt to read the purpose of your formula but I'm
guessing you could put those "words" in a list (ie cells), and with a
different formula end up with the same result. And a much shorter formula.
Another thing that might be relevant is naming both the word list and
A26:A391, perhaps with dynamic names.

Regards,
Peter T

JohnP said:
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:I
391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<>"")))-(SUMPRODUCT((A26:A
391<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")))-(SUMP
RODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26
:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUM
PRODUCT((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:A3
91<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Pate
rnity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"


Thanks in advance,
JohnP
Click to expand...
 
J

Joel

Why don't you write you own UDF function. Here is a start

ActiveCell.Offset(-11, 0).formula = "=Myfunction(A26:I391)"


Myfunction(Target as Range)
NumberofRows = target.Rows
FirstPart = WorksheetFunction.SumProduct( _
"(" & target.Column(1) & "<B1)*(" & target.Column(9) & "<>"")")

(A26:A391<B1)*(I26:I391<>"")
end function

JohnP said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining R1C1 and absolute reference in SUMPRODUCT in VBA 1
Using VBA To Enter A Sumproduct Formula Into A Cell 0
Formula Too Long? 2
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
formula is too long error 3
Formula too long 1
Execl DrWatson Error 80000007 3
WTD: Nvidia Videocard for 4x AGP Slot 0

Top