making code more efficient

[Guest]

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:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"

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

"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=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:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"

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

"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=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:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"

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

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



Any help would be appreciated.

 

[Earl Kiosterud]

timmulla,

You could first stuff column L with your values. Then do the formulas.
This will copy down the formula in column M:

Range("M3") = _
"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"

Range("M4").Select
Do While ActiveCell.Offset(0, -1) <> "" ' is there a value in L?
ActiveCell.Offset(-1, 0).Copy Destination:=ActiveCell ' copy down
ActiveCell.Offset(1, 0).Select ' move down
Loop

You can paste it from here, but watch for extra line feeds. It's not
terribly efficient. Usually we don't use the active cell. But it works
fine. You could write a similar routine for column N.

 

[Bob Phillips]

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)

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:R65536C4>=RC12)*(R2C4:R65536C4<R[

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

"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R

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

"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[

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

"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R

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

"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[

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

"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R

Any help would be appreciated.