Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > slow macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default slow macro
I have a macro that updates a worksheet to ensure that the formulas stay
current. The worksheet pulls data off 12 other worksheets so that another
worksheet can display only pertinent data from the 12 combined. The purpose
of the code is to "fix" the worksheet if someone inserts, deletes, or pastes
cells on one of the 12. That being said, this is my code:

Sub Reset_Sheet()

Dim Current_Row As Integer
Dim iRow As Integer
Dim Months_Done As Boolean
Dim iMonth As Integer
Dim cMonth As String

Current_Row = 0
iRow = 0
Months_Done = False
iMonth = 1
cMonth = "Jan!"
Cells.Select
Selection.ClearContents
Sheets("Ref").Select
Application.ScreenUpdating = False

Do Until Months_Done = True

For iRow = 5 To 74
Current_Row = Current_Row + 1
Range("H" & Current_Row).Select
ActiveCell.Formula = "=if(" & cMonth & "H" & iRow & "TODAY(),"
& cMonth & "H" & iRow & ")"
Range("A" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0," & cMonth &
"A" & iRow & ")"
Range("B" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"B" & iRow & ")"
Range("C" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"C" & iRow & ")"
Range("D" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"D" & iRow & ")"
Range("E" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"E" & iRow & ")"
Range("F" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"F" & iRow & ")"
Range("G" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"G" & iRow & ")"
Range("I" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"I" & iRow & ")"
Range("J" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"J" & iRow & ")"
Range("K" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"K" & iRow & ")"
Range("L" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"L" & iRow & ")"
Range("M" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"M" & iRow & ")"
Range("N" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"N" & iRow & ")"
Next iRow

iMonth = iMonth + 1
Select Case iMonth
Case 1: cMonth = "Jan!"
Case 2: cMonth = "Feb!"
Case 3: cMonth = "Mar!"
Case 4: cMonth = "Apr!"
Case 5: cMonth = "May!"
Case 6: cMonth = "Jun!"
Case 7: cMonth = "Jul!"
Case 8: cMonth = "Aug!"
Case 9: cMonth = "Sept!"
Case 10: cMonth = "Oct!"
Case 11: cMonth = "Nov!"
Case 12: cMonth = "Dec!"
Case 13: Months_Done = True
End Select
Loop

Application.ScreenUpdating = False

End Sub


Any help would be great!!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default slow macro
You don't need to select the cell, but rather, can refer to it as an
object. This may speed up the response time.

Regards,
Eddie
http://www.HelpExcel.com

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default slow macro
Try turning calculation off at the beginning and back on at the end...

Application.Calculation = xlCalculationManual
'Your code...
Application.Calculation = xlCalculationAutomatic

--
HTH...

Jim Thomlinson


"John_A" wrote:

I have a macro that updates a worksheet to ensure that the formulas stay
current. The worksheet pulls data off 12 other worksheets so that another
worksheet can display only pertinent data from the 12 combined. The purpose
of the code is to "fix" the worksheet if someone inserts, deletes, or pastes
cells on one of the 12. That being said, this is my code:

Sub Reset_Sheet()

Dim Current_Row As Integer
Dim iRow As Integer
Dim Months_Done As Boolean
Dim iMonth As Integer
Dim cMonth As String

Current_Row = 0
iRow = 0
Months_Done = False
iMonth = 1
cMonth = "Jan!"
Cells.Select
Selection.ClearContents
Sheets("Ref").Select
Application.ScreenUpdating = False

Do Until Months_Done = True

For iRow = 5 To 74
Current_Row = Current_Row + 1
Range("H" & Current_Row).Select
ActiveCell.Formula = "=if(" & cMonth & "H" & iRow & "TODAY(),"
& cMonth & "H" & iRow & ")"
Range("A" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0," & cMonth &
"A" & iRow & ")"
Range("B" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"B" & iRow & ")"
Range("C" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"C" & iRow & ")"
Range("D" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"D" & iRow & ")"
Range("E" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"E" & iRow & ")"
Range("F" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"F" & iRow & ")"
Range("G" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"G" & iRow & ")"
Range("I" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"I" & iRow & ")"
Range("J" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"J" & iRow & ")"
Range("K" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"K" & iRow & ")"
Range("L" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"L" & iRow & ")"
Range("M" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"M" & iRow & ")"
Range("N" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"N" & iRow & ")"
Next iRow

iMonth = iMonth + 1
Select Case iMonth
Case 1: cMonth = "Jan!"
Case 2: cMonth = "Feb!"
Case 3: cMonth = "Mar!"
Case 4: cMonth = "Apr!"
Case 5: cMonth = "May!"
Case 6: cMonth = "Jun!"
Case 7: cMonth = "Jul!"
Case 8: cMonth = "Aug!"
Case 9: cMonth = "Sept!"
Case 10: cMonth = "Oct!"
Case 11: cMonth = "Nov!"
Case 12: cMonth = "Dec!"
Case 13: Months_Done = True
End Select
Loop

Application.ScreenUpdating = False

End Sub


Any help would be great!!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default slow macro
That worked really well! Thanks a lot!

"Jim Thomlinson" wrote:

Try turning calculation off at the beginning and back on at the end...

Application.Calculation = xlCalculationManual
'Your code...
Application.Calculation = xlCalculationAutomatic

--
HTH...

Jim Thomlinson


"John_A" wrote:

I have a macro that updates a worksheet to ensure that the formulas stay
current. The worksheet pulls data off 12 other worksheets so that another
worksheet can display only pertinent data from the 12 combined. The purpose
of the code is to "fix" the worksheet if someone inserts, deletes, or pastes
cells on one of the 12. That being said, this is my code:

Sub Reset_Sheet()

Dim Current_Row As Integer
Dim iRow As Integer
Dim Months_Done As Boolean
Dim iMonth As Integer
Dim cMonth As String

Current_Row = 0
iRow = 0
Months_Done = False
iMonth = 1
cMonth = "Jan!"
Cells.Select
Selection.ClearContents
Sheets("Ref").Select
Application.ScreenUpdating = False

Do Until Months_Done = True

For iRow = 5 To 74
Current_Row = Current_Row + 1
Range("H" & Current_Row).Select
ActiveCell.Formula = "=if(" & cMonth & "H" & iRow & "TODAY(),"
& cMonth & "H" & iRow & ")"
Range("A" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0," & cMonth &
"A" & iRow & ")"
Range("B" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"B" & iRow & ")"
Range("C" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"C" & iRow & ")"
Range("D" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"D" & iRow & ")"
Range("E" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"E" & iRow & ")"
Range("F" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"F" & iRow & ")"
Range("G" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"G" & iRow & ")"
Range("I" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"I" & iRow & ")"
Range("J" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"J" & iRow & ")"
Range("K" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"K" & iRow & ")"
Range("L" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"L" & iRow & ")"
Range("M" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"M" & iRow & ")"
Range("N" & Current_Row).Select
ActiveCell.Formula = "=if(H" & Current_Row & "<0, " & cMonth &
"N" & iRow & ")"
Next iRow

iMonth = iMonth + 1
Select Case iMonth
Case 1: cMonth = "Jan!"
Case 2: cMonth = "Feb!"
Case 3: cMonth = "Mar!"
Case 4: cMonth = "Apr!"
Case 5: cMonth = "May!"
Case 6: cMonth = "Jun!"
Case 7: cMonth = "Jul!"
Case 8: cMonth = "Aug!"
Case 9: cMonth = "Sept!"
Case 10: cMonth = "Oct!"
Case 11: cMonth = "Nov!"
Case 12: cMonth = "Dec!"
Case 13: Months_Done = True
End Select
Loop

Application.ScreenUpdating = False

End Sub


Any help would be great!!

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
Macro is very slow jlclyde Excel Discussion (Misc queries) 2 September 29th 08 04:43 PM
Macro help, very slow Scott Marcus Excel Programming 0 November 8th 06 05:39 PM
Slow macro AG Excel Programming 5 August 25th 05 01:25 AM
Macro slow down Jonny Excel Programming 3 February 24th 05 01:29 AM
Macro it's very Slow .... leo_nunez[_2_] Excel Programming 4 August 28th 04 03:45 PM


All times are GMT +1. The time now is 07:24 PM.

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