help making code more effecient
 

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

help making code more effecient
 

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

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


Corey....


"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

help making code more effecient
 

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:R65536C 4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))"
range("N3:N5").FormulaR1C1 = _
"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65536 C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))"hth,Doug"timmulla" wrote in ... 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:R655 36C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = _"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65 536C4=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:R655 36C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = _"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65 536C4=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:R655 36C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = _"=SUMPRODUCT((R2C8:R65536C8=""SALE"")*(R2C4:R65 536C4=RC12)*(R2C4:R65536C4<R[-1]C12)*(R2C4:R65536C4<""""))" Any help would be appreciated. -- Regards, timmulla