ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   making code more efficient (https://www.excelbanter.com/excel-discussion-misc-queries/127241-making-code-more-efficient.html)

timmulla
making code more efficient
 
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

Earl Kiosterud
making code more efficient
 
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:R65536C 4=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.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"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



Bob Phillips
making code more efficient
 
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

timmulla
making code more efficient
 
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


All times are GMT +1. The time now is 04:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com