| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making code more efficient | Excel Discussion (Misc queries) | |||
| Summary sheet in effecient way | Excel Discussion (Misc queries) | |||
| Making A Code | Excel Worksheet Functions | |||
| Effecient use of Arrays for encryption? | Excel Programming | |||
| Effecient way to check, add, delete duplicates | Excel Programming | |||
Linear Mode
