Heh, fair enough. I wasn't sure if it was just a common problem that
could
be easily answered without the code. So here is all the code and some
remarks after:
Option Explicit
Sub TallySheetRepDump()
Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer
Dim TSStartRow As Integer
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Call BanSumSort
With Sheets("Catalyst Dump")
'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("A2"), _
Order1:=xlAscending, _
Key2:=.Range("F2"), _
Order2:=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 5 or more transactions
EndRow = StartRow + 4
CheckRow = StartRow
AddRow = 4
'If rep has at least 5 transactions then copy the first 5 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 5 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
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code is causing "Calculation/Conversion Errors in Paramus
'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
12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1
12,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 + 4)).FillDown
TSStartRow = TSStartRow + 8
End If
Next RowCount
.Shapes("Elbow1").Cut
.Shapes("Elbow2").Cut
.Shapes("Picture 1").Cut
.Shapes("Picture 2").Cut
End With
End Sub
The 4 Vlookup formulas under With Sheets("Tally Sheet") is causing the
problem. If I comment out that section I don't experience the error. I
was
initially getting the problem DURING the execution of the macro but then I
went back and Declared all my variables (something I had not done). That
allowed the macro run nicely with no errors. However, when you save,
close,
re-open the spreadsheet (after the macro has run) the Calculating Cells:
XX%
pops up again.
Niek Otten said:
< I've narrowed it down to which part of the macro is causing the
problem>
Yes, and? Or do we have to guess..
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
I've written a macro on my computer and it runs seamlessly when I call
it.
My co-workers work in a different state so I save the spreadsheet to
our
share drive and they open and run it from there. The problem is when
they
open it they get: "Calculating Cells: XX%" in the lower left bottom of
Excel.
This takes about an hour to run and locks up Excel in the meantime.
I've
tried this same macro on 3 different computers in my building and don't
experience this problem. Everyone on my team has the same version of
Excel
(2003) and the same service pack (SP3). I've narrowed it down to which
part
of the macro is causing the problem but I can't figure out WHY I'm
having
the
problem to begin with. What's causing this error?
