Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Calculating(Processor(2)): %
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default Calculating(Processor(2)): %
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,440
Default Calculating(Processor(2)): %
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" wrote in message
...
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 100
Default Calculating(Processor(2)): %
Niek Otten wrote:
Try setting calculation to Manual (and back to Automatic once your macro
finished)

....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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default Calculating(Processor(2)): %
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" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default Calculating(Processor(2)): %
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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Calculating(Processor(2)): %


"Bishop" wrote:

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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Calculating(Processor(2)): %
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.
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 2 processors
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 = 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" wrote:

Previous Posts In This Thread:

On Wednesday, February 03, 2010 11:39 AM
Bishop wrote:

Calculating(Processor(2)): %
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 is 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 do not
understand what is 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 does not 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)

On Wednesday, February 03, 2010 11:57 AM
Niek Otten wrote:

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

On Wednesday, February 03, 2010 1:01 PM
Phil Hibbs wrote:

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.

On Wednesday, February 03, 2010 10:55 PM
J_Knowles wrote:

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


"Bishop" wrote:

On Thursday, February 04, 2010 9:36 AM
Bishop wrote:

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.

"J_Knowles" wrote:

On Wednesday, February 24, 2010 11:27 AM
ETinKC wrote:

"Bishop" wrote:
"Bishop" wrote:

On Wednesday, February 24, 2010 11:38 AM
ETinKC wrote:

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/tutorials...ta-grids-.aspx
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 226
Default 2 processors
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

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 procedu

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


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
Text Item Processor littleredhairedgirl Excel Worksheet Functions 1 October 24th 09 01:05 PM
Calculate:Calculating Processor(s) Amit Patel Excel Discussion (Misc queries) 0 October 3rd 09 06:08 AM
Calculating: (1 Processor(s)): 25% MESSAGE ON STATUS BAR RM Excel Discussion (Misc queries) 2 February 9th 08 12:30 AM
how do i take somthing off the word processor and put it in a fil 1 Excel Worksheet Functions 5 December 13th 07 08:53 PM
Programming a processor macrolad Excel Programming 1 October 1st 03 05:15 PM


All times are GMT +1. The time now is 10:29 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