Error 13: Type Mismatch Row Limit Restriction?

[shawnryan5]

When the excel sheet raw data has under 10,000 rows it runs, when it has 10,000 rows and over I get the error. Any idea? The error is pointed to the mu = Cells(joker, 12)



Columns("A:I").Select
Selection.ClearContents
Windows("New Registrations.xls").Activate
ActiveWindow.WindowState = xlNormal
Columns("A:I").Select
Selection.Copy
Windows("Polk Trend Report CYTD.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

Sheets("Data").Select

Dim nz As Long
Dim joker As Long
Dim lambda As Long

nz = Cells(4, 12).Value

Dim mu As Long
For joker = 5 To nz + 4
lambda = Cells(joker, 11)
mu = Cells(joker, 12)
If lambda <> 0 And mu - lambda > 1 Then
Range("A" & lambda).Select
Selection.Copy
Range("A" & lambda + 1 & ":A" & mu - 1).Select
ActiveSheet.Paste
Else:
End If
Next joker

Range("N5:O" & nz + 4).Select
Selection.ClearContents

Dim iota As Long
Dim kappa As Long
iota = 7
Do While Cells(iota, 2).Value <> ""
If Cells(iota, 2) = "UNKNOWN" Then
kappa = Application.WorksheetFunction.Match(Cells(iota, 1).Value, Range("J1:J" & nz + 4), 0)
Cells(kappa, 14).Value = Cells(iota, 7).Value
Cells(kappa, 15).Value = Cells(iota, 5).Value
Range("A" & iota & ":I" & iota).Select
Selection.Delete Shift:=xlUp
iota = iota - 1
ElseIf Cells(iota, 2) = "Zone Total" Then
Range("A" & iota & ":I" & iota).Select
Selection.Delete Shift:=xlUp
iota = iota - 1
ElseIf Application.WorksheetFunction.And(Cells(iota, 5) = 0, Cells(iota, 7) = 0) Then
Range("A" & iota & ":I" & iota).Select
Selection.Delete Shift:=xlUp
iota = iota - 1
Else:
End If
iota = iota + 1
Loop
Range("A" & iota & ":I" & iota).Select
Selection.Delete Shift:=xlUp

Range("C5:I5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Set pvtTable = Worksheets("Total Dealer (Trend)").Range("O5").PivotTable
pvtTable.RefreshTable
Sheets("Total Dealer (Trend)").Select
Cells.Select
Selection.Columns.AutoFit

Sheets("Data").Select
Range("S40:T" & nz + 39).Select
Selection.Copy
Range("A2").Select
Sheets("Total Dealer (Trend)").Select
Range("B40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("Data").Select
Range("U40:U" & nz + 39).Select
Selection.Copy
Range("A2").Select
Sheets("Total Dealer (Trend)").Select
Range("E40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B40:E" & nz + 39).Select
Selection.Sort Key1:=Range("E40"), Order1:=xlDescending, Header:=xlNo _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveWindow.WindowState = xlMaximized
End Sub

[shawnryan5]

Any help on why if there is 9,999 rows it works but if it is 10,000 rows it does not?

[mikerickson]

What is in the cell when the error occurs. If it is an string or an error value (e.g. #N/A) that will be a mismatch with the Long mu.

[SamT]

That, my friends, is pretty awesome coding.

[snb]

are you familiar with


sheet1.cells(1).currentregion.resize(,9)