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" wrote:

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