Calculating(Processor(2)): %

B

Bishop

I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it's running I have "Calculating(Processor(2)): XX%" in my status
bar. You can see the data being added one block at a time (one iteration of
the code) as it cycles through the Calculating process. I just don't
understand what's causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
.Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep doesn't have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub
 
N

Niek Otten

If you're moving data the workbook may be recalculated all the time
Try setting calculation to Manual (and back to Automatic once your macro
finished)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Bishop said:
I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it's running I have "Calculating(Processor(2)): XX%" in my
status
bar. You can see the data being added one block at a time (one iteration
of
the code) as it cycles through the Calculating process. I just don't
understand what's causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in
this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
.Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep doesn't have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of
rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow +
1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7
Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally
sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),"""","
& _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub
Click to expand...
 
P

Phil Hibbs

Niek said:
Try setting calculation to Manual (and back to Automatic once your macro
finished)
Click to expand...

....and I think you need to do an Application.Calculate as well when
you set it back to automatic, or the workbook will be inconsistent
until you change a cell.

Phil Hibbs.
 
J

J_Knowles

Sub SpeedUpMacro()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'your code goes here

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

HTH,
--
Data Hog


Bishop said:
I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it's running I have "Calculating(Processor(2)): XX%" in my status
bar. You can see the data being added one block at a time (one iteration of
the code) as it cycles through the Calculating process. I just don't
understand what's causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
.Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep doesn't have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub
Click to expand...
 
B

Bishop

This worked like a charm! Thank you so much! What was taking 40 minutes
before now runs in less than 5 seconds!

You're a life saver.

J_Knowles said:
Sub SpeedUpMacro()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'your code goes here

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

HTH,
--
Data Hog


Bishop said:
I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it's running I have "Calculating(Processor(2)): XX%" in my status
bar. You can see the data being added one block at a time (one iteration of
the code) as it cycles through the Calculating process. I just don't
understand what's causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
.Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep doesn't have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub
Click to expand...
Click to expand...
 
E

ETinKC

Bishop said:
I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it's running I have "Calculating(Processor(2)): XX%" in my status
bar. You can see the data being added one block at a time (one iteration of
the code) as it cycles through the Calculating process. I just don't
understand what's causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
.Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
.Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep doesn't have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
.Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
.Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
.Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub
Click to expand...
 
E

ETinKC

I found your post while googling "slow excel calculating 2 processor(s)" as I
was having the same problem as you, but wasn't working with Macros at the
time. I am glad you got yours fixed but thought I would post this here as
it might help other who are having the same problem but not while writing
code.

I was creating a spreadsheet template using multiple instances of indirect
to reference ranges in other sheets in the workbook that had not yet been
created. So while working on the sheet i did turn off auto calc. But even
when finished it would reference a sheet not yet created, waiting for the
user to add the tab and name it appropriately so the indirect can reference
it.

I solved the slow problem by adding an if statement before the indirect in
my formula such that the user must check a box telling the template that tab
exists before running the part of the formula with the indirect function
pointing to that tab.

Problem solved.
 
C

chris jamieson

I too have been experiencing this problem and am keen to try the Sub SpeedUpMacro you've included, but I have probably 30 vba codes. Would I need to add this Sub SpeedUpMacro to all of those 30 or just one, and if just one, how do I know which one?

Chris



J_Knowles wrote:

Sub SpeedUpMacro()Application.ScreenUpdating = FalseApplication.
03-Feb-10

Sub SpeedUpMacro(

Application.ScreenUpdating = Fals
Application.EnableEvents = Fals
Application.Calculation = xlCalculationManua

'your code goes her

Application.ScreenUpdating = Tru
Application.EnableEvents = Tru
Application.Calculation = xlCalculationAutomati
Application.Calculat

End Su

HTH
-
Data Ho

:

Previous Posts In This Thread:

Calculating(Processor(2)): %
I have a program running in a .xlsm 2007 spreadsheet that formats data an
moves the data from one worksheet to another (in the same workbook). Th
problem is that it runs painfully slow. I tested it with 2000 rows @ ~
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When th
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE
Also, while it is running I have "Calculating(Processor(2)): XX%" in my statu
bar. You can see the data being added one block at a time (one iteration o
the code) as it cycles through the Calculating process. I just do no
understand what is causing it to have such latency. Is there a way to mak
the code more efficient so that it runs faster? What's causing th
Calculating(Processor(2)): % to pop up? I have other macros running in thi
same workbook and they run fine

My code

'This code formats the data downloaded from Catalyst (in the Catalyst Dum
'tab) and puts in the Tally Sheet to be reviewe
Sub TallySheetRepDump(

Dim LastRow As Intege
Dim StartRow As Intege
Dim TSPasteRow As Integer 'Tally Shee
Dim TSStartRow As Integer 'Tally Shee
Dim RowCount As Intege
Dim EndRow As Intege
Dim CheckRow As Intege
Dim AddRow As Intege
Dim counter As Intege
Dim PCounter As Integer 'Progress Counte
Dim PctDone As Single 'Percent Don

With Sheets("Tally Sheet"
..Shapes("BigOrangeButton").Cu
End Wit

With Sheets("SortedRepData"
'The following line of code calculates the number of rows of dat
LastRow = .Range("A" & Rows.Count).End(xlUp).Ro
'Sort by UID (column A) then by Transaction Amount (column F
..Rows("1:" & LastRow).Sort
Key1:=.Range("R1"),
Order1:=xlAscending,
Key2:=.Range("A1"),
Order2:=xlAscending,
Key3:=.Range("F1"),
Order3:=xlAscending,
Header:=xlN

StartRow =
TSPasteRow =
RowCount =
'Outer loop for entire worksheet
D
RowCount = RowCount +
'Check to see if RowCount is equal to the next row. If not tha
'means the name has changed and we want to capture the info fo
'the current re
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) The
'If name changes make sure the rep has 3 or more transaction
EndRow = StartRow +
CheckRow = StartRo
AddRow =
'If rep has at least 3 transactions then copy the first 3 an
'move them to the Tally Shee
If .Range("A" & StartRow) = .Range("A" & EndRow) The
..Range("A" & StartRow & ":F" & EndRow).Copy
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow
..Range("G" & StartRow & ":Q" & EndRow).Copy
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow
TSPasteRow = TSPasteRow +
StartRow = RowCount +
'If rep does not have at least 3 transactions then determine ho
man
'transactions they do have and add the appropriate number of row
Els
For counter = CheckRow To EndRo
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1)
The
AddRow = AddRow -
CheckRow = CheckRow +
Els
..Rows(CheckRow + 1).Resize(AddRow).Inser
(xlShiftDown
RowCount = RowCount + AddRo
..Range("A" & StartRow & ":F" & EndRow).Copy
Destination:=Sheets("Tally Sheet").Range("A"
TSPasteRow
..Range("G" & StartRow & ":Q" & EndRow).Copy
Destination:=Sheets("Tally Sheet").Range("N"
TSPasteRow
TSPasteRow = TSPasteRow +
LastRow = LastRow + AddRo
StartRow = RowCount + AddRo
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)

If you are moving data the workbook may be recalculated all the timeTry
If you are moving data the workbook may be recalculated all the time
Try setting calculation to Manual (and back to Automatic once your macro
finished)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Niek Otten wrote:...and I think you need to do an Application.
Niek Otten wrote:

....and I think you need to do an Application.Calculate as well when
you set it back to automatic, or the workbook will be inconsistent
until you change a cell.

Phil Hibbs.

Sub SpeedUpMacro()Application.ScreenUpdating = FalseApplication.
Sub SpeedUpMacro()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'your code goes here

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

HTH,
--
Data Hog


:

This worked like a charm! Thank you so much!
This worked like a charm! Thank you so much! What was taking 40 minutes
before now runs in less than 5 seconds!

You're a life saver.

:

:
:

I found your post while googling "slow excel calculating 2 processor(s)" as
I found your post while googling "slow excel calculating 2 processor(s)" as I
was having the same problem as you, but was not working with Macros at the
time. I am glad you got yours fixed but thought I would post this here as
it might help other who are having the same problem but not while writing
code.

I was creating a spreadsheet template using multiple instances of indirect
to reference ranges in other sheets in the workbook that had not yet been
created. So while working on the sheet i did turn off auto calc. But even
when finished it would reference a sheet not yet created, waiting for the
user to add the tab and name it appropriately so the indirect can reference
it.

I solved the slow problem by adding an if statement before the indirect in
my formula such that the user must check a box telling the template that tab
exists before running the part of the formula with the indirect function
pointing to that tab.

Problem solved.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Distributed Data Grids - Share Objects Between Windows Service and ASP.NET
http://www.eggheadcafe.com/tutorial...b7a-1bb00e33db07/distributed-data-grids-.aspx
 
G

GS

chris jamieson presented the following explanation :
I too have been experiencing this problem and am keen to try the Sub
SpeedUpMacro you've included, but I have probably 30 vba codes. Would I need
to add this Sub SpeedUpMacro to all of those 30 or just one, and if just one,
how do I know which one?

Chris
Click to expand...

Not sure where this SpeedUpMacro code came from but looks like
something I posted here some time ago in this reusable sub:

Public Sub EnableFastCode(Optional SetFast As Boolean = True)
With Application
If SetFast Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculate '//update changes
End If
End With
End Sub

To use it in any procedure:

Sub SomeSub()
'dim your vars and do other stuff before running main process

'before you run main process, turn it on
EnableFastCode
'code to do lengthy stuff or lots of recalcs goes here

'at end of main process, turn it off and recalc
EnableFastCode False

'cleanup code goes here
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating(Processors(2)): % 1
Checking for a minimum of 5 rows 2
Reference Cell in VLOOKUP 5
Textbox input goes to 2 sheets in same wbk 0
Code only works in Debug mode. Why? 6
Joel - Data Consolidate Limitation 0
Excel Macro VBA to Count Google Search Results 0
VBA Borders in Excel 2007 1

Top