Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Code takes to long
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 49
Default Code takes to long
Hi

I have the following code that takes an age to process when i have
alot of items (5000 takes approx 1 hour.). Is there a more efficent
way i can process this? I am using Excel 2007 and writing in formulars
to the qtys of items i have inserted.

Sub newsystemorder()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Do While ActiveCell.Value < ""
ActiveCell.Offset(0, 0).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 1).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 2).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 3).FormulaR1C1 = "=RC[-1]-
(RC[1]+RC[2]+RC[3]+RC[4]+RC[5])"
ActiveCell.Offset(0, 3).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 4).FormulaR1C1 = "=SUM(Drawing!RC[6]:RC[7])-rc[1]-
rc[2]-rc[3]-rc[4]"
ActiveCell.Offset(0, 4).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 5).FormulaR1C1 = "=sum(Manufacture!RC[5]:RC[6])-
rc[1]-rc[2]-rc[3]"
ActiveCell.Offset(0, 5).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 6).FormulaR1C1 = "=sum('Sub Contract'!
RC[7]:RC[8])-rc[1]-rc[2]"
ActiveCell.Offset(0, 6).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 7).FormulaR1C1 = "=sum(Recieved!RC[3]:RC[4])-
rc[1]"
ActiveCell.Offset(0, 7).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 8).FormulaR1C1 = "=sum(Delivery!RC[2]:RC[3])"
ActiveCell.Offset(0, 8).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 9).FormulaR1C1 = "=RC[-7]-RC[-1]"
ActiveCell.Offset(0, 9).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 47).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 48).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 49).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 50).FormulaR1C1 = "=RC4*RC48"
ActiveCell.Offset(0, 50).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 51).FormulaR1C1 = "=RC5*RC48"
ActiveCell.Offset(0, 51).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 52).FormulaR1C1 = "=RC6*RC48"
ActiveCell.Offset(0, 52).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 53).FormulaR1C1 = "=RC7*RC48"
ActiveCell.Offset(0, 53).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 54).FormulaR1C1 = "=RC8*RC48"
ActiveCell.Offset(0, 54).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 55).FormulaR1C1 = "=RC9*RC48"
ActiveCell.Offset(0, 55).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 56).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
ActiveCell.Offset(0, 56).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic



ETC.........




Thanks in advace

Addy
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default Code takes to long
Sub newsystemorder()
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Row
End With
With ActiveCell
With .Resize(LastRow - .Row + 1, 10)
.BorderAround LineStyle:=xlContinuous, _
Weight:=xlThin, _
ColorIndex:=xlAutomatic
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
With .Offset(0, 47).Resize(LastRow - .Row + 1, 10)
.BorderAround LineStyle:=xlContinuous, _
Weight:=xlThin, _
ColorIndex:=xlAutomatic
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
.Offset(0, 3).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC[-1]-(RC[1]+RC[2]+RC[3]+RC[4]+RC[5])"
.Offset(0, 4).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=SUM(Drawing!RC[6]:RC[7])-rc[1]-rc[2]-rc[3]-rc[4]"
.Offset(0, 5).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=sum(Manufacture!RC[5]:RC[6])-rc[1]-rc[2]-rc[3]"
.Offset(0, 6).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=sum('Sub Contract'!RC[7]:RC[8])-rc[1]-rc[2]"
.Offset(0, 7).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=sum(Recieved!RC[3]:RC[4])-rc[1]"
.Offset(0, 8).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=sum(Delivery!RC[2]:RC[3])"
.Offset(0, 9).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC[-7]-RC[-1]"
.Offset(0, 50).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC4*RC48"
.Offset(0, 51).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC5*RC48"
.Offset(0, 52).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC6*RC48"
.Offset(0, 53).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC7*RC48"
.Offset(0, 54).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC8*RC48"
.Offset(0, 55).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=RC9*RC48"
.Offset(0, 56).Resize(LastRow - .Row + 1, 10).FormulaR1C1 =
"=SUM(RC[-6]:RC[-1])"
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Oggy" wrote in message
...
Hi

I have the following code that takes an age to process when i have
alot of items (5000 takes approx 1 hour.). Is there a more efficent
way i can process this? I am using Excel 2007 and writing in formulars
to the qtys of items i have inserted.

Sub newsystemorder()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Do While ActiveCell.Value < ""
ActiveCell.Offset(0, 0).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 1).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 2).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 3).FormulaR1C1 = "=RC[-1]-
(RC[1]+RC[2]+RC[3]+RC[4]+RC[5])"
ActiveCell.Offset(0, 3).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 4).FormulaR1C1 = "=SUM(Drawing!RC[6]:RC[7])-rc[1]-
rc[2]-rc[3]-rc[4]"
ActiveCell.Offset(0, 4).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 5).FormulaR1C1 = "=sum(Manufacture!RC[5]:RC[6])-
rc[1]-rc[2]-rc[3]"
ActiveCell.Offset(0, 5).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 6).FormulaR1C1 = "=sum('Sub Contract'!
RC[7]:RC[8])-rc[1]-rc[2]"
ActiveCell.Offset(0, 6).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 7).FormulaR1C1 = "=sum(Recieved!RC[3]:RC[4])-
rc[1]"
ActiveCell.Offset(0, 7).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 8).FormulaR1C1 = "=sum(Delivery!RC[2]:RC[3])"
ActiveCell.Offset(0, 8).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 9).FormulaR1C1 = "=RC[-7]-RC[-1]"
ActiveCell.Offset(0, 9).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 47).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 48).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 49).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 50).FormulaR1C1 = "=RC4*RC48"
ActiveCell.Offset(0, 50).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 51).FormulaR1C1 = "=RC5*RC48"
ActiveCell.Offset(0, 51).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 52).FormulaR1C1 = "=RC6*RC48"
ActiveCell.Offset(0, 52).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 53).FormulaR1C1 = "=RC7*RC48"
ActiveCell.Offset(0, 53).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 54).FormulaR1C1 = "=RC8*RC48"
ActiveCell.Offset(0, 54).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 55).FormulaR1C1 = "=RC9*RC48"
ActiveCell.Offset(0, 55).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic
ActiveCell.Offset(0, 56).FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
ActiveCell.Offset(0, 56).BorderAround LineStyle:=xlContinuous,
Weight:=xlThin, ColorIndex:=xlAutomatic



ETC.........




Thanks in advace

Addy


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

Advanced Search
Display Modes
Linear Mode Linear 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
deleting takes too long Howiek1 Excel Worksheet Functions 2 January 17th 11 12:58 PM
VLookup takes too long DTTODGG Excel Worksheet Functions 4 March 20th 08 09:57 PM
Clearing cells takes long, long time unclemuffin Excel Discussion (Misc queries) 9 August 17th 07 02:22 AM
Save takes long time Jan Excel Discussion (Misc queries) 2 February 15th 06 06:01 PM
Recalculation takes too long - help!!!! JulieD Excel Worksheet Functions 0 October 29th 04 09:39 AM


All times are GMT +1. The time now is 11:06 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement