Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > making code more efficient
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 46
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 611
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 46
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: For Count, when count changes from cell to cell LenS Excel Discussion (Misc queries) 18 January 4th 07 12:53 AM
Cell value not recognized by code. Brady Excel Discussion (Misc queries) 8 December 21st 06 02:56 AM
Macro Help (Uppercase multiple ranges?) Ken Excel Discussion (Misc queries) 14 December 2nd 06 07:23 PM
Text formatting Kace Excel Worksheet Functions 1 September 18th 06 08:28 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement