Your code got it work. Thanks
--
Regards,

timmulla


"Bob Phillips" wrote:

Just those 3 cells?

Const sPart1 As String = "=SUMPRODUCT((R2C8:R65536C8="
Const spart2 As String = _

")*(R2C4:R65536C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))"
Dim sFormula1 As String
Dim sformula2 As String
Dim cell As Range
Dim nValue As Long

sFormula1 = sPart1 & """BUY""" & spart2
sformula2 = sPart1 & """SELL""" & spart2
nValue = 975000

With Range("L3:L5")
.Formula = "=97500-(ROW(A1)-1)*2500"
.Offset(0, 1).FormulaR1C1 = sFormula1
.Offset(0, 2).FormulaR1C1 = sformula2
End With



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"timmulla" wrote in message
...
Can anyone help me make the following code more effecient. I keep
repeating
the same formulas, so I think there might be a much more effecient way of
writing my code.

[L3].Value = 975000
[L3].Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C 4=RC12)*(R2C4:R65536C4<R[
-1]C12)*(R2C4:R65536C4<""""))"

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536 C4=RC12)*(R2C4:R65536C4<R
[-1]C12)*(R2C4:R65536C4<""""))"

[L4].Value = 950000
[L4].Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C 4=RC12)*(R2C4:R65536C4<R[
-1]C12)*(R2C4:R65536C4<""""))"

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536 C4=RC12)*(R2C4:R65536C4<R
[-1]C12)*(R2C4:R65536C4<""""))"

[L5].Value = 925000
[L5].Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C 4=RC12)*(R2C4:R65536C4<R[
-1]C12)*(R2C4:R65536C4<""""))"

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _


"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536 C4=RC12)*(R2C4:R65536C4<R
[-1]C12)*(R2C4:R65536C4<""""))"



Any help would be appreciated.
--
Regards,

timmulla