help making code more effecient

[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.

 

[Corey]

I am no expert in code but from my understanding using the [L3] is very slow.

Better to use:
with sheet1.Range("L3")
..Activate
..value = 975000
..Offset(0,1).FormualR1C1 =
"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"

..
..
..
..
..
..
End With


Corey....


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.

 

[Doug Glancy]

timmulla,

You always want to avoid using Activate or Select - they both slow things
down a lot, and are rarely necessary:

range("L3").value = 975000
range("L4").value = 950000
range("L5").value = 925000
range("M3:M5").FormulaR1C1 = _
"=SUMPRODUCT((R2C8:R65536C8=""BUY"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"
range("N3:N5").FormulaR1C1 = _
"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536C4>=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<>""""))"hth,Doug"timmulla" <[email protected]> wrote in messagenews:[email protected]...> Can anyone help me make the following code more effecient. I keeprepeating> 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.>> --> Regards,>> timmulla